Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Back calculation (MS Excel 2000 SP3)

    The attachment is an example of part of a worksheet I created six years ago to assist a son in his business. With it he can calculate a rental for retail premises ringing the changes and as well as detailed calculation working backwards through the target cells. The example is entirely a figment of my mind and the rates are not representative. In the working situation the fill color cells would be protected as they contain formulas and the reconciliation in cell E29 would not be present.

    I am presently being asked to provide a facility such that the rental in cell E21 can be changed and the rate in cell D5 automatically recalculated. I am unable to do this as evidently cell E21 is protected and even if it were not protected and it were possible to type in another figure the formula it contains would be lost destroying the functionality of the worksheet. I could provide a second column to replace the target cells but this is not wanted. I am told that professional competitors have such a facility but also there are good reasons for having it apart from keeping up with the opposition!

    Is this possible with programming not that I want to write one as it is beyond my capabilities. (And I am not asking for anyone to do it for me! If needs be son will have to employ a professional to do this for him.) All I want is to know if/how it could be done.

    To sum up the calculation needs to treat the contents of cells D5 and E21 as variable by calculation and back calculation in relation to each other and the figures for the other floors treated as fixed even though they too could be changed manually if required. I sincerely hope I am making sense as I can't think of any better to come for good advice. {8:-))
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back calculation (MS Excel 2000 SP3)

    Excel has a built-in tool for this: Tools | Goal Seek:
    - 'Set cell' should be set to E21.
    - 'To value' should be set to the desired value for E21.
    - 'By changing cell' should be set to D5.
    Click OK to start the goal seek.
    If the 'To value' is reasonable, Excel will calculate the new value for D5.
    You can accept the new value by clicking OK, or restore the original situation by clicking Cancel.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Back calculation (MS Excel 2000 SP3)

    Hans,

    Thank you very much for your reply and its contents. I am trying to send you a large gin but I can't get it into my router. My faith in you and this forum is confirmed yet again.

    Malcolm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •