Trendlines to ignore empty cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
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
 
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
 
Back
Top