1. 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

2. 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).

3. 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. 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.

5. 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. I had only modified the charts, not the cells with TREND formulas. See the attached version.

[attachment=86507:Trendline.xls]

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