Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
Hi,
You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
OK, but then the column with the formula displays #N/A instead of (what I
had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
Hi,
You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
I also discovered that formulas on the columns containing the #N/A now don't
work. For example MAX returns #N/A. sheesh! --Bill "Andy Pope" wrote in message ... Hi, You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
Can you not just have to sets of the data. One for the charts with the #N/As
The other for presentation and the source for the MIN/MAX formula. To get around #N/A with the MAX function you would need to add an IF function and use array formula. Confirm array formula using CTRL+SHIFT+ENTER =MAX(IF(ISNA(<range),0,<range)) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... I also discovered that formulas on the columns containing the #N/A now don't work. For example MAX returns #N/A. sheesh! --Bill "Andy Pope" wrote in message ... Hi, You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message .. . Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
How do I make a =SUM formula return NA whne there are no value to SUM yet?
I have the same problem. I want to have a line chart stop in the month where there is no data yet. "Andy Pope" wrote: Can you not just have to sets of the data. One for the charts with the #N/As The other for presentation and the source for the MIN/MAX formula. To get around #N/A with the MAX function you would need to add an IF function and use array formula. Confirm array formula using CTRL+SHIFT+ENTER =MAX(IF(ISNA(<range),0,<range)) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... I also discovered that formulas on the columns containing the #N/A now don't work. For example MAX returns #N/A. sheesh! --Bill "Andy Pope" wrote in message ... Hi, You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message .. . Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
=IF(COUNT(A1:A99)=0,NA(),SUM(A1:A99))
-- David Biddulph Jim wrote: How do I make a =SUM formula return NA whne there are no value to SUM yet? I have the same problem. I want to have a line chart stop in the month where there is no data yet. "Andy Pope" wrote: Can you not just have to sets of the data. One for the charts with the #N/As The other for presentation and the source for the MIN/MAX formula. To get around #N/A with the MAX function you would need to add an IF function and use array formula. Confirm array formula using CTRL+SHIFT+ENTER =MAX(IF(ISNA(<range),0,<range)) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... I also discovered that formulas on the columns containing the #N/A now don't work. For example MAX returns #N/A. sheesh! --Bill "Andy Pope" wrote in message ... Hi, You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
linear charting with zeros
Thank you!
"David Biddulph" wrote: =IF(COUNT(A1:A99)=0,NA(),SUM(A1:A99)) -- David Biddulph Jim wrote: How do I make a =SUM formula return NA whne there are no value to SUM yet? I have the same problem. I want to have a line chart stop in the month where there is no data yet. "Andy Pope" wrote: Can you not just have to sets of the data. One for the charts with the #N/As The other for presentation and the source for the MIN/MAX formula. To get around #N/A with the MAX function you would need to add an IF function and use array formula. Confirm array formula using CTRL+SHIFT+ENTER =MAX(IF(ISNA(<range),0,<range)) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... I also discovered that formulas on the columns containing the #N/A now don't work. For example MAX returns #N/A. sheesh! --Bill "Andy Pope" wrote in message ... Hi, You could use conditional formatting to display the cells contents in the same colour as the cells fill colour. Cheers Andy Bill H. wrote: OK, but then the column with the formula displays #N/A instead of (what I had before) a space. Can the display of #N/A be set to not show in the cells? --Bill "Andy Pope" wrote in message ... Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test , <value , NA() ) Line charts will not display markers for #N/A and the line is interpolated between valid points. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill H." wrote in message ... Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The multiple regression tool - linear or non-linear regression? | Excel Discussion (Misc queries) | |||
Charting a linear equation | Charts and Charting in Excel | |||
Charting zeros as non zeros | Charts and Charting in Excel | |||
Charting a full year without zeros | Charts and Charting in Excel | |||
Charting with zeros or DIV/0 values | Charts and Charting in Excel |