Reverse calculation (MS Excel 2000 SP3)

Topic

New Reply

The attached worksheet is fine for its intended purpose. But now I have been asked to make it work in reverse!

To explain: the result is a rental that is negotiable. In a face to face negotiation it would be useful to be able to adjust the final figure (F54) & see the effect on the ITZA (D34). At present this can only be done by entering by trial & error data in D34 to see the effect on F54 as in use cells D36 & the cells F36 to F54 that cotain formulas are protected. Even so without protection changing the numbers in F54 doesn’t affect the contents of the cells above it.

What can be done to enable the additional facility of reversing the calculation? Practical suggestions suitable for my limited knowledge of Excel would be very much appreciated.

Reply | Quote

Replies

(Edited by JohnBF on 24-Oct-04 12:49. Changed reference from F52 to F54.)

Malcolm, from a design standpoint I wouldn’t overwrite the formula in F54, rather, I’d use a separate cell for the negotiation target. Assuming you used cell I54 for that target, the formula to back into the returned value for D34 should be:

=(I54/(1+C54/100)-SUMPRODUCT(D38:D50,E38:E50))/(1+C36/100)/E36

… which you can place in a cell such as I34.

But, please run a few scenarios to see if I have understood the problem correctly.

Reply | Quote

Thanks John for your response – it is very much appreciated.

When I entered your formula in I54 Excel told me there was circular reference so I have modified your formula as follows:

=(F54/(1+C54/100)-SUMPRODUCT(D38:D50,E38:E50))/(1+C36/100)/E36

and indeed that shows& confirms the contents of D34.

However what I need to do is by some way change (play with) different values for the Adjusted Rental (in F54) & be able to see what change there is in D34 for any change in the value of F54.

To recap: At present any change in D34 is reflected in F54. I want to keep the same layout but have the facility to also see what effect a change in F54 has on D34. I hope I am not overlooking the obvious!

Reply | Quote

(Edited by JohnBF on 24-Oct-04 13:29. Corrected Attachment. Sorry, Malcolm.)

Malcolm, you may have misunderstood me. The formula should go in any Cell that it (the formula) doesn’t reference (I suggested I34), and it should reference cell to be hard coded with the total target Rental, which is also not an existing cell addressed by the formula or any of your other formulas (I suggested I54). I suggest I54 rather than overwriting D54 so that you can see it both ways (which is something I do in my pricing: I have one pair of cells calculate the return from a final rate, and I have a separate cell pair calculate the rate from a target return). See attached where I entered them in J54 and J56. HTH.

Reply | Quote

John,

It’s hard to teach an old dog new tricks! First of all I did misunderstand & secondly I didn’t read your message thoroughly.

Thank you for taking the trouble & the time for a demonstration. All is now clear & I am very grateful for your help.

Reply | Quote

John,

It’s hard to teach an old dog new tricks! First of all I did misunderstand & secondly I didn’t read your message thoroughly.

Thank you for taking the trouble & the time for a demonstration. All is now clear & I am very grateful for your help.

Reply | Quote

(Edited by JohnBF on 24-Oct-04 13:29. Corrected Attachment. Sorry, Malcolm.)

Malcolm, you may have misunderstood me. The formula should go in any Cell that it (the formula) doesn’t reference (I suggested I34), and it should reference cell to be hard coded with the total target Rental, which is also not an existing cell addressed by the formula or any of your other formulas (I suggested I54). I suggest I54 rather than overwriting D54 so that you can see it both ways (which is something I do in my pricing: I have one pair of cells calculate the return from a final rate, and I have a separate cell pair calculate the rate from a target return). See attached where I entered them in J54 and J56. HTH.

Reply | Quote

Thanks John for your response – it is very much appreciated.

When I entered your formula in I54 Excel told me there was circular reference so I have modified your formula as follows:

=(F54/(1+C54/100)-SUMPRODUCT(D38:D50,E38:E50))/(1+C36/100)/E36

and indeed that shows& confirms the contents of D34.

However what I need to do is by some way change (play with) different values for the Adjusted Rental (in F54) & be able to see what change there is in D34 for any change in the value of F54.

To recap: At present any change in D34 is reflected in F54. I want to keep the same layout but have the facility to also see what effect a change in F54 has on D34. I hope I am not overlooking the obvious!

Reply | Quote

(Edited by JohnBF on 24-Oct-04 12:49. Changed reference from F52 to F54.)

Malcolm, from a design standpoint I wouldn’t overwrite the formula in F54, rather, I’d use a separate cell for the negotiation target. Assuming you used cell I54 for that target, the formula to back into the returned value for D34 should be:

=(I54/(1+C54/100)-SUMPRODUCT(D38:D50,E38:E50))/(1+C36/100)/E36

… which you can place in a cell such as I34.

But, please run a few scenarios to see if I have understood the problem correctly.

Reply | Quote