Multi-conditional percentile

**Tarrant** · 05-19-2010, 01:11 AM

Hi,

I have a list of sales data in column C of which I want to calculate the 40th percentile where certain conditions are met in columns A and B.

I have used the following formula to calculate the answer where I only have two conditions in separate columns which seems to work.

{=PERCENTILE(IF(Data!A:A="U",IF(Data!B:B=590,Data!C:C)),0.4)}

However, I need to add an additional condition to column A which is to include the data in the percentile array if the value is equal to "U" or "H". I have tried a nested IF AND and a nested IF OR but can't seem to get the syntax right. Here's my latest try:

{=PERCENTILE(IF(OR(Data!AW:AW="u",Data!AW:AW="h"),IF(Data!A:A=590,Data!AH:AH)),0.4)}

While the above formula returns a value it appears to actually return the 40th percentile of all sales in location 590, rather than only those with a U or H in column A.

Below is some sample data.

Dwelling Type Location Sale Price
H 590 480000
H 590 475000
H 590 210000
H 590 185000
H 55 750000
H 55 500000
U 590 253000
U 590 251000
U 590 189000
U 590 300000
U 590 325000
U 55 152000
L 590 100000
L 590 127000
L 590 80000
L 55 98000
L 55 102000

Can anyone assist?

Cheers,

Tarrant

**DonkeyOte** · 05-19-2010, 03:18 AM

ORs in Arrays/Sumproduct are generally conducted by means of addition, eg:

=PERCENTILE(IF(((DATA!AW:AW="u")+(DATA!AW:AW="h"))*(Data!A:A=590),Data!AH:AH),0.4)
confirmed with CTRL + SHIFT + ENTER

Though in 2007 and beyond use of entire column references is permitted in Arrays it is not advised in terms of performance - restrict the ranges being processed whenever & wherever possible.