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

    Projection Formula Problem (Excel 2000/2003)

    Hi

    I would like some help with a projection formula please,

    Please see attached

    Many thanks

    Braddy
    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: Projection Formula Problem (Excel 2000/2003)

    What are the numbers in L5, M5, etc.? Are they monthly amounts, or cumulative amounts?

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

    Re: Projection Formula Problem (Excel 2000/2003)

    Hi Hans

    They are monthly amounts.

    Thanks for the reply

    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: Projection Formula Problem (Excel 2000/2003)

    And how would you like the projection?

    Should we assume that monthly fiuctuations are random, or that there is a systematic trend (in the latter case, the prediction for April would be 250, for May 300 etc.)

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

    Re: Projection Formula Problem (Excel 2000/2003)

    Hi Hans

    Then monthly fiuctuations are random some months may have no sales at all , but would need to be included in the calculation.

    Many thanks

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

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

    Re: Projection Formula Problem (Excel 2000/2003)

    If there are no sales in a month, will the corresponding cell be left blank, or will a 0 be entered?

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

    Re: Projection Formula Problem (Excel 2000/2003)

    Hi Hans

    Sorry about this, the cell will be left blank,

    Many Thanks

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

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

    Re: Projection Formula Problem (Excel 2000/2003)

    Try this formula:
    <code>
    =12*SUM(L5:W5)/MATCH(9.99999999999999E+307,L5:W5)
    </code>
    SUM(L5:W5) adds all available data.
    9.99999999999999E+307 is the highest number you can enter in a cell.
    MATCH(9.99999999999999E+307,L5:W5) looks for this number in the range L5:W5 and of course cannot find it, so it returns the index of the last filled-in cell. In your sample workbook, it would return 3 because the last filled-in month is March. It doesn't matter if intermediary cells are left blank.
    SUM(L5:W5)/MATCH(9.99999999999999E+307,L5:W5) is the average amount over the months up to the last filled-in one.
    The year projection is 12 times this average.

    Note: if there are no sales for the last months of the year, you should enter a 0. If for example December is left blank, it is impossible to tell whether this is because there were no sales, or because no data have become available yet.

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

    Re: Projection Formula Problem (Excel 2000/2003)

    Hi Hans

    That works for me,

    Once again I am in your debt.

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