CODEWITHSUNDEEP
excelexcel-formula
Ascendant
Ascendant

Reputation: 2579

Collapse circular reference when each cell depends on every other cell?

Context: I'm mapping some excel sheets into web backend code.

Circular reference works well and super fast within excel, we currently do 1000 iterations in excel for each place of circular reference, and each recalc is practically instant. But when converted to backend code, it's not as fast, so I'm trying to collapse the circularity into formulae.

I was able to collapse some of the circular references, but here's a tricky one. It essentially boils down to this:

subtotal1 = parameter1 * total
subtotal2 = parameter2 * total
subtotal3 = parameter3 * total
total = subtotal1 + subtotal2 + subtotal3

Each subtotal depends on the total and vice versa.

If you do algebraic transformations you'll realize you can never extract the formula for any one argument, because there are over 2 layers of interconnectedness that cannot be unfurled.

Ideally I'd like to it down to a formula like this: sub1 = <a formula that calculates sub1 directly and does not include sub2, sub3 or total>

How can we collapse this kind of circular references into formulae and avoid doing 1000 iterations in the code?

Upvotes: 3

Views: 55

Answers (1)

P.b
P.b

Reputation: 11415

If your values are in A1:C1 you could use:

=SUM(REDUCE(A1:C1,SEQUENCE(8),LAMBDA(x,y,SUM(x)*x)))

The number mentioned in the sequence is the number of iterations, but this will quickly result in a number too large for Excel.

enter image description here

I used 1, 2, 3 for this and above SEQUENCE(8) results in #NUM! (at least using the mobile app version of Excel).

What this formula does is start with the values in A1:C1, sums these values and multiplies it with it's individual values, creating an array of 3 numbers (subtotal1-3). The the last calculated value (x) is the new start point for the same calculation sum of x * x. This repeats untill the sequence ends.

To make visible what it does you can use: =REDUCE(A1:C1,SEQUENCE(8),LAMBDA(x,y,VSTACK(x,SUM(TAKE(x,-1))*TAKE(x,-1))))

Which will spill the arrays (starting at the start value, then the iterations, without showing the summed array value). enter image description here First mentioned formula does the same without stacking and it's wrapped in sum to get the total.

Upvotes: 2

Related Questions