Hello:

I have an existing formula where I calculate the average of top 5, bottom 5 and middle X of a data set, for example student's correct answer. The formula like this...

Sample Data:

Top 5 avg: = SUM(LARGE($B$2:B$32,{1,2,3,4,5}))/5

Middle x avg: = (SUM(B$2:B$32) - (SUM(LARGE($B$2:B$32,{1,2,3,4,5})) + SUM(SMALL($B$2:B$32,{1,2,3,4,5}))) ) / (COUNTA(B$2:B$32)-5-5)

Bottom 5 avg: = SUM(SMALL($B$2:B$32,{1,2,3,4,5}))/5

Since my list of students can be more or less, i,e, some class can have 30 students, some can have 50, and some can have 100, I am trying to determine a dynamic formula to determine the following, i.e.

Top 20% Avg.

Middle 60% Avg.

Bottom 20% Avg.

Using my existing formula for Top 5 Avg, Bottom 5 Avg and Middle x avg, how can I make the formula dynamic based on % instead of actual fixed #

Thank you in advance.

Regards

Chieh

I have an existing formula where I calculate the average of top 5, bottom 5 and middle X of a data set, for example student's correct answer. The formula like this...

Sample Data:

Student | Correct Answer Count |

Student 1 | 200 |

Student 2 | 75 |

Student 3 | 374 |

Student 4 | 139 |

Student 5 | 277 |

Student 6 | 185 |

Student 7 | 2 |

Student 8 | 0 |

Student 9 | 1 |

Student 10 | 94 |

Student 11 | 235 |

Student 12 | 55 |

Student 13 | 27 |

Student 14 | 121 |

Student 15 | 78 |

Student 16 | 111 |

Student 17 | 12 |

Student 18 | 117 |

Student 19 | 6 |

Student 20 | 4 |

Student 21 | 87 |

Student 22 | 65 |

Student 23 | 23 |

Student 24 | 14 |

Student 25 | 200 |

Student 26 | 100 |

Student 27 | 5 |

Student 28 | 27 |

Student 29 | 3 |

Student 30 | 65 |

Student 31 | 16 |

Top 5 Avg. | 257 |

Middle 21 Avg. | 68 |

Bottom 5 Avg. | 2 |

**Formula For:**Top 5 avg: = SUM(LARGE($B$2:B$32,{1,2,3,4,5}))/5

Middle x avg: = (SUM(B$2:B$32) - (SUM(LARGE($B$2:B$32,{1,2,3,4,5})) + SUM(SMALL($B$2:B$32,{1,2,3,4,5}))) ) / (COUNTA(B$2:B$32)-5-5)

Bottom 5 avg: = SUM(SMALL($B$2:B$32,{1,2,3,4,5}))/5

Since my list of students can be more or less, i,e, some class can have 30 students, some can have 50, and some can have 100, I am trying to determine a dynamic formula to determine the following, i.e.

Top 20% Avg.

Middle 60% Avg.

Bottom 20% Avg.

Using my existing formula for Top 5 Avg, Bottom 5 Avg and Middle x avg, how can I make the formula dynamic based on % instead of actual fixed #

Thank you in advance.

Regards

Chieh