# Thread: Percentile Array Formula (Excel 2003 SP 1)

1. ## Percentile Array Formula (Excel 2003 SP 1)

Hello once again to all you Excel gurus. I'm back with another challenge (at least it's a challenge for me). I have the following formula:

=SUM(IF(A3:A800=A1,IF(B3:B800>=B1,IF(B3:B800<=C1,I F(NOT(ISERROR(C3:C800)),C3:C800,0),0),0)))

which works fine. It returns a value based on the conditions in the formula.

The problem is that I need to actually add not ALL the cells in C3:C800 based on the conditions; instead I need to add the upper quartile (top 25%) of those cells. And to complicate it a little further, some of the cells in C3:C800 contain #VALUE errors, while others contain "NM", while others contain values, which is why the IF(NOT(ISERROR... construction.

I would have posted a sample, but I can't seem to get the file size below 100K with the formula in it.

I feel like I'm so close but I just can't get it....

2. ## Re: Percentile Array Formula (Excel 2003 SP 1)

Enter the following array formula in cell D1:

=PERCENTILE(IF(NOT(ISERROR(C3:C800)),IF(NOT(ISBLAN K(C3:C800)),C3:C800,"")),0.75)

Use the following array formula to add the numbers in C3:C800 satisfying all the conditions:

=SUM((A3:A800=A1)*(B3:B800>=B1)*(B3:B800<=C1)*IF(N OT(ISERROR(C3:C800)),IF(ISNUMBER(C3:C800),IF(C3:C8 00>D1,C3:C800))))

Depending on what exactly you want, you may want to change the > in C3:C800>D1 to >= (greater than or equal to).

3. ## Re: Percentile Array Formula (Excel 2003 SP 1)

Hi Hans,

Good suggestion. I am not in front of that file right now, but I will try your suggestion first thing in the morning.

I was hitting a wall with this formula. I finally had to give up and go to the pros...

I'll let you know how it works...

Thanks again...

4. ## Re: Percentile Array Formula (Excel 2003 SP 1)

Hi Hans,

Well, I just tried your combination and it works. Next I decided to put the two formulas together and I got that to work as well...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•