vladimiratanasiu
Active Member
- Joined
- Dec 17, 2010
- Messages
- 347
- Office Version
- 365
- 2021
- Platform
- 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!
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) |