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

 

[SanjayGMusafir]

Instead of using this -

=SUM(B2:B3)/2

Use this from C2 itself and fill down

=SUM($B$2:B2)/COUNTA($B$2:B2)

And if that does not counts blank cells then use

=SUM($B$2:B2)/(COUNTA($B$2:B2)+COUNTBLANK($B$2:B2))

That shall work. Try it.

Making First cell B2 absolute and later B2 relative makes formula dynamic.

 

[Fluff]

Another option

=IF(B2="","",AVERAGE(--B$2:B2))

 

[vladimiratanasiu]

Thank you, Fluff and SanjayGulatiMusafir, for your responses!

I think that both formulas covers only partially my needs, the final solution looking like a combination between them. The numbers inserted in the column C of my table are only for examples of the desired values. I wish the result be only a final / single, not more succesive ones, and placed in the cell C14. It should reflect the progressive value, reached from the first non-blank cell (B2) to the next similar one (B3,B5 etc.), but including in the calculation the empty ones between them. So, the formula should relate to a dynamic range (B2:B3, B2:B5 etc), extending automatically, not manually, the references to the new non-empty cell.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[SanjayGMusafir]

Thank you, Fluff and SanjayGulatiMusafir, for your responses!

I think that both formulas covers only partially my needs, the final solution looking like a combination between them. The numbers inserted in the column C of my table are only for examples of the desired values. I wish the result be only a final / single, not more succesive ones, and placed in the cell C14. It should reflect the progressive value, reached from the first non-blank cell (B2) to the next similar one (B3,B5 etc.), but including in the calculation the empty ones between them. So, the formula should relate to a dynamic range (B2:B3, B2:B5 etc), extending automatically, not manually, the references to the new non-empty cell.

Let me understand - You want average in Cell C14 from B2 to the cell in Column B that corresponds to the current month. Right or is it something different?

 

[vladimiratanasiu]

Glad we could help & thanks for the feedback.

Glad we could help & thanks for the feedback.

Sorry, but no green bar is active in my messages.

 

[Fluff]

You edited your post after I saw it, which is why I responded as I did.

 

[Fluff]

What are you expecting for the result, 15.28, 14 or something else?

 

[vladimiratanasiu]

Let me understand - You want average in Cell C14 from B2 to the cell in Column B that corresponds to the current month. Right or is it something different?

Exactly, considering it just an example. However, the formula references shouldn't refer to the whole month / larger period, but only to the area between the first and last non-empty cell. And as the values are added progressively, the formula should become a dynamic one, to cover the new non-blank cells too.