Rolling average cascading (spill) dynamic range formula

[leolapa]

I have the following table below...

1D Spilled Range Formula (Rolling Average Staggering Array).png

The light blue shaded fields are manual inputs of historical data.

Columns "C" through "H" are calculated fields, as well as column "A" from the second month going forward.

I managed to come up with cascading/spill formulas for the following columns:

Month (cell A7):
=EOMONTH($A$6;ROW($B$7:$B$41)-ROW($B$6))

Δ% vs. prior month (cell C6):
=IF(ISNUMBER($B$5:$B$40)=TRUE;($B$6:$B$41/$B$5:$B$40)-1;"N/A ")

Δ% vs. 12 months prior (cell E6):
=IFS(EOMONTH(DATE(YEAR($A$6:$A$41);MONTH($A$6:$A$41)-11;DAY($A$6:$A$41)-11);0)=$A$6;$G$6#;EOMONTH(DATE(YEAR($A$6:$A$41);MONTH($A$6:$A$41)-12;DAY($A$6:$A$41)-12);0)<$A$6;"N/A ";TRUE;($B$6:$B$41/INDEX($A$6:$H$41;MATCH($A$6:$A$41;$A$6:$A$41;0)-12;MATCH($B$4;$A$4:$H$4)))-1)

Running Δ%, within year (cell G6):
=IF(ISNUMBER($B$5:$B$40)=TRUE;($B$6:$B$41/INDEX($A$6:$H$41;MATCH($A$6:$A$41;$A$6:$A$41;0)-MONTH($A$6:$A$41);MATCH($B$4;$A$4:$H$4;0)))-1;"N/A ")

Running Δ%, whole period (cell H6):
=IF(ISNUMBER($B$5:$B$40)=TRUE;($B$6:$B$41/$B$6)-1;"N/A ")

But I've been trying every trick in the book (at least the ones I know of) to come up with a cascading/spill formula solution for the rolling average columns ("D" and "F") to no avail.

It seems to me the issue at hand that prevents me from getting there is the fact that this calculation deals with 12-month arrays that not only are just a piece of their respective columns but they also stagger down the table...

The standard formulas are as per the following:

Rolling average vs. prior month (cell D6):
=IFERROR(IF(DATE(YEAR($A6);MONTH($A6)-12;DAY(EOMONTH($A6;-12)))<$A$6;"N/A ";AGGREGATE(1;6;OFFSET($A$1;ROW(INDEX($A$4:$H$41;MATCH($A6;$A$6:$A$41;0)-11;MATCH($C$4;$A$4:$H$4;0)));COLUMN($C$4)-1;12)));"N/A ")

Rolling average vs. 12 months prior (cell F6):
=IFERROR(IF(DATE(YEAR($A6);MONTH($A6)-23;DAY(EOMONTH($A6;-23)))<$A$6;"N/A ";AGGREGATE(1;6;OFFSET($A$1;ROW(INDEX($A$4:$H$41;MATCH($A6;$A$6:$A$41;0)-11;MATCH($E$4;$A$4:$H$4;0)));COLUMN($E$4)-1;12)));"N/A ")

Could anyone please shed a light here?

Leonardo

[AliGW]

You need to show the results you are expecting. I have no idea what you perceive to be wrong about what the formula is producing now.

[JohnTopley]

In D18

=AVERAGE(OFFSET($C18,-12,0,12,1))

or

=AVERAGE($C6:$C17)

Copy down: figures agree with those on the table in your post.

In F30

=AVERAGE($E17:$E28)

Copy down

[JohnTopley]

As a start, replace

(DATE(YEAR($A29),MONTH($A29)-23,DAY(EOMONTH($A29,-23)))

with

EDATE($A29,-23)

[leolapa]

You need to show the results you are expecting. I have no idea what you perceive to be wrong about what the formula is producing now.

Thanks AliGW,

The figure results I'm expecting to get are already being shown on both the table displayed on my question and the attached file, since the provide calculations are all correct.

What I'm aiming here is to find a way to have the standard-type rolling average formulas on columns D and F replaced with formulas that spill/cascade down the adjacent cells (i.e.: one single formula input on cell D6 would populate the entire column all the way down through cell D41), just the way I already have on columns C, E, G and H.

Leonardo

[leolapa]

In D18

=AVERAGE(OFFSET($C18,-12,0,12,1))

or

=AVERAGE($C6:$C17)

Copy down: figures agree with those on the table in your post.

In F30

=AVERAGE($E17:$E28)

Copy down

Thanks for your reply John.

Your solution provides standard formulas that do not cascade/spill down the adjacent cells just as the ones I already have on columns C, E, G and H, and that's what I'm actually aiming for on this question.

The rolling average calculations I already have, what I don't have and would like to get at is a way to achieve the same results but not having to copy/paste the formulas down through the entire column.

Leonardo

[AliGW]

I'm sorry - I don't understand what you mean.

Oh, wait - yes, I see what you mean now.

[JohnTopley]

Just dragging down a formula a few rows hardly constitutes a onerous task: and why replace a very simple formula with a complex one?

As I pointed out earlier in your current "spill" formula replace

(DATE(YEAR($A29),MONTH($A29)-23,DAY(EOMONTH($A29,-23)))

with

EDATE($A29,-23)

[AliGW]

I think the blocker here is the fact that the formula is referencing parts of the array with the OFFSET and therefore spilling won't be possible, if I've understood what I've just read about spill arrays correctly.

I'm with John on this: is this really a case of style over substance?

[leolapa]

I'm sorry - I don't understand what you mean.

Oh, wait - yes, I see what you mean now.

I believe the screenshots below should help...

1D Spilled Range Formula (Rolling Average Staggering Array) 1.png
1D Spilled Range Formula (Rolling Average Staggering Array) 2.png

If you look at the formula bar on both the screenshots shown above you'll notice the lower one (cell C7 selected) is greyed out, since the formula on this column is only input on cell C6 and its results are spilled down the column all the way through cell C41.

1D Spilled Range Formula (Rolling Average Staggering Array) 3.png
1D Spilled Range Formula (Rolling Average Staggering Array) 4.png

However the formula bars on both screenshots above (selecting cells D6 and D7) are NOT greyed out because in order to get the rolling averages I need the only way I could find was via standard formula that doesn't spill as the one shown on the prior couple screenshots.

Leonardo

[AliGW]

Yes - I get it, as I said in my post. See posts #8 and #9.

[leolapa]

Just dragging down a formula a few rows hardly constitutes a onerous task: ...

Thanks John,

Please keep in mind that my day-to-day challenge is having to work with huge datasets and workbooks that span several different tabs, thousands if not millions of formulas that considerably slow things down while I work - I believe the kind of situation you might be used to having as well.

Therefore I'm in constant need to retrofit what I've got with features that were made available to us (LAMBDA/LET, recursive LAMBDA, dynamic/spill ranges, structured referencing, etc. -- some fairly recently, some not) in order to lighten those workbooks and consequently speed things up...

The example I shared in my question was only a snippet of what I've got in order to keep my case in point and not bog you down with unnecessary info, just as suggested by this very forum.

I would sure agree with you if my whole problem was around what I shared, but I actually mean to apply whatever solution I can get here to my working files.

Leonardo

[leolapa]

I think the blocker here is the fact that the formula is referencing parts of the array with the OFFSET ...

I realized that when trying to come up with a solution on my end...

But the thing is that I also tried other ways that would circumvent this OFFSET limitation by using formulas that are more "spill friendly" such as INDEX and even matrix multiplication via MMULT but to no avail so far.

And with regards "the style over substance" conundrum, please take a look at my prior answer to John's point and you might understand my reason to get to a solution for this.

[AliGW]

Yes, I read that and wondered whether the VBA route might be better for you.

Maybe share one of the more spill-friendly attempts you made and see if someone here can fix it for you?

[JohnTopley]

this in D6 solves one problem

=IFERROR(IF(EDATE($A6,-12)<$A$6,"N/A ",AGGREGATE(1,6,OFFSET($A$1,ROW(INDEX($A$4:$H$41,MATCH($A6,$A$6:$A$41,0)-11,MATCH($C$4,$A$4:$H$4,0))),COLUMN($C$4)-1,12))),"N/A ")

Copy down

[JohnTopley]

As I don't have Office 365 I cannot offer any further assistance with "spill" formulae.

Re performance, I don't if there is any data regarding the performance of 365 versus older versions. From posts on this forum, there are certainly more "user-friendly" formulae (UNIQUE , LET) but regards performance ...???

[Fluff13]

One option, if you put the start date into (for instance) A3 & then put this in A6

Please Login or Register  to view this content.

you could then use this in D6

Please Login or Register  to view this content.

[leolapa]

One option, if you put the start date into (for instance) A3 & then put this in A6

Please Login or Register  to view this content.

you could then use this in D6

Please Login or Register  to view this content.

After a few minor tweaks to your solution it sure worked like a charm, thanks a bunch!

My final formulas came up like this...

Please note that since I've added a couple rows above the table to put the start date as you suggested (D4 on my case...), its header now sits on rows 6 and 7, thereby its data now starts on row 8.

Column D:
=IF(EOMONTH(DATE(YEAR($A$8#);MONTH($A$8#)-12;DAY($A$8#)-12);0)<$A$8;"N/A";IFERROR(SUBTOTAL(1;OFFSET($A$1;SEQUENCE(ROWS($A$8#);;ROW($A$8)-12);COLUMN($C$6)-1;12));"N/A "))

Column F:
=IF(EOMONTH(DATE(YEAR($A$8#);MONTH($A$8#)-23;DAY($A$8#)-23);0)<$A$8;"N/A";IFERROR(SUBTOTAL(1;OFFSET($A$1;SEQUENCE(ROWS($A$8#);;ROW($A$8)-12);COLUMN($E$6)-1;12));"N/A "))

Leonardo

[Fluff13]

Glad to help & thanks for the feedback.