New Contributor

# Formula to adjust cell value until another cell equals 0

I have a budget spreadsheet that has a lot of math based on multiplying or dividing by percentages.  I need a formula that will generate a value up or down until another cell is equal to 0.

For Example:  Formula to adjust the "Site Improvements" budget until the "Delta" is Zero.

 Earthwork \$       7,070.00 Site Improvements \$     17,656.00 Lower this cell Utilities \$  102,195.00 Total \$  126,921.00 5% \$      6,346.05 3% \$      3,807.63 1.50% \$      2,056.12 Total \$  139,130.80 Total Budget \$  138,000.00 Delta \$    (1,130.80) Delta to 0
3 Replies

# Re: Formula to adjust cell value until another cell equals 0

I believe you could do this with Solver.  Set it like this and then click Solve.

# Re: Formula to adjust cell value until another cell equals 0

Ah... "Goal Seek" did the trick.

Thanks Patrick... that got me where I need to go.

# Re: Formula to adjust cell value until another cell equals 0

If you would like to avoid using Goal Seek, a manual step, consider the following.

Formulas:

C3: =ROUND(C11/(1+A6+A7)/(1+A9) - C2 - C4, 2)

C5: =ROUND(SUM(C2:C4), 2)

C6: =ROUND(C5*\$A6, 2)

C7: =ROUND(C5*\$A7, 2)

C8: =ROUND(SUM(C5:C7), 2)

C9: =ROUND(\$A9*SUM(C5:C7), 2)

C10: =ROUND(SUM(C8:C9), 2)

Caveat:  Whenever stepwise calculations are rounded, a real-world requirement, expect some "penny-off" differences.  (Not this time only by coincidence.)

Proof of concept (not necessary)....  I copied C5:C10 into D6:D10 to demonstrate the correctness of the formulas, which I had to derive from your example, since you neglected to include them. (sigh)