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 

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

@scubastu01 

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

 

Patrick2788_0-1672955990737.png

 

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

 

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

@scubastu01 

 

 

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

 

JoeUser_0-1672965032282.png

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)