|
chart data
I have a spreadsheet set up that updates the information every month over a
12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
Hi,
You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
If i use N/A it still shows N/A along the bottom axis and goes to zero. I
don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
The formula would be
=IF( <test , <valid data , NA() ) You could also use #N/A. Anything else will be treated as text and text is plotted as zero. If you do not want the months included on the axis then you will need to create some named ranges and based the chart data upon those. You find information on dynamic named ranges here, http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... If i use N/A it still shows N/A along the bottom axis and goes to zero. I don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
Do i put that formula in the chart data source?
Thanks for your help on this one. "Andy Pope" wrote: The formula would be =IF( <test , <valid data , NA() ) You could also use #N/A. Anything else will be treated as text and text is plotted as zero. If you do not want the months included on the axis then you will need to create some named ranges and based the chart data upon those. You find information on dynamic named ranges here, http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... If i use N/A it still shows N/A along the bottom axis and goes to zero. I don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
If it shows N/A on the X axis, it sounds as if you are using the confusingly
named line chart instead of an XY chart. -- David Biddulph "Emma Chapman 123" wrote in message ... If i use N/A it still shows N/A along the bottom axis and goes to zero. I don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
Hi All,
I think we are working across purposes with the OP. The formulas everyone is talking about, Emma, look like this =IF(R1=0,NA(),R1) The NA is a function not text. Second, if you have functions in the X-axis columns, don't use this, they will display as #N/A on the X-axis. I suspect you don't want to display the months unless you have data other than 0, is that correct? If so you may want to consider Dynamic Range Names. Post back if this is what you want. -- Thanks, Shane Devenshire "David Biddulph" wrote: If it shows N/A on the X axis, it sounds as if you are using the confusingly named line chart instead of an XY chart. -- David Biddulph "Emma Chapman 123" wrote in message ... If i use N/A it still shows N/A along the bottom axis and goes to zero. I don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
chart data
No formula in the data source.
The formula is in the cells where you currently display chart data or zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... Do i put that formula in the chart data source? Thanks for your help on this one. "Andy Pope" wrote: The formula would be =IF( <test , <valid data , NA() ) You could also use #N/A. Anything else will be treated as text and text is plotted as zero. If you do not want the months included on the axis then you will need to create some named ranges and based the chart data upon those. You find information on dynamic named ranges here, http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... If i use N/A it still shows N/A along the bottom axis and goes to zero. I don't want to include these cells if null value on the chart data. "Andy Pope" wrote: Hi, You need to modify your formula to use NA() instead of zero. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Emma Chapman 123" wrote in message ... I have a spreadsheet set up that updates the information every month over a 12 month period. The data i have for example is Actual Profile Jan 77723 77723 Feb 75319 67582 Mar 72047 73469 Apr 69557 81048 May 76672 88563 Jun 65068 77750 Jul 63819 79528 Aug 71079 90357 Sep 65793 85348 0 0 0 0 0 0 0 0 0 Obviously i have not received the data for the last three months yet but i need to keep the formulas in to automatically update the information. I want to select all of the information in this area but not show zeros on my chart. This chart will then update every month as the data updates. Is this possible to do. Please can someone help me. Thanks |
| All times are GMT +1. The time now is 04:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com