11

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.

Improve this question

2 Answers 2

Reset to default
6

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)
Improve this answer
0

Some "statistical" and "math" type functions - AVERAGE, COUNT, MAX, MIN, SUM - have corresponding *IF and / or *IFS variants that, when combined with the INDEX function, seem to satisfy this sort of need.

For example, I have a sheet named monthly with dates in column A and dollar amounts in column B. I wanted to get the average of dollar amounts for rows where the date was in the year 2021.

Combining INDEX and AVERAGEIF worked for me ...

=INDEX(AVERAGEIF(YEAR(monthly!$A$2:$A), 2021, monthly!$B$2:$B))

Functions of this type include ...

Singular forms (*IF) match rows based on a single criterion. Plural forms (*IFS) match rows based on multiple criteria.

Improve this answer
3
  • 1
    Are you saying that =INDEX(AVERAGEIF(YEAR(monthly!$A$2:$A), 2021, monthly!$B$2:$B)) works like an arrayformula?
    – Tedinoz
    Commented Apr 26, 2021 at 6:36
  • @Tedinoz, yep, it's true. But it's not like ARRAYFORMULA. Just the first argument of INDEX is iterable. And this works like ARRAYFORMULA.
    – oshliaer
    Commented May 31, 2021 at 2:03
  • 1
    @rh-becker it's doesn't work like expected.
    – oshliaer
    Commented May 31, 2021 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.