How can I average certain number of lowest values in a column?

C

Chip Pearson

The following array formula will average the lowest 5 numbers in
A1:A10.

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:5"))))

Change the 1:5 to 1:N where N is the number of values you want to
average. 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 {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 

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

lowest number with a colr 1
How do I get the lowest number in a row to turn red in Excel ? 5
Trying to find additional ranges outside of min, max, and average 2
How do I get Excel to make highest #, 1 color, & lowest # another? 1
How do I make the smallest number in a column red ( No VBA Please ) 11
Average formula 2
Find min() value greater than zero in column of numbers 2
average out of bottom 10 values 5

Top