Percentile Array Formula (Excel 2003 SP 1)

Topic

New Reply

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,IF(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….

Thanks in advance, once again…

Reply | Quote

Replies

Enter the following array formula in cell D1:

=PERCENTILE(IF(NOT(ISERROR(C3:C800)),IF(NOT(ISBLANK(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:B800D1,C3:C800))))

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

Reply | Quote

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…

Reply | Quote

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…

Reply | Quote