Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attached workbook has a data table which is plotted on a chart. The trendlines on the first chart is a line picture overlayed the chart. I was playing with trendline on a scatter chart and am wondering if it is possible to have a combined chart wherein the a trendline is created over actual values of the first chart and then extrapolated out for the whole time period. Any thoughts are always appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached version - I used the TREND function to create an extra data series that represents the linear forecast for the Actual Revenue series, and added this to the chart (by dragging/dropping the series onto the chart).
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. That worked well. How would the TREND formula array be changed so that it would update when a new actual value was added to the data table?

    Amy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the attached version, I have created dynamic named ranges (in Insert | Name | Define...), then used these to define the chart series in both charts. The list of months in row 4 governs them - if you add a new month in row 4, the series will automatically expand.
    Select a chart, select Chart | Source Data... and activate the Series tab to see how the named ranges are used to define the series.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    Thank you. This is a helpful way of doing this; however, it doesn't appear to be changing the TREND formula array in Row 8 and the named range doesn't seem to make that accommodation either.

    If the formula array is consistently =TREND($D$5:$F$5,,COLUMN()-3) in the range D8:P8, then the slope of the trend line does not change as new 'Actual Revenue' values are input in Row 5 each month.

    For example, once the Jul 'Actual Revenue' value is input into the table, then the TREND formula array in Row 8 range D8:P8 would be =TREND($D$5$5,,COLUMN()-3)

    As 'Actual Revenue' is input each month, the TREND formula array would change to accommodate each subseqent month of 'Actual Revenue' values.

    Once August values are input, the TREND formula array would change to =TREND($D$5:E$5,,COLUMN()-3)

    Sep values would prompt a change to =TREND($D$5:F$5,,COLUMN()-3)
    Oct values would prompt a change to =TREND($D$5:G$5,,COLUMN()-3)

    So how would the second range variable for the TREND formula change with new inputs. I see that =TREND($D$5$C$5+COUNTIF(D5:P5,">0")),,COLUMN()-3) doesn't work

    I hope that spells out the problem.

    Thanks
    Amy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I had only modified the charts, not the cells with TREND formulas. See the attached version.

    [attachment=86507:Trendline.xls]
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    Good play. That was very instructive. In my situation (not the workbook that we have been using), the table was filled with cell references that had values equal to formula arrays from other worksheets. As such, the COUNTA reference within the ActualData named range counted all cells within the range. I changed the COUNTA to a COUNTIF statement ">0" and that worked just fine. Thank you for the lesson on named ranges.

    Amy

Posting Permissions

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