Divide the sum of values from the same periods of two consecutive years

[vladimiratanasiu]

Hello!

I have more annual lists, with different monthly values. Sometimes, there is no one monthly recording. I need a solution, to divide similar periods of two years (e.g. B70, C70 - Jan-Aug 2022/ Jan-Aug 2021; B71, C71 - Jan-Nov 2022 / Jan-Nov 2021), covered from January to the last non-blank month. I mention also that the latest year has a dynamic status, meaning it is progressively filled with new values. That is why, formula should consider automatically the new inputs, added in the future.

Thank you!

Book1
	A	B	C
38	Jan-21	67.82	0.00
39	Feb-21	61.25	104.51
40	Mar-21	59.19	0.00
41	Apr-21	48.36	100.60
42	May-21	49.97	0.00
43	Jun-21	48.36	79.73
44	Jul-21	49.97	0.00
45	Aug-21	16.12	80.09
46	Sep-21	67.00	0.00
47	Oct-21	11.00	63.77
48	Nov-21	44.00	35.97
49	Dec-21	0.00	0.00
53
54
55	Jan-22	54.00	36.57
56	Feb-22	66.00	44.70
57	Mar-22	78.00	53.04
58	Apr-22	68.00	46.25
59	May-22	-10.00	-7.12
60	Jun-22
61	Jul-22
62	Aug-22	49.00	33.32
63	Sep-22	27.00	18.13
64	Oct-22
65	Nov-22	33.00	18.90
66	Dec-22
70	Dynamic average	76.05%	56.66%
71		63.48%	48.40%
72		69.78%	52.47%
Sheet1

Cell Formulas
Range	Formula
B70:C70	B70	=SUM(B55:B62)/SUM(B38:B45)
B71:C71	B71	=SUM(B55:B63)/SUM(B38:B48)
B72:C72	B72	=SUM(B55:B65)/SUM(B38:B48)

 

[Leo Skywalker]

Hi there,

Try this:

=SUM(B55:B66)/SUM(B38:OFFSET(B38,SUMPRODUCT(MATCH(1,0/(B55:B66<>"")))-1,0))

 

[vladimiratanasiu]

Hi there,

Try this:

=SUM(B55:B66)/SUM(B38:OFFSET(B38,SUMPRODUCT(MATCH(1,0/(B55:B66<>"")))-1,0))

Thank you, Leo! Your formula is exactly what I need.

 

[Fluff]

@vladimiratanasiu when marking a post as the solution you need to mark the post with the solution, not your post saying it worked. I have changed it for you this time. Thanks

 

[vladimiratanasiu]

@vladimiratanasiu when marking a post as the solution you need to mark the post with the solution, not your post saying it worked. I have changed it for you this time. Thanks

Sorry, but in my answer box is seen also the Leo's name and formula / solution. That's why, I considered it's ok to mark it there.

 

[Leo Skywalker]

Thank you, Leo! Your formula is exactly what I need.

You're welcome!, glad I could help!