6

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.

3

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)

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.