6

I'm looking for a formula to find the average of the top 10 smallest values within a dataset. It works in Excel but not in Google Sheets.

E.g. (that works in Excel):

enter image description here

=AVERAGE(SMALL(D2:BD2,{1,2,3,4,5,6,7,8,9,10}))

1 Answer 1

8

In Google Sheets, you need to wrap small in arrayformula so it produces an array:

=average(arrayformula(small(D2:BD2, {1,2,3,4,5,6,7,8,9,10})))

There is an alternative solution, which is shorter and scales better if you will need 100 smallest instead of 10 smallest numbers:

=average(array_constrain(sort(D2:BD2), 10, 10))

Here, sort sorts the array in the increasing order. The array_constrain limits the output to at most 10 rows and 10 columns, which in practice means 10 smallest elements, regardless of whether your data was in a row or in a column.

1
  • Perfect, the first option works the best for what I am after. Thank you.
    – Lisa T
    Feb 8, 2016 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.