Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Generate Gross Sales Value (Excel 2002)

    Hi

    I have been asked the question below, but have now idea where to start or even if the request is feasable. Any Ideas please.

    I need to juggle the Gross Sales Value (G12) up or down and referring after each change to check what EBITDA Value (Q60) is obtained. Repeat changes to G12 until desired EBITDA in Q60 is achieved.
    What I would like to be able to do is put in the desired EBITDA value (cell P6) and this would then automatically generate the correct Gross Sales Value (G12)
    and show the same
    If you are a fool at forty, you will always be a fool

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

    Re: Generate Gross Sales Value (Excel 2002)

    Interactively, you can use Tools | GoalSeek for this. In the attached workbook, I have placed a command button from the Forms toolbar that runs a macro to do this:

    Sub SetEBITDA()
    Dim dblMaxChange As Double
    dblMaxChange = Application.MaxChange
    Application.MaxChange = 0.0001
    Range("Q60").GoalSeek Goal:=Range("P6"), ChangingCell:=Range("G12")
    Application.MaxChange = dblMaxChange
    End Sub

    I set the Maximum Change option for the worksheet to 0.0001 temporarily to get a more accurate result than with the default value of 0.001, then run GoalSeek.

    Note: when you change P6, you must confirm the value before clicking the command button.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Generate Gross Sales Value (Excel 2002)

    Hi Hans

    You are not only a genius for understanding the question, but to answer it aswell is quite a bonus.

    Just one question if you don't mind, What does MaxChange mean?

    Grateful Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Generate Gross Sales Value (Excel 2002)

    It is an option that you normally set in the Calculation tab of Tools | Options...
    When you run the goal seeker, Excel repeatedly tries values, using a method that usually quickly approaches the desired value. It stops when it has performed the number of steps specified in Maximum Iterations (default: 100), or when the values change less than the value specified in Maximum Change (default: 0.001), whichever is reached first. For your calculations, it's best to be more precise than the default. If you're curious, temporarily comment out the lines in the macro that set MaxChange, and see what happens.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Generate Gross Sales Value (Excel 2002)

    Hi Hans

    Thank you for your explanation.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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