Rolling Calculation

[ZDR23]

Hi everyone.

I've provided an example of what I'm trying to achieve. Each month new revenue is added and I have to constantly shift my formulas over to compare month over month, year over year, compared to 6 months, and 12 months in columns W-Z. Is there a way to automate when new month data is added to shift the formulas over?

[AliGW]

In W3:

=LET(t,TAKE(FILTER(B3:V3,B3:V3<>""),,-2),IFERROR((TAKE(t,,-1)-TAKE(t,,1))/ABS(TAKE(t,,1)),"N/A"))

In X3:

=LET(t,TAKE(FILTER(B3:V3,B3:V3<>""),,-2),IFERROR((TAKE(t,,-1)-B3)/ABS(B3),"N/A"))

In Y3:

=LET(t,TAKE(FILTER(B3:V3,B3:V3<>""),,-7),IFERROR((TAKE(t,,-1)-(AVERAGE(TAKE(t,,6))))/AVERAGE(TAKE(t,,6)),"N/A"))

In Z3:

=LET(t,TAKE(FILTER(B3:V3,B3:V3<>""),,-13),IFERROR((TAKE(t,,-1)-(AVERAGE(TAKE(t,,12))))/AVERAGE(TAKE(t,,12)),"N/A"))

[AliGW]

Any good to you?

[ZDR23]

AliGW thank you so much! I've checked these and the only one that seems to be off is X3. For example, when August data is added, it is now comparing August 2023 to July 2022, and it should be comparing to August 2022.

[Glenn Kennedy]

One formula, W3, copied down:

Please Login or Register  to view this content.

[Glenn Kennedy]

I forgot to add... delete ALL expected results first!!

[ZDR23]

Thank you Glenn, this is great! However I am noticing that it isn't working quite correct when there are blank values or negative values in certain months. In my original calculations I am dividing by ABS value to get accurate numbers and to also show NA when there are blanks. Let me know if this is still possible with your method.

[ZDR23]

@AliGW is there a way to fix X3. For example, when August data is added, it is now comparing August 2023 to July 2022, and it should be comparing to August 2022.

[Glenn Kennedy]

Show what you mean in a sample sheet.

[AliGW]

Yes, try this:

=LET(t,TAKE(FILTER(B3:V3,B3:V3<>""),,-13),IFERROR((TAKE(t,,-1)-TAKE(t,,1))/ABS(TAKE(t,,1)),"N/A"))

[ZDR23]

Glenn, example attached. Discrepancies shown in orange between the original calculation and the formula driven one.

[AliGW]

See post #10.

[ZDR23]

AliGW take a look at attached. The bottom table is using your formulas. It doesn't like when there are blank values.

[AliGW]

Were there blank values in the original sample file? No - so before I look, is there anything else that needs considering?

Easiest if you just add 0s to those blank cells.

[ZDR23]

No sorry there weren't any in the original sample file. I was just testing in my real data set and noticed this. So no, there is nothing else that needs considering.

[AliGW]

So just make sure those cells are populated with 0 instead of blank. Otherwise it means a complete formula rewrite ... Full disclosure in future, please! Otherwise you are effectively wasting our time.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[ZDR23]

Yea thanks I'm just waiting on additional feedback from Glenn before I mark the thread as solved. And I've already added reputation for you both.

[ExceLogan]

hi there, I allow to propose this alternative solution that spills all the expected results:

Please Login or Register  to view this content.

I hope it resuls useful.

Example (Alternative Solution).xlsx

[ZDR23]

Thanks alot ExceLogan! Your formula works great with blank values and negative values. The only one that's not computing correctly is Column X "PY". When new month data is added is doesn't shift over to the new month to compare to. For example, when I added data into Sept 2023, it should compare to Sept 2022.

[AliGW]

Regarding your PM to me, the way to resolve the issue is in post #16 in this thread. Give me one good reason why those cells should not contain 0s.

[ZDR23]

One great reason is that the source of my data has it as a blank and not a 0. I'm talking thousands of rows of data that I copy and paste into my template. So instead of me changing them all to 0's, I might as well stick to my original calculation and manually shift my formulas.

[AliGW]

Select just the the populated columns - Home ribbon - Find & Replace - leave the Find box blank and put 0 into the Replace box - Replace All - done. Twenty seconds, maybe???

[ZDR23]

I'm aware of how to use find & replace. The purpose of me seeking assistance within this forum is to eliminate having to do such things.

Given that I didn't provide all scenarios, my bad. Thanks anyway.

[AliGW]

Well, I am not aware of what you are and are not aware of, but I'd have thought that it would be easy to use on your data.

The problem with the formula approach is that Excel sees all blank cells as equal, so telling it what to include and what not to include becomes very tricky in your scenario. I spent quite some time trying to overcome it and couldn't, hence my advice.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[ExceLogan]

Thanks alot ExceLogan! Your formula works great with blank values and negative values. The only one that's not computing correctly is Column X "PY". When new month data is added is doesn't shift over to the new month to compare to. For example, when I added data into Sept 2023, it should compare to Sept 2022.

hi ZDR23, try with this formula:

Please Login or Register  to view this content.

I hope it results useful.