4

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}))
5

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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