|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 6th, 2009, 02:13 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
Surpressing plot of blank cells
I'm creating a line chart plot using Excel 2007. The data range for plot
lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John 
|
|
#2
October 6th, 2009, 04:02 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Surpressing plot of blank cells
Hi,
If they are truly blank then check the plot empty cells setting via. Select Data Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide unwanted rows. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "John" wrote in message ... I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John
|
|
#3
October 7th, 2009, 09:43 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Surpressing plot of blank cells
The cells I am plotting are non-empty, they contain the formula
=IF('Monthly Input'!I32"",'Monthly Input'!I32,"") that picks up none empty cells from another sheet. Checking the "Plot empty cells" option does not surpress the plotting of these cells as zero points. How do I change my formula to cause zero points to not plot? I appreciate your help, -John "Andy Pope" wrote: Hi, If they are truly blank then check the plot empty cells setting via. Select Data Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide unwanted rows. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "John" wrote in message ... I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John
|
|
#4
October 7th, 2009, 11:15 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Surpressing plot of blank cells
=IF('Monthly Input'!I32"",'Monthly Input'!I32,NA())
-- David Biddulph "John" wrote in message ... The cells I am plotting are non-empty, they contain the formula =IF('Monthly Input'!I32"",'Monthly Input'!I32,"") that picks up none empty cells from another sheet. Checking the "Plot empty cells" option does not surpress the plotting of these cells as zero points. How do I change my formula to cause zero points to not plot? I appreciate your help, -John "Andy Pope" wrote: Hi, If they are truly blank then check the plot empty cells setting via. Select Data Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide unwanted rows. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "John" wrote in message ... I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John
|
|
#5
January 25th, 2010, 05:36 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Surpressing plot of blank cells
Funny thing. I can get this to work if I'm using a straight IF()
function...but using a check on the value returned by VLOOKUP, while the cell still shows "#N/A" as a result of the source cell being blank, the chart seems to recognize it differently. (I have two spreadsheets going with the same data, one of which only needs a single value updated to slide the date forward, whereas the other requires all formulae in all columns to be updated...naturally, I want to use the VLOOKUP version) So...using if(source="",NA(),source) shows "#N/A" and plots no point. Using IF(VLOOKUP(source)=0,NA(),VLOOKUP(source)) shows "#N/A" and plots 0:00. "David Biddulph" wrote: =IF('Monthly Input'!I32"",'Monthly Input'!I32,NA()) -- David Biddulph "John" wrote in message ... The cells I am plotting are non-empty, they contain the formula =IF('Monthly Input'!I32"",'Monthly Input'!I32,"") that picks up none empty cells from another sheet. Checking the "Plot empty cells" option does not surpress the plotting of these cells as zero points. How do I change my formula to cause zero points to not plot? I appreciate your help, -John "Andy Pope" wrote: Hi, If they are truly blank then check the plot empty cells setting via. Select Data Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide unwanted rows. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "John" wrote in message ... I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
