average ifs/ percentile

bluepenink

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
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
 
Upvote 0
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)}
 
Upvote 0
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.
 
Upvote 0
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. ;)
 
Upvote 0
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!
 
Upvote 0
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)}
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top