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

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
347
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
ABC
38Jan-2167.820.00
39Feb-2161.25104.51
40Mar-2159.190.00
41Apr-2148.36100.60
42May-2149.970.00
43Jun-2148.3679.73
44Jul-2149.970.00
45Aug-2116.1280.09
46Sep-2167.000.00
47Oct-2111.0063.77
48Nov-2144.0035.97
49Dec-210.000.00
53
54
55Jan-2254.0036.57
56Feb-2266.0044.70
57Mar-2278.0053.04
58Apr-2268.0046.25
59May-22-10.00-7.12
60Jun-22
61Jul-22
62Aug-2249.0033.32
63Sep-2227.0018.13
64Oct-22
65Nov-2233.0018.90
66Dec-22
70Dynamic average76.05%56.66%
7163.48%48.40%
7269.78%52.47%
Sheet1
Cell Formulas
RangeFormula
B70:C70B70=SUM(B55:B62)/SUM(B38:B45)
B71:C71B71=SUM(B55:B63)/SUM(B38:B48)
B72:C72B72=SUM(B55:B65)/SUM(B38:B48)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi there,

Try this:

Excel Formula:
=SUM(B55:B66)/SUM(B38:OFFSET(B38,SUMPRODUCT(MATCH(1,0/(B55:B66<>"")))-1,0))
 
Upvote 0
Solution
@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
 
Upvote 0
@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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top