Can you work this backwards

[Box666]

Previously I used 3 simple formulas (if statements) to calculate a
charge, so if E9 =£3000 then the formula for the charge was

=IF(E9<=1000;E9*1.1;"£100") then
=IF(AND(E9>1000;E9 <=2000);(E9-1000)*0.9;"£200")
=IF(E9>2000;(E9-2000)*0.5
Total Cost ... of above 3 items plus E9 (£3000)

Now people are asking to spend a total of £3000 including the charge,
so now Total Cost is to be £3000 and then I have to work back to find
E9.

How is the best way to do this please.

Bob

 

[Guest]

Hi,

Use the "Solver" utility in Excel.

Let us suppose that the total cost [which is the sum of E9 and the charges
calculated with your formula(s)] is in the cell J9.
Invoke "Solver" from the "Tools" menu in the Toolbar,
"Set Target Cell" J9;
"Equal To" Check "Value of:" and enter 3000;
"By Changing Cells" E9
Click "Solve"

Note that the 'Solver' add-in is not installed in a standard installation of
Excel. If that is the case, install it as follows:
"Tools" --> "Add-ins" and check the "Solver Add-in" button........

Regards,
B. R. Ramachandran