Formula to calculate dinamically the average costs until the last non-blank cell

[vladimiratanasiu]

Hello!

I have a large table with monthly spendings. Some months they still don't happen, so that the correspondent cells are empty. I need a formula to calculate dinamically the average incurred costs until the last period with value, but counting also the previous months without spendings (see the results from column C).

Thank you!

Book1
	A	B	C
1	Month	Euro	Average
2	Jan-22	36.57	36.57
3	Feb-22	44.70	40.635
4	Mar-22
5	Apr-22	46.25	31.88
6	May-22	-7.12	24.08
7	Jun-22
8	Jul-22
9	Aug-22	33.32	19.215
10	Sep-22
11	Oct-22
12	Nov-22	14.36	15.28
13	Dec-22
14	Average
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2/1
C3	C3	=SUM(B2:B3)/2
C5	C5	=SUM(B2:B5)/4
C6	C6	=SUM(B2:B6)/5
C9	C9	=SUM(B2:B9)/8
C12	C12	=SUM(B2:B12)/11

 

[Fluff]

The index needs to be from row 1 like

=AVERAGE(--C4:INDEX(C1:C15,LOOKUP(99^99,C3:C15,ROW(C3:C15))))

 

[vladimiratanasiu]

The index needs to be from row 1 like

=AVERAGE(--C4:INDEX(C1:C15,LOOKUP(99^99,C3:C15,ROW(C3:C15))))

Thank you , Fluff! It's running ok now.

 

[Fluff]

Glad to help & thanks for the feedback.