average ifs/ percentile

bluepenink

Well-known Member
Joined
Dec 21, 2010
Messages
583
all

having some issues with average ifs logic.

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

Code:
{=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?

Code:
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.
 
Last edited:

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
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
Code:
AVERAGEIFS(range for math, range for criterion 1, criterion 1, range for criterion 2, criterion 2)
You have

Code:
AVERAGEIFS(range for criterion 1, criterion 1, range for criterion 2, criterion 2)
Maybe this?

Code:
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

Well-known Member
Joined
Dec 21, 2010
Messages
583
Hi Oaktree

this one works fine for sure :)

Code:
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
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.

Code:
{=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

Well-known Member
Joined
Dec 21, 2010
Messages
583
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.

Code:
{=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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
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

Well-known Member
Joined
Dec 21, 2010
Messages
583
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
Sure, you can nest IFs within the PERCENTILE function.

Code:
{=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

Well-known Member
Joined
Dec 21, 2010
Messages
583
Sure, you can nest IFs within the PERCENTILE function.

Code:
{=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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
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...
 

Forum statistics

Threads
1,082,618
Messages
5,366,619
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top