| Home |
| Search |
| Today's Posts |
|
#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? Much appreciate your advice Keith |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set a cell to "Empty" so that it does not display in a ch | Charts and Charting in Excel | |||
| make a cell empty based on condition | Charts and Charting in Excel | |||
| To get a lot of charts that refer to different cells | Charts and Charting in Excel | |||
| Excel Charts and cells | Charts and Charting in Excel | |||
Linear Mode
