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):
=AVERAGE(SMALL(D2:BD2,{1,2,3,4,5,6,7,8,9,10}))
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.