Average sequentially by top x percent

[shavar]

In reference to http://chandoo.org/wp/2010/06/04/average-of-top-5-values/.

This formula allows you to average 3 numbers at a time in consecutive rows:

=IF((ROW()-2)<CEILING(COUNT($B$2:$B$13)/3,1),AVERAGE(OFFSET($B$2,3*(ROW()-2),0,3)),"")

Is it possible to do this by % of the largest values? For example, I want to average the first largest 25% within the list of values. Then the next 25% of the largest. And so on. I can average the largest 25% of values with:

=AVERAGE(LARGE($B$2:$B$13,ROW(OFFSET($B$2,-1,,CEILING(COUNT($B$2:$B$13)*25%,1))))) CSE

Note that the values to be averaged will be in a dynamic list and won't be sorted.

I hope the attached helps to explain better.

http://chandoo.org/forum/attachments/average_largest_by_top_x_percent-xlsx.34871/

 

[NARAYANK991]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=AVERAGE(LARGE($B$2:$B$13,ROW(OFFSET($B$2,3*(ROW(A1)-1)-1,,CEILING(COUNT($B$2:$B$13)*25%,1)))))

Enter this in E2 , and copy down.

Narayan

 

[Hui]

Top 25%:
=AVERAGEIFS(B2:B13,B2:B13,">="&LARGE(B2:B13, COUNTA(B2:B13) *25%))

50-75%:
=AVERAGEIFS(B2:B13,B2:B13,"<"& LARGE(B2:B13, COUNTA(B2:B13)*25%),B2:B13,">=" &LARGE(B2:B13,COUNTA(B2:B13)*50%))

25-50%:
=AVERAGEIFS(B2:B13,B2:B13,"<"&LARGE(B2:B13,COUNTA(B2:B13)*50%),B2:B13,">="&LARGE(B2:B13,COUNTA(B2:B13)*75%))

0-25%
=AVERAGEIFS(B2:B13,B2:B13,"<"&LARGE(B2:B13,COUNTA(B2:B13)*75%))

 

[shavar]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=AVERAGE(LARGE($B$2:$B$13,ROW(OFFSET($B$2,3*(ROW(A1)-1)-1,,CEILING(COUNT($B$2:$B$13)*25%,1)))))

Enter this in E2 , and copy down.

Narayan

Yep works fine in sample and adapted to production workbook and works in my dynamic list. Thank you.

 

[shavar]

Top 25%:
=AVERAGEIFS(B2:B13,B2:B13,">="&LARGE(B2:B13, COUNTA(B2:B13) *25%))

50-75%:
=AVERAGEIFS(B2:B13,B2:B13,"<"& LARGE(B2:B13, COUNTA(B2:B13)*25%),B2:B13,">=" &LARGE(B2:B13,COUNTA(B2:B13)*50%))
25-50%:
=AVERAGEIFS(B2:B13,B2:B13,"<"&LARGE(B2:B13,COUNTA(B2:B13)*50%),B2:B13,">="&LARGE(B2:B13,COUNTA(B2:B13)*75%))

0-25%
=AVERAGEIFS(B2:B13,B2:B13,"<"&LARGE(B2:B13,COUNTA(B2:B13)*75%))

Works well in sample workbook. In my primary workbook I have a dynamic list so there are blanks...thus #DIV/0! error. I can definitely use this in other scenario though. Thank you.

 

[narunfca]

I feel Hui reply more appropriate with slight modification if it is a dynamic list.
Since it is a dynamic list the entire column is selected.

Top 25%:
=AVERAGEIFS(B:B,B:B,">="&LARGE(B:B, COUNT(B:B) *25%))

50-75%:
=AVERAGEIFS(B:B,B:B,"<"& LARGE(B:B, COUNT(B:B)*25%),B:B,">=" &LARGE(B:B,COUNT(B:B)*50%))

25-50%:
=AVERAGEIFS(B:B,B:B,"<"& LARGE(B:B, COUNT(B:B)*50%),B:B,">=" &LARGE(B:B,COUNT(B:B)*75%))

0-25%
=AVERAGEIFS(B:B,B:B,"<"&LARGE(B:B, COUNT(B:B) *75%))

 

[narunfca]

If you want a more dynamic average list. Type the Number of averages required in A1.

Example: if 25% each - 4, 20% each -5, 10% each -10.

Then type the following formula in E2 and copy down as far as it requires.

=IF(ROW(A1)<=$A$1,AVERAGEIFS(B:B,B:B,">="&LARGE(B:B, COUNT(B:B) *ROW(A1)/$A$1),B:B,IFERROR("<"&LARGE(B:B, COUNT(B:B) *(ROW(A1)-1)/$A$1),"<="&LARGE(B:B,1))),"")

With best regards

Arun N