1

So, right now im working on another spreadsheet project and this time i want to know how to use Average Formula with Array formula to make them automatically dragdown each time a new data entered.

Here is the picture from my spreadsheet. So i want to average them from Column CH to Column CL using average formula with arrayformula.

Example

I've tried all 'Averageif' or 'Average' and using arrayformula but it turns out '#DIV/0'

Can you guys please help me with it? Thankyou.

Sung
  • 17
  • 4

2 Answers2

5

It's not going to work. Average formula works on arrays so it won't change anything when you try nest it with arrayformula. For example. If you try to average 3 values from 3 columns, you could try: arrayformula(average(CH1:Ch,CI1:CI,CL1:CL)) but it will return the same result as average(CH1:Ch,CI1:CI,CL1:CL) - an average of all values from all 3 ranges.

I suggest workaround and make average manually (sum of elements divided by it's number):

=Arrayformula( (CH1:Ch + CI1:CI +CL1:CL)/3)
Krzysztof Dołęgowski
  • 2,583
  • 1
  • 5
  • 21
0

these days it can be done like:

=INDEX(IFERROR(1/(1/BYROW(OFFSET(CH2,,,
 MAX((INDIRECT("CH2:"&ROWS(CH:CH))<>"")*ROW(CH2:CH)), 5), 
 LAMBDA(x, AVERAGE(x))))))

enter image description here

for more variations see: https://stackoverflow.com/a/65435321/5632629

player0
  • 124,011
  • 12
  • 67
  • 124