How do I create a function to find the lowest range of numberS* instead of just a single number in excel?

Question

Microsoft Excel - I want to find the average of the lowest 5 numbers in a range of 10 numbers and put it all in a function. For example, I have the numbers 5, 5, 10, 15, 15, 121, 134, 139, 392, 133. I want to find the average for the lowest five numbers in this range. The answer would be 10 because the average for the lowest five numbers (5, 5, 10, 15, 15) equals 10. I want to create a function that can do this. I know there is a min function but that only finds one value. What about multiple values and then creating an average from those?

Answer 1

You may have to do this one in a couple steps or write a macro.  

Let's say your range of numbers is in cells A1 through A10.

What I would recommend is using a second set of cells B:B that uses the RANK(A1,A:A,-1) function to determine the 5 lowest numbers.  

Then you can use SUMIF(B:B,"<=5",A:A)/COUNTIF(B:B,"<=5") to calculate your average.

Answer 2

interesting question.

i had to google it (see source) its a page packed with info.

assuming your numbers are in A1 to A10

copy&paste this formula:

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

this is an array formula so instead of hitting the usual Enter...

you have to hit Ctrl-Shift-Enter

if you hit all 3 keys correctly, you will see curly brackets { and } appear, and you will get 10.  you cant type those brackets in manually.

if you just hit enter, i ended up with the wrong answer 5