Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill a series using the pattern of another series (2000)

    I have two data series (sales volume) starting a year apart from each other. I would like to know if there is a way to quickly finish off the later series (2002) which is a year behind the first series (2001) to using the pattern or curve of the first series. I need to graph this as well as derive the values in between. I used the edit - fill - series on the second series, but this only creates a liner line, not an arc like the first series.

    Richard

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

    Re: Fill a series using the pattern of another series (2000)

    I don't think there is a single correct answer - it depends on the pattern in your data. Do you want the curve for 2002 to be parallel to that for 2001 (i.e. vertical distance is constant), or to have the same exponential growth factor, or logarithmic growth factor, or ...?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fill a series using the pattern of another series (2000)

    Here is a good article about Time-series forecasting including links to other sites with free software and tutorials:
    https://www.plantservices.com/Web_First/PS....ID/CBOH-5MSLCL/

    Steve

  4. #4
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill a series using the pattern of another series (2000)

    The 2001 and 2002 data is the cumulative business generated from new customers since first contact respectively from each year. Both are upward sloping with occasional spikes (reasons for which include seasonal factors and lead time from contact to shipment of product). The 2001 data is of course 12 months more developed than the 2002 data and it been deemed reasonable to assume that 2001 will follow the same pattern as 2002 as 2001 develops but maintaining the current spread. In Excel, know how to build a trend from one set of data, but it has two problems. The trend is linear and the trend does not build off of data from a more developed data set. I could hack this out manually, but I have a lot of these to do and would like to find a way to highlight the two data set and have a method to return a completed data set that I can chart.

    Any help is much appreaciated,

    Richard

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

    Re: Fill a series using the pattern of another series (2000)

    Have you investigated the link Steve (sdckapr) provided? You'll find lots of links to sites about time series there.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fill a series using the pattern of another series (2000)

    If you just want a trendline you can add the data to a chart right click on the series and select add trendline, pick a type and it will plot it. You can trend it "backward" and "forward" in the options. If you don't want to see the actual data points you can remove the line and format the points to "no color"

    The references I gave show techniques to help examine if the trend has some "reality" and I recommend at least looking at some of these before you blindly trust predictions using the excel trend lines. Extrapolations are ALWAYS dangerous. No model to predict is accurate, but many can be useful. Also many are downright BAD. It is important to find a useful one!

    Steve

Posting Permissions

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