Cannot find "Plot empty cells as" option in 2007

  • Thread starter Thread starter Jack Tripper
  • Start date Start date
J

Jack Tripper

Click on the chart
In the Chart Tools tabs open Design;
In the Data group, open Select Data
Bottom left corner of dialog open: Hidden and Empty Cells
Make your selection

I would be interested in why you refer to the use of NA() as a 'hack'
My experience suggests this to be the simper way
best wishes
 
Hi,

Regarding the =NA() comment: The use of this is mainly when one has a
formula that return a blank to one or more of the cells being plotted. You
generally don't just type =NA() in a cell in the data range, what you do is
modify a formula that might return "" so that it return NA when it would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because it is
not even applicable. Meaning if the formula returns "" it is not consider
empty by Excel. The empty cell are cells that really have nothing in them.

Cheers,
Shane Devenshire
 
Hi guys,

I'm finding that I have to use IF(A1="","",A1) in columns where I need the
following formulas to operate while ignoring "blanks":
average() ,stdev(),skew(),and kurt()
I canot use NA() here because the above formulas fail upon encountering any
#N/As

I of course then find the same problem as you guys that these "" are
interpreted as zeros by Excel charts rather than blanks to be ignored.

Is there something other than "" or NA() that I could use in my
IF(A1="","",A1)
formulas so that both charts and the abobe statistical functions can work?

Thanks so much
 
I have a followup question related to this thread - I created a second data
series, as David suggested, so Series 1 contains empty cells for missing data
points and Series 2 contains NA() (which displays as #N/A) for missing data
points. When I graph Series 1 with Tools->Options->Chart->Plot Empty Cells
set to "Not Plotted (leave gaps)" it leaves gaps in my line chart for the
empty cells as expected. However, when I copy the chart and point it at data
Series 2 (same data, just contains #N/A in place of empty cells) the graph
does not display gaps for data points with a value of #N/A. If data points 2
and 3 are missing, the graph connects point 1 to 4 as if it is interpolating
rather than "Not Plotted" which is the current setting. Do #N/A values in a
data series somehow override the Tools->Options->Chart settings? Is there a
value I can input into a calculated cell that plots on a graph exactly like
an empty cell would?

I am running Excel 2003 SP3. Thanks for any insights.

Phil Platt
 
I have been able to plot the blanks in your spreadsheet as gaps or connected lines (but not zeroes), through the "Hidden and Empty Cell Settings" box. However, this only happens when I select log scale on the vertical axis, and only happens on one of my computers, but not the other. Both are running Windows 7 Home Premium with Microsoft Office Enterprise 2007 installed.
 
Back
Top