+ Reply to Thread
Results 1 to 9 of 9

Trendlines to ignore empty cells

  1. #1
    Hoochi Coochi Man
    Guest

    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?
    Much appreciate your advice
    Keith

  2. #2
    Registered User
    Join Date
    01-11-2005
    Posts
    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. #3
    John Mansfield
    Guest

    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?
    > Much appreciate your advice
    > Keith


  4. #4
    Hoochi Coochi Man
    Guest

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


  5. #5
    John Mansfield
    Guest

    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?
    > > > Much appreciate your advice
    > > > Keith


  6. #6
    Hoochi Coochi Man
    Guest

    RE: Trendlines to ignore empty cells

    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


  7. #7
    Andy Pope
    Guest

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

  8. #8
    Hoochi Coochi Man
    Guest

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


  9. #9
    Jon Peltier
    Guest

    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
    already!

    - 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?
    >>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 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?
    >>>>Much appreciate your advice
    >>>>Keith



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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