Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    London, Middlesex, England
    Thanked 0 Times in 0 Posts

    Predicted Vs Actual Chart? (XP)


    I need some more specialist advice from you WOPR Whizzos!

    You can see on the attached XL file

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Predicted Vs Actual Chart? (XP)

    It will your call whether you want the "chart table" to be raw data or just have it read the raw table in your form:

    Several techniques depending on what you want:
    Assume you want:
    1)Actual Yearly average Points
    2)Estimated Yearly points
    3)A "line" connecting them all

    This implies 3 ranges of "Y" and 1 of X (year)
    1)Have the actual read if no point use NA() (just a marker, no line)
    2)If has actual point for year use NA, else use predicted (marker, no line)
    3) This is a combination of all the points:
    If you want to connect the points just make it line, no marker
    If you want a trend line have excel add the trend line then use no line, marker with no colors and the points will be "invisible"

    You can also include the actual monthlies if desired, (make sure you use an XY chart). The chart the for the averages should use July 1 of the year to plot(mid year) and each of the others should use the something like the 15th of the month (mid month). You can use different XY ranges in an XY scatter.

    If you need more clarification, let us know. If you do if you could provide how you want the raw data to be, we could show how the chart data needs to be.


  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts

    Re: Predicted Vs Actual Chart? (XP)

    Hi Bob,

    A couple of observations & suggestions.

    Your picture is not consistent with you chart. Whereas the picture shows a steady workload throughout the year, the chart implies a continual increase.

    Your data has a straight trend line, so it's quite easy to fit more data points into it. What I'd suggest is starting off with Jan 2003 at 437.5 calls, which equates to 5250pa. Then simply increase the monthly calls by 3.47222222222222. By the time you get to July 2003, you'll have a monthly call rate equivalent to 5000 pa. By the time you get to July 2004, you'll have a monthly call rate equivalent to 5500 pa., and so on. Under this scenario, you 2003 total will be 5479 calls. If your need to match an actual total of 5500, use 439.236111111111 as your January value. Shift the starting point +/- 6 months if that's what your data reflects.


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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