# How do I refer to dynamic range in google sheet?

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?

Use an absolute-relative reference like this:

`=average(B\$2:B2)`

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.