Cannot find "Plot empty cells as" option in 2007

J

Jack Tripper

B

Bernard Liengme

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
 
S

Shane Devenshire

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
 
A

AllSensibleNamesTaken

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
 
D

David Biddulph

The best bet is to use one series for your statistical functions and a
different series as source data for your chart.
 
P

pplatt

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
 
T

Toram Dj

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

scatter chart with gaps in series (XL 2007) 7
Plot empty cells as not plotted (leave gaps) 2
"treat empty cells as" is greyed out in charts 1
If I do not plot empty cells, how do I change the category axis? 0
Worksheet functions plot and #NA's 3
Generating blank cells in Excel 2007 1
Cells that "appear" empty plot as zero. Do not want cells to plot. 1
Enter NA() into All Blank Cells in Range 16

Top