How can I build self-referential ARRAYFORMULAs that work quickly in Google Sheets?

Question

Here is an example sheet which you should be able to make a copy of.

The sheet also contains further detail on the problem.

However...

The gist of the problem is that we have 3 columns (B:D) which we ideally want to set up as ARRAYFORMULAS, to keep dynamically expanding them as new data is added. (In this case, each column has 1 piece of data for a date kept in A2:A)

The only "static" input into these columns is a number initially placed into B2.

Cells C2 and D2 then need to be generated based on the number in B2. There is some relatively sophisticated logic in our actual sheet that does the generation, but I'm reasonably sure the only "important" dependence for solving this problem is the dependence on B2.

Cell B3 then needs to be generated based on the numbers in B2, C2, and D2. This logic really is just B2+C2+D2, which is nice.

The problem we are encountering is:

How do we set up a self-referential ARRAYFORMULA? (e.g. if the ARRAYFORMULA starts in B3... then whatever it tries to write in B5 will depends on what it wrote in B3, B4, and B5.) I can find some attempts of other people to solve this problem, but I don't really understand how to modify them for my use-case — can you?
How do we cut down the amount of circular reference as much as possible to keep the sheet speedy? (right now we NEED iterative calculation turned on, with a lot of iterations, to work through the self-reference)

We could avoid these issues, obviously, if we just had formulas in each column (e.g. B3=B2+C2+D2, B4=B3+C3+D3...) but:

a) trying this still seems to make the sheet very sluggish with the amount of calculation we're using (especially as columns C and D have relatively "chunky" logic in our actual sheet)
b) we'd really rather a dynamic table here as it'll make some other aspects of our sheet a lot easier

Thank you for your help in advance!

Here is one attempt just now at writing a self-referential ARRAYFORMULA in B3 (using the link above), but it's giving weird results — e.g. the rank drops again even with no Decay / Match change negative values, which makes me think it's not correctly cumulatively tracking changes. But it might help to see my thinking:

=ARRAY_CONSTRAIN(

ARRAYFORMULA( ARRAYFORMULA(INDIRECT("B2"&":b"&COUNTA(B2:B))) + C2:C + D2:D),

DaysInRange,1)

score 0 · Answer 1 · edited Jan 23 '21 at 12:33

First, I deleted any unused rows and columns, and that sped things up a lot.

Then I changed the array formulas. I don't think the ARRAY-CONSTRAIN function is required, if the formula ranges are specified correctly.

So try this. Insert a blank column to the left of column B. Set the new B2 = StartRank. Then in cell B3:

=ARRAYFORMULA( IF(LEN(INDIRECT("A3:A"&COUNT(A2:A)+1)),
                      INDIRECT("B2:B"&COUNT(A3:A)+1) + 
                      INDIRECT("D2:D"&COUNT(A3:A)+1) + 
                      INDIRECT("E2:E"&COUNT(A3:A)+1),""))

And in D2, to calculate the Decay Change:

=ARRAYFORMULA(
  IFS(A2:A = "", "",
      C2:C > GKSheet1!RankCap,  round( -1*GKSheet1!DecayPercent*ABS(GKSheet1!RankCap-C2:C)),
      C2:C < GKSheet1!RankFloor,round(    GKSheet1!DecayPercent*ABS(GKSheet1!RankFloor-C2:C)),
      TRUE,0))

And in E2, to calculate the Match Decay:

=ARRAYFORMULA(
   IFS(A2:A = "", "",
       B2:B > RankCap,   F2:F * -10,
       B2:B < RankFloor, F2:F *  10,
       TRUE,             F2:F *  60))

I also changed the date formula for column A. The only negative I think is that it doesn't automatically add rows. So if you increase your date range, ie. move your starting date to an earlier date, column A will fill, but may not extend to "today" if you don't add more rows.

I've added a tab, GKSheet1, to your sample sheet, but I don't think I changed anything else. It still is slow, but that may be because of the other tabs in your sheet. Here is my sample, with only my one tab in it. It takes about ten seconds to recalculate, with a starting date of about one year before today - eg. if we're in January, 2021, used a start date of Jan 1, 2020.

How can I build self-referential ARRAYFORMULAs that work quickly in Google Sheets?

1 Answers1