0

Here is my sheet:

     A       B            C
1    ID      VALUE        MY VAL
2    1        500         = AVERAGE(B2:B2)
3    2        400         = AVERAGE(B2:B3)
4    3        550         = AVERAGE(B2:B4)
5    4        700         = AVERAGE(B2:B5)
6    5       1200         = AVERAGE(B2:B6)

.......

This is a very long sheet so I can't input the AVERAGE(B2:Bx) manually. Is there any good ideas to solve this?

2 Answers 2

0

Use an absolute-relative reference like this:

=average(B$2:B2)

0

Place the following formula in C2:

=BYROW(SEQUENCE(COUNTA(B2:B)), LAMBDA(r,
   AVERAGE(QUERY(B2:B, "LIMIT "&r))))
  1. COUNTA returns the number of values in B2:B
  2. SEQUENCE returns an array of numbers from 1 to the result of COUNTA
  3. BYROW passes the array of numbers row by row into a LAMBDA that stores the current number in r
  4. The LAMBDA's formula uses QUERY to return B2:B limited to the first r rows, and that is used in the AVERAGE function.
  5. The process is repeated until each r has been processed.

Functions

AVERAGE  BYROW  COUNTA  LAMBDA  QUERY  SEQUENCE

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.