# Trendlines to ignore empty cells

1. ## Trendlines to ignore empty cells

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?
Keith

2. You can use a trick called "dynamic charting" (a google search might give a better description) to only graph data from a column which is not empty. You will use the COUNT() function and the Offset() function. COUNT() will count the number of cells which contain values and OFFSET() uses a start point and generates a range given a height and width from that starting point. Create a named range which used the OFFSET() function where the range height is the COUNT() function. Whenever new data is types into the column, the count will increase by one and the range will increase accordingly. The graph will automagically update when the data is added.

I hope this is what you're looking for.

3. ## RE: Trendlines to ignore empty cells

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?
> Keith

4. ## RE: Trendlines to ignore empty cells

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?
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?
> > Keith

5. ## RE: Trendlines to ignore empty cells

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?
> > > Keith

6. ## RE: Trendlines to ignore empty cells

Hi John
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?
> > > > Keith

7. ## Re: Trendlines to ignore empty cells

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
> 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?
>>>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?
>>>>>Keith

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

8. ## Re: Trendlines to ignore empty cells

Andy, That is brilliant!
Many thanks. I discovered that if I delete series 1 I get the welcome result
of the graph extending dynamically as y data is added.
This really is excellent. Thanks again
Keith

"Andy Pope" wrote:

> 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
> > 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?
> >>>>>Keith

>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

9. ## Re: Trendlines to ignore empty cells

You could use LINEST to get coefficients (described in Bernard Liengme's web site,
http://www.stfx.ca/people/bliengme/E...Polynomial.htm), then use the
coefficients to construct a trendline manually. But I see Andy's come to the rescue

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

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?
>>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 CandDarecalculatedbasedontheinputsincolumnB.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?
>>>>Keith

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1