Sunday, April 17, 2011

Find out the sum or average of top or bottom N numbers in Excel

In my Excel Worksheet, I have random number from cell A1 to A10. My requirement is to find out result of following queries in cell A11.
  1. To find the average of top 5 numbers, Excel formula of cell A11 will be =AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))
  2. To find average of bottom 5 numbers , Excel formula of cell A11 =AVERAGE(SMALL(A1:A10,{1,2,3,4,5}))
  3. Excel formula of cell A11 for the sum of bottom 3 numbers will be =SUM(SMALL(A1:A10,{1,2,3}))
    Important Note:
    • AVERAGE is the function for finding average within a range as we all know.
    • LARGE is the function for finding TOP numbers with a range.
    • SMALL is the function for finding BOTTOM numbers with a range.
    • {1,2.3} means to find TOP or BOTTOM 3 numbers depending on the function you chose.
    • {1,2.3,4,5} means to find TOP or BOTTOM 5 numbers depending on the function you chose.