Blank Values in Excel Charts

[Ken]

I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.

 

[Mike H]

Ken.

Make you blanks which I assume are formula generated evaluate as NA()
instead of blank and Excel won't chart them it will interpolate between
adjacent values in the chart.

Mike

 

[Andy Pope]

Hi,

All non numeric values in cells will be treated as text and therefore
charted as zero.
The exception is the use of the formula NA() which will suppress the
plotting of a data marker. In a line chart the line will be joined
between 2 valid data points.
The only way to get a line break is to have truly empty cells.

Cheers
Andy

 

[ShaneDevenshire]

Hi,

Replace formulas like
=IF(A3<10," ",A3)
with
=IF(A3<10,NA(),A3)

A couple of points:
1. It is probably better technique to use "" to represent a blank cell
rather than " ". In many situations " " would not be treated as a blank
cell, but "" would.
2. In a column or bar chart this technique works very well, however, in a
line chart if your goal was to leave a break in the line, this would not do
it, because as Mike has said Excel interpolates (connects the known points as
thought the missing point was on the line connecting them).
3. You should look at Jon Peltier's website for other useful charting
suggestions:
http://peltiertech.com/Excel/Charts/

 

[Loren]

This idea works good, however, if you are doing any calculations in your
spreadsheet such as finding the average, you get an error. Is there anyway
to have the best of both worlds.

 

[StuartB]

Thanx for that - but only close for me -- no cigar
I have some 7000+ rows of data that i'm percolating down for a series of
different stats charts. For this particular chart i have just two columns -
a UserName and a 'Count of Conflicts' - so in all cases i have data in both
columns (a Name and a Count) or 'blank' -- #NA (or null or whatever works) in
both columns. With this particular formula i have a results set of about 100
rows of data i want to chart using a Bar Chart - Names on vertical axis and
Count across the bottom. No matter if i use #NA or null in one or the other
or both 'blank' cells my chart always includes the 'blank' rows. Any clues
how i can just chart the non-blank data? I'm using Excel 2003 on XP.
--------------------- Cheers ----------------------------

 

[Fred Smith]

The first thing you have to do is see for yourself what will get Excel to
ignore data points.

It's not blanks, or nulls, or text like "#NA". It's the result #N/A which is
obtained by the NA() function.

Go to your chart data, and use =NA() in one of the cells you don't want
charted. You should see the results you want in the chart.

Once you understand what the NA() function does for you, you can then modify
your formulas to get those results.

Regards,
Fred

 

[StuartB]

Hi Fred. Thanx for your reply.
I am indeed already using the NA() function in my formulae to try to achieve
what you say - but to no avail. Hence I've tried "" and 0 also to no avail.
Tools/Options/Chart is set to not chart empty cells and to plot only visible
cells. I'm tearing out what little hair i have left
Here's the top few rows i'm charting using the standard simple Bar Chart -
and i get 8 bars on my chart not the one i'd expect. The #N/A you see is
obtained via the NA() function as shown below.

User ConflictsCount
#N/A #N/A
#N/A #N/A
ABBOTTMF 2
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

Here's the formula in each column -
User [Column Z]: =IF(Y2="",NA(),X2)
ConflictsCount [Column AA]: =IF(ISNA(Z2),NA(),Y2)

Really hope you can help further.
------------ Cheers -----------------