Log In
Register
Home
July 15, 2020
Offline
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.)
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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?
July 15, 2020
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
Isn't it just:
=AVERAGEIF($A$1:$A$60,"<="&SMALL($A$1:$A$60,10))
Answers Post
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
@Velouria
Yes indeed. Sometimes I'm just overthinking 🙂
Trusted Members
Moderators
November 1, 2018
Offline
Been there, done that. 🙂 Very easy to get fixated on a specific path...
1 Guest(s)
