Google-sheets – Finding the average of top 10 smallest values in Google Sheets

google sheets

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

Best Answer

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.

Related Topic