Average of bottom 10 values in a range

  • Thread starter Thread starter Matt Jameson
  • Start date Start date
M

Matt Jameson

What is the formula for taking the average of only the
*bottom 10* values in a range of 20?

Thank you for your help!

-Matt
 
Hi Matt,
This must be entered as an array function (end with Shift+Ctrl+Enter)
=AVERAGE(SMALL(A1:B100,{1,2,3,4,5,6,7,8,9,10}))
Bernard
 
Matt,

Use the following array formula:
=AVERAGE(SMALL(A1:A20,ROW(INDIRECT("1:10"))))

Since this is an array formula, you must press Ctrl+Shift+Enter rather than
just Enter when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces {}.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to find additional ranges outside of min, max, and average 2
average out of bottom 10 values 5
Averaging data across a date range 1
Scrabble Value calculation for Welsh words 0
Averaging data from a range calculated using cell reference number 3
Average Ranges of Cells, Return Separate Value for Each Range 5
conditional formula 10
Non-contiguous range and Sumproduct to average 5

Back
Top