Iterative calculation for circular formula

shawnb12

Is there anyway to do iterative calculations in Smartsheet like Excel has for circular formulas?

Trying do to do a budget table like this

Which is made up of a circular formula that only works with iterative calc turn on in Excel.

Paul Newcome

I would try this instead:

=SUM(E4:E5) + (SUM(E4:E5) * D6)

Ramzi

Unfortunately, there is not.

shawnb12

That doesn't work, as the E6 value is not just a % of E4 & E5, but also of itself as a % of the sum of all 3, which is a circular reference. It works ok if doing iterative calculation in excel, but can't get to work in smartsheet.

Paul Newcome

I just ran it on a calculator, and it actually looks like we need to run it 4 times to get to the Excel output.

So...

7,000,000 * .03 = 210,000

210, 000 + 7,000,000 = 7,210,000

7,210,000 * .03 = 216,300

216,300 + 7,000,000 = 7,216,300

7,216,300 * .03 = 216,489

216,489 + 7,000,000 = 7,216,489

7,216,489 * .03 = 216,494.67 (round to 216,495)

216,495 + 7,000,000 = 7,216,495 (Excel output)

That would look like this:

=((((((((E4+E5) * 0.3) + (E4+E5)) * .03) + (E4+E5)) * 0.03) + (E4+E5)) * .03) + (E4+E5)