Iterative calculation for circular formula
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.
Answers

I would try this instead:
=SUM(E4:E5) + (SUM(E4:E5) * D6)

Unfortunately, there is not.
Smartsheet Solutions Architect
www.adapture.com

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.

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)
Help Article Resources
Categories
Check out the Formula Handbook template!