# Iterative calculation for circular formula

Options
✭✭✭

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Unfortunately, there is not.

Smartsheet Solutions Architect

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!