Google-sheets – How to use AVERAGE inside ARRAYFORMULA in Google Sheets

google sheetsgoogle-sheets-arrayformula

I want to use the ARRAYFORMULA version of 

=AVERAGE(Linda!B3, Nandi!B3, David!B3, Remmelt!B3)

I want the cell to calculate the average value of the entries in that cell from the other tabs (Linda, Nandi, David, Remmelt). I need to use AVERAGE and not just sum and division, because I need to deal with the fact that not everyone will enter values in all cells.

I tried using

=ARRAYFORMULA(AVERAGE(Linda!B3:B, Nandi!B3:B, David!B3:B, Remmelt!B3:B))

but then it average over all four columns, not just the four cells I want.

As far as I understand ARRAYFORMULA takes an input of type array and displays it over as many cells. So the problem comes down to making AVERAGE (or similar) output an array.

Best Answer

Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA.

One alternative is to use the arithmetic operators (+,-,/,*).

If it makes sense to assume that

  • all ranges are of the same size
  • there aren't blank cells between non-blank cells
  • blank cells at the bottom should be ignored

then the following formula could be used to calculate the average: 

=ARRAY_CONSTRAIN(
   Linda!B3:B+Nandi!B3:B+David!B3:B+Remmelt!B3:B,
   1,
   COUNT(Linda!B3:B)
 )
 /
COUNT(Linda!B3:B)
Related Topic