How to differentiate between a 0 value and no data on a graph

[scottwc]

Hi,

I have a series of column graphs that plots data that can range from -2 to +2, including 0. Some of my data points are missing, these have been coded as ND for now.

My issue is that when I do my column graphs, there is no way to tell between a value that is a 0 and a missing data point. Is there a way to have excel plot an X or similar when there is a missing data point?

Thanks,
Scott

[MarvinP]

Hi Scott,

There is a dialog box deep in the graph dialogs that asks if you want to use the zero as a space or use them. Read:

https://support.office.com/en-us/art...5-a00e9e476b03
I found a picture of it at:
http://www.excelitems.com/2011/01/pr...lot-chart.html

Is this what you were looking for?

[scottwc]

Hi Marvin,

Sort of. Let me show you an example graph.

In the attached picture, the values for item 53 are -2, -2, -2, -1, 0 which displays fine.

However, for item 54, the values are ND (the item was not completed), -1, -2, -1, 0.
In this case, it is not possible to tell the difference between where there is no data, and where there is a '0' value.
Ideally, I would like some way to show the difference between these two values. Perhaps a x in the column where the No Data value is.

Thanks,
Scott

[MrShorty]

Is the ND referring to a cell containing the text string "ND", or is it referring to a truly blank cell?

A truly blank cell can be differntiated from a 0 value using the ISBLANK() function. A cell that is truly blank will return TRUE if it is truly blank, but a cell that contains a zero value (or anything else including empty string "") will return FALSE.

Where the question is in the context of charting, a cell containing an N/A error value (which can be generated using the NA() function, if needed) is kind of ignored by charts (depending on the chart type and exactly what you are trying to do). This essay describes different chart behavior towards blanks, 0s, and N/As: https://peltiertech.com/mind-the-gap...g-empty-cells/

Does any of that help, or is it just adding confusion?