Formula to adjust cell value until another cell equals 0

Copper Contributor

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.00Lower this cell
Utilities  $  102,195.00 
  Total  $  126,921.00 


  $      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


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




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


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




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




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)