# How to use AVERAGE inside ARRAYFORMULA in Google Sheets

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.

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