Can you work this backwards

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

=3DIF(E9<=3D1000;E9*1.1;"=A3100") then
=3DIF(AND(E9>1000;E9 <=3D2000);(E9-1000)*0.9;"=A3200")
=3DIF(E9>2000;(E9-2000)*0.5
Total Cost ... of above 3 items plus E9 (=A33000)

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

How is the best way to do this please.

Bob

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

"Box666" wrote:

> 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
>
>