Excel 2013 - I have no explanation for this chart behavior. Do you?
December 15, 2015 11:52 AM   Subscribe

Excel 2013 - I have no explanation for this chart behavior. Do you?

Here's a link to the doc :

https://dl.dropboxusercontent.com/u/25232878/upload%20for%20research.xlsx

Here's a screenshot :

http://i.imgur.com/06YrlCs.png

So we're calculating and charting a couple of pieces of data. I have dummy data entered for three months currently.

For three data sets (black line, grey line, blue bars), i'm using a formula that says, basically, "check a cell. if it's zero, show NA(). if it's not a zero, show the value".
For the black line series and the grey line series, excel is ignoring the #N/As as it should - this is what I WANT - it's not displaying a zero on the chart, it's not displaying the #N/A error... it's just blank for any month that doesn't contain data. THIS IS MY GOAL.

the blue bars, however, are actually SHOWING the #N/A code.

the black series and the blue series use the EXACT SAME FORMULA (aside from cell references).

I can only assume it has something to do with the fact that the black series is a line and the blue series is a bar? I don't know; at this point I'm just stumped.

GOAL : future months show NOTHING until data has been entered on source tab. no zeroes, no #N/A, no nothing.

Why isn't this working?
posted by radiosilents to Computers & Internet (6 answers total) 1 user marked this as a favorite
 
How are your data labels formatted? If you change them to numbers, will the N/A go away?
posted by xingcat at 11:58 AM on December 15, 2015


Response by poster: xingcat : tried a bunch of options (including '0%;;;'), no dice. at best i can make the LABELS go away by using a custom format, but to do it I seem to have to have zero-value BARS (which is sub-optimal since the axis goes below zero).
posted by radiosilents at 12:12 PM on December 15, 2015


Response by poster: OK, it's a workaround BUT

through a combination of =iferror on the data set and then a custom label format, it's all kludged together.
posted by radiosilents at 12:17 PM on December 15, 2015 [1 favorite]


I found this link. Basically I don't think it's possible. You'll either have to manually delete the formula, or use that VBA formula to do it as suggested.

I do have one idea that I just quickly tested, and it kind of worked:

If you make your data summary a pivot-table instead, those cells will actually be blank, and as such won't return as Zero. You'll have to figure out if that works with your data layout.

On preview: looks like you found another way! Posting just in case you don't like that work-around.
posted by mayonnaises at 12:21 PM on December 15, 2015


Try taking out the "NA" formatting for the bar chart. It looks like you're just using it to suppress the zeroes? You can just have that removed if you take out that formula.
posted by xingcat at 12:25 PM on December 15, 2015


The formula you want to use to encapsulate is called =iferror( [current formula], 0)
posted by Nanukthedog at 1:17 PM on December 15, 2015


« Older How to HP print thru cable from Chromebook,or from...   |   Where are all the cool NYC lesbians swinging... Newer »
This thread is closed to new comments.