I have a column of about 60 numbers, and I want to find the average of the lowest 10. There may be some duplicates as well, and they would have to be included in the calculation. I've tried using the AVERAGE formula, but I'm not sure if it gave me the correct result. (In the past Mynda gave me a formula that worked, but I have moved on from that job and no longer have access to the file that contains the formula.)
Needed to think this through a bit and hope you have Excel 365 or 2021. Was wondering if you wanted to average the 10 smallest numbers including any duplicates or the 10 smallest unique numbers including their duplicates.
The attached example puts both scenarios to the test. Any of these fits yours?
Riny, thanks for the reply. I would want the 10 smallest numbers including any duplicates (your 1st example). I am using Excel 2016, so I suspect the formula will be different than the one you gave me.
Hi Tom,
I'm convinced that it's possible with old-style Excel, but I believe it's going to require quite a bit of complicated formula writing, copying and pasting. Not something I'm very good at, unfortunately.
But perhaps you can live with a pivot table solution.
Determine the 10th smallest number in the data set: =SMALL(A2:A61,10)
Create a pivot table on the basis of all the numbers. Number in the Row area, Number (as average) in the Value area. Then manually filter the Row labels for all less than or equal to the 10th smaller number. The answer is in the Grand Total at the bottom of the table.
Not pretty perhaps, but no complex formulas needed.
File attached.
Isn't it just:
=AVERAGEIF($A$1:$A$60,"<="&SMALL($A$1:$A$60,10))
@Velouria
Yes indeed. Sometimes I'm just overthinking 🙂
Been there, done that. 🙂 Very easy to get fixated on a specific path...
How true, blind spots pop-up out of no where
Thanks, all. Both solutions work!
