Forum Discussion
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 |
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)
Ah... "Goal Seek" did the trick.
Thanks Patrick... that got me where I need to go.
