average ifs/ percentile

[bluepenink]

all

having some issues with average ifs logic.

my formula of "average if" / "percentile" works fine

{=AVERAGE(IF('Data sample'!$E$11:$E$211>PERCENTILE('Data sample'!$E$11:$E$211,0.9),'Data sample'!$E$11:$E$211))}

HOWEVER, when i try to add "averageifs", i cant seem to make it work?

AVERAGEIFS(Data!$R$13:$R$1449,">"&PERCENTILE(Data!$R$13:$R$1449,0.9),Data!$I$13:$I$1449,results!$C11)

basically, i want the average if formula / percentile / another condition that looks at column I13:I1449 (which is city names)

any thoughts guys? thanks.

 

[Oaktree]

Your AVERAGEIFS is missing the first argument, where you tell it which range to use to do the math.

In other words, it should be

AVERAGEIFS(range for math, range for criterion 1, criterion 1, range for criterion 2, criterion 2)

You have

AVERAGEIFS(range for criterion 1, criterion 1, range for criterion 2, criterion 2)

Maybe this?

AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(Data!$R$13:$R$1449,0.9),Data!$I$13:$I$1449,results!$C11)

 

[bluepenink]

Hi Oaktree

this one works fine for sure

AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(Data!$R$13:$R$1449,0.9),Data!$I$13:$I$1449,results!$C11)

is it possible for the percentile to exclude 0s? my data has 0s, so i dont want that to skew the average?

thxs again

 

[Oaktree]

Yes... but, you'd have to use an array formula for it.

Since you had the {}s in your first message in this thread, I assume you're familiar with the CTRL+SHIFT+ENTER necessity.

{=AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(IF(Data!$R$13:$R$1449<>0,Data!$R$13:$R$1449),0.9),Data!$I$13:$I$1449,results!$C11)}

 

[bluepenink]

Yes... but, you'd have to use an array formula for it.

Since you had the {}s in your first message in this thread, I assume you're familiar with the CTRL+SHIFT+ENTER necessity.

{=AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(IF(Data!$R$13:$R$1449<>0,Data!$R$13:$R$1449),0.9),Data!$I$13:$I$1449,results!$C11)}

ah that works like a charm your fantastic, thanks so much

i do have a general question tho.

for example, for "Nashville", there are 201 sales ppl. simple math says 10% of 201 = 20.1
-> for simplicity, on a seperate sheet, i listed the 201 sales ppl with their sales and sorted sales by highest to lowest.
--> i then simply took the average of the first "20.1" ppl or 20 ppl, as that is top 10%
---> this gave me an answer of 316K

when i apply the same logic, with the above formula, with percentile 90% and <>0, i get....312K

i assume the excel percentile formula is more accurate? make sense? just confused as to what figure to report.

 

[Oaktree]

You've basically answered your own question... because your n=201 can't be divided into 10 equal buckets, Excel does some interpolation when it calculates the percentiles.

 

[bluepenink]

Hi Oaktree

need you expertise one more time....seems like the formula needs to be dynamic:

issue is : formula is saying, give me the average of all the numbers that are in the top 10% AND the ones that say ie. "Nashville". The problem is, none of ie. Nashville's numbers are in the top 10%. (If i delete all records except for, nashville i get the answer the answer).

any chance index can be added, so it moves with the data? thxs so much again!

 

[Oaktree]

Sure, you can nest IFs within the PERCENTILE function.

{=AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(IF(Data!$R$13:$R$1449<>0,IF(Data!$I$13:$I$1449=results!$C11,Data!$R$13:$R$1449)),0.9),Data!$I$13:$I$1449,results!$C11)}

 

[bluepenink]

Sure, you can nest IFs within the PERCENTILE function.

{=AVERAGEIFS(Data!$R$13:$R$1449,Data!$R$13:$R$1449,">"&PERCENTILE(IF(Data!$R$13:$R$1449<>0,IF(Data!$I$13:$I$1449=results!$C11,Data!$R$13:$R$1449)),0.9),Data!$I$13:$I$1449,results!$C11)}

hi Oaktree

Hmm, its showing #DIVO error for one of the cities

that city (NJ) is in range I1450:I1496 and sales in R1450:R1496. a few rows in there are 0s but hmm is there a reason why the DIVO is showing? if i manually filter the top 90% percentile or top 10% of sales for NJ, i get 89K. the calculation using above gives DIVO - any idea why that may be?

 

[Oaktree]

Hmm... it works for the others, but just NJ is giving you issues? One place I'd look is to make sure the labels are exactly the same. For example, if you're looking for the label "NJ" but the actual data has "NJ " with a trailing space, the formula will return a DIV/0 error because it isn't returning any data for the exact match it's looking for...