Average of the top 25%

[brandon20]

Hi, I am trying to find the top 25% fastest times of these items then find the average.

I know I can find the average by doing an averageifs
=(AVERAGEIFS(C2:C25,A2:A25,"MA",B2:B25,"NW"))

But now I need to separate out the Top 25% fastest times.
Then take the average again. How would I do this in one equation?
Would I use the quartertile function?

Any help is greatly appreciated. Thanks!

Res	Type	time
MA	ST	37.5
LB	NW	48.1
MA	ST	50.4
LB	NW	55.2
MA	ST	43.375
ZU	NW	53.375
MA	ST	36
ZU	NW	166.5417
MA	ST	25.85417
MA	ST	25.625
MA	ST	31.375
MA	ST	38.875
MA	ST	53.64583
RT	NW	79.4375
MA	ST	45.9
MA	ST	23.125
MA	ST	32.79167
MA	ST	40.27083
LB	NW	43.416
MA	NW	66.375
UB	NW	59.20833
LB	NW	58.6666
LB	NW	59.39583
UB	ST	40.77083

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[AlphaFrog]

Try this...

=AVERAGEIF(C2:C25, ">="&LARGE(C2:C25,6))

 

[mrmmickle1]

Here are some ideas. COULDN'T THINK OF A WAY TO GET IT ALL INTO ONE FORMULA.....

Excel 2012
	A	B	C	D	E	F	G
1	Res	Type	time	Sub Calc		Check Answer
2	MA	ST	37.5	6		1	166.5417
3	LB	NW	48.1	Answer		2	79.4375
4	MA	ST	50.4	81.60416		3	66.375
5	LB	NW	55.2			4	59.39583
6	MA	ST	43.375			5	59.20833
7	ZU	NW	53.375			6	58.6666
8	MA	ST	36			Average	81.60416
9	ZU	NW	166.5417
10	MA	ST	25.85417
11	MA	ST	25.625
12	MA	ST	31.375
13	MA	ST	38.875
14	MA	ST	53.64583
15	RT	NW	79.4375
16	MA	ST	45.9
17	MA	ST	23.125
18	MA	ST	32.79167
19	MA	ST	40.27083
20	LB	NW	43.416
21	MA	NW	66.375
22	UB	NW	59.20833
23	LB	NW	58.6666
24	LB	NW	59.39583
25	UB	ST	40.77083
Sheet1

Cell Formulas
Range	Formula
D2	=ROUND(COUNT(C2:C25)/4,1)
D4	{=AVERAGE(LARGE(C2:C25,{1,2,3,4,5,6}))}
G2	=LARGE($C$2:$C$25,F2)
G3	=LARGE($C$2:$C$25,F3)
G4	=LARGE($C$2:$C$25,F4)
G5	=LARGE($C$2:$C$25,F5)
G6	=LARGE($C$2:$C$25,F6)
G7	=LARGE($C$2:$C$25,F7)
G8	=AVERAGE(G2:G7)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrmmickle1]

Good Thinking AlphaFrog! I learn something new everyday!

 

[VBA Geek]

=averageifs(c2:c25,c2:c25,">="&percentile(c2:c25,0.75))

 

[brandon20]

Thanks for your help everybody. I think VBA Geek's works best for what I am trying to do.

I was wondering if there was a way to single out the MA in the column A, and then find the the average of the top 25 percent.

To clarify, this formula works
=averageifs(c2:c25,c2:c25,">="&percentile(c2:c25,0.75))


but I need to be able to find the average of the top 25 percent of "MA" in column A.

I also need to be able to find the top 25 percent of "MA" in column A that is also a "NW" in column B.

I understand that these the data I gave will not give a very important number but this is just a small sample of my data.


Thanks for any additional help.

 

[VBA Geek]

try:

=AVERAGEIFS(C2:C25,A2:A25,"MA",B2:B25,"NW",C2:C25,">="&PERCENTILE(IF(A2:A25="MA",IF(B2:B25="NW",C2:C25)),0.75))

Control shift enter

 

[brandon20]

THANKS!!!!

That works great.

I hate to do this to you, but there is no way to use AverageIFS for two different values in the same column correct?
I have tried this before and had to go another route.

We just looked up "MA" in column A and "NW" in column B.

What if I wanted to look up "MA" AND "LB" in column A and "NW" in column B.

AverageIFS function does not have this ability right?

I have used an Average(IF((A2:A25="MA")+(A2:A25="LB"),C2:C25)) to get the average before but did not know how to through in column B "NW".

I would like to think that averageIFS can handle this but Im not sure.

 

[VBA Geek]

Try this, also Control Shift Enter


=AVERAGE(IF(A2:A25={"MA","LB"},IF(B2:B25="NW",IF(C2:C25>=PERCENTILE(IF(A2:A25={"MA","LB"},IF(B2:B25="NW",C2:C25)),0.75),C2:C25))))

 

[brandon20]

THANKS!

that really clears up some issues I was having inside this average(If equations.

so in order to put two different variables from the same row into the equation I just need to put the {"MA","LB"} brackets around them. At least this is what seems to work.
Much more efficient than adding the two together.

Thanks again!