|
|
|
|
Hi Keith,
I have adapted John's example so you can use any of the trend lines types.
http://www.andypope.info/ngs/ng31.htm
Cheers
Andy
Hoochi Coochi Man wrote:
Hi John
I cant find a ready made function but will continue looking. Your linear
solution is useful anyway so thanks for that.
Cheers
Keith
"John Mansfield" wrote:
Keith,
You might want to check in the math functions and/or Analysis Toolpak to see
if Excel offers a built-in function that would build the polynomial trend
that you want. That function could then be substituted for the TREND
function in the example. If a built-in function is not available, then the
formulas would need to be modified to build the equation and then plot the
points each time the data was updated. That will probably be pretty hard to
do without the use of VBA.
----
Regards,
John Mansfield
http://www.pdbook.com
"Hoochi Coochi Man" wrote:
Hi John
Many thanks for this. I have tried it and it works well in principle. Only
problem is that the data is not very linear and a polynomial trendline
produces a better fit. Is there anyway your method can be adapted for a
polynomial say to 3 or 4?
Thanks again for your help.
Cheers
keith
"John Mansfield" wrote:
This example will allow you to have a dynamic linear trend line that
recalculates and moves with the data line. The X Axis values will remain
static. If the data covers only five points, the linear trend will return
the trend line of five points. If the data covers eight points, the linear
trend will return the trend line for eight points.
Open a workbook and call it Tst2.xls. Call the sheet where the embedded
chart is to be placed €śTest€ť.
Assume the letter €śa€ť starts in cell A2. Your actual data points begin in
cell B2. Columns C and D are calculated based on the inputs in column B. If
in column B a cell contains no data, enter €ś=NA()€ť. The data is initially
set up as follows:
a 50 1 41.20
b 29 2 46.70
c 67 3 52.20
d 46 4 57.70
e 69 5 63.20
f #N/A #N/A #N/A
g #N/A #N/A #N/A
h #N/A #N/A #N/A
i #N/A #N/A #N/A
The formulas look like this (the spaces between a spaces between columns A,
B, C, and D):
a 50 1
=IF(ISERROR(B2),NA(),TREND(RR2,RR1,C2))
b 29 =IF(ISERROR(B3),NA(),C2+1) =IF(ISERROR(B3),NA(),TREND(RR2,RR1,C3))
c 67 =IF(ISERROR(B4),NA(),C3+1) =IF(ISERROR(B4),NA(),TREND(RR2,RR1,C4))
d 46 =IF(ISERROR(B5),NA(),C4+1) =IF(ISERROR(B5),NA(),TREND(RR2,RR1,C5))
e 69 =IF(ISERROR(B6),NA(),C5+1) =IF(ISERROR(B6),NA(),TREND(RR2,RR1,C6))
f =NA() =IF(ISERROR(B7),NA(),C6+1) =IF(ISERROR(B7),NA(),TREND(RR2,RR1,C7))
g =NA() =IF(ISERROR(B8),NA(),C7+1) =IF(ISERROR(B8),NA(),TREND(RR2,RR1,C8))
h =NA() =IF(ISERROR(B9),NA(),C8+1) =IF(ISERROR(B9),NA(),TREND(RR2,RR1,C9))
i =NA() =IF(ISERROR(B10),NA(),C9+1) =IF(ISERROR(B10),NA(),TREND RR2,RR1,C10))
Four dynamic named ranges are set up for the chart and for the formulas
above . . .
Go to Insert - Name - Define and create a name called €śRR1€ť. Add this
formula in the €śrefers to€ť area:
=OFFSET(Test!$C$2,0,0,COUNT(Test!$C$2:$C$10),1 )
Go to Insert - Name - Define and create a name called €śRR2€ť. Add this
formula in the €śrefers to€ť area:
=OFFSET(Test!$B$2,0,0,COUNT(Test!$B$2:$B$10),1 )
Go to Insert - Name - Define and create a name called €śTrendNbrs€ť. Add
this formula in the €śrefers to€ť area:
=OFFSET(Test!$D$2,0,0,COUNTA(Test!$D:$D),1)
Go to Insert - Name - Define and create a name called €śValues€ť. Add this
formula in the €śrefers to€ť area:
=OFFSET(Test!$B$2,0,0,COUNTA(Test!$B:$B),1)
Build a simple line chart.
For Series 1, enter the following formula:
=Tst2.xls!Values
For Series 2, enter the following formula:
=Tst2.xls!TrendNbrs
For the Category (X) Axis Labels, enter the following formula:
=Test!$A$2:$A$10
The chart should now contain two lines. The first line is the original
data. The second line is a linear trend line. Both lines will update as
values are entered or deleted from column B.
Since the formulas are hard to make out, I'll post the example on my website
tomorrow morning.
----
Regards.
John Mansfield
http://www.pdbook.com
"Hoochi Coochi Man" wrote:
Hi
I have five years of data and the x axis is set for 6 years to allow the
chart to fill up as new data is input. When I add a trendline it seems to
include these empty cells. In fact, the cells contain a formula but have
managed to get the data plot to not be zero by using the NA() function which
I found in another thread. Any ideas how I can get the trendline to only use
the actual data for its calc rather than plotting along the whole of the x
axis?
Much appreciate your advice
Keith
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
|