Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2007
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forecasting (Win XP SP 2, Office 03)

    Hi guys. This info is probably already up here somewhere,but I am going to ask because I don't know the correct term for it.

    I have a client user with a spreadsheet into which she has actual figures for budget allocations over four years. She wants to use these figures to calculate a trend/forecast of what the 07/08 figure should be. File is attached.

    She wants to base the forecast on the figures in the range C6:F6, and place the result in H6 and also into I:6.

    Once she has this working, she then wants to use the same calc on C15:F15 (results in H15 and I15), then do the same again for C:27 :F27.

    As you can see, she has used the "Trend" function, and I am not sure if this is the right one. If you look at the figures she has in H6, for example, it is lower than that for all the previous years data.

    Can anyone please help?
    Attached Files Attached Files

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

    Re: Forecasting (Win XP SP 2, Office 03)

    Welcome to Woody's Lounge!

    It's OK to use the TREND function, but you need to specify an extra argument. The formula in H6
    <code>
    =TREND(B6:F6)
    </code>
    returns the trend value for the 1st of the given data points, i.e. for 2003-2004. That is not what she intended. The range B6:F6 contains 5 data points, the next one is #6. Therefore, the formula needed to forecast the 6th value is
    <code>
    =TREND(B6:F6,,6)
    </code>
    Similarly, the formula in I6 should be
    <code>
    =TREND(B7:F7,,6)
    </code>
    The formulas in H15, I15, H27 and I27 need to be adjusted the same way.

  3. #3
    New Lounger
    Join Date
    Nov 2007
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forecasting (Win XP SP 2, Office 03)

    Oh Hans, you are wonderful.

    I will now go back to my user (and make sure she knows and appreciates your help!!!)

    Will let you know how it goes.

    Thanks so much!

Posting Permissions

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