Google-sheets – Can Google Sheet’s =AVERAGE function count multiple values only once

google sheets

In Google Sheets, is it possible to count multiple values inside the =AVERAGE function only once?

Example:

=AVERAGE(32.3,48.9,33.2,48.9,33.5,49,33.8,49,33.5,48.5,48.8,33.9)

Count 33.5 only once.

Best Answer

You can use =UNIQUE for this, to remove duplicate values from a list of numbers. Note that =UNIQUE takes an array as parameter, not a list of numbers, so we need to wrap your list of numbers in {}:

=UNIQUE({32.3; 48.9; 33.2; 48.9; 33.5; 49; 33.8; 49; 33.5; 48.5; 48.8; 33.9})

This gives you a list of 9 elements - the duplicates have been removed:

32.3, 48.9, 33.2, 33.5, 49, 33.8, 48.5, 48.8, 33.9

Knowing this, we can combine the two formulas:

=AVERAGE(UNIQUE({32.3; 48.9; 33.2; 48.9; 33.5; 49; 33.8; 49; 33.5; 48.5; 48.8; 33.9}))

which gives the expected result of 40.21111111.

I have created a spreadsheet to demonstrate this, feel free to copy it. And see the documentation for =UNIQUE, =AVERAGE and Using arrays in Google Sheets.

Related Solutions

Google Sheets – Limit the AVERAGE Function

In a google spreadsheet, something like this should work:

=average(array_constrain(filter(B:B, A:A=1),10,1))

or alternatively:

=average(query(A:B, "Select B where A =1 limit 10",0))

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

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.

Best Answer

Related Solutions

Google Sheets – Limit the AVERAGE Function

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

Related Topic