CODEWITHSUNDEEP
excelchartsline-plot
Nonancourt
Nonancourt

Reputation: 559

How do I ignore non-numeric cells in a chart?

In Excel 2016, I have time series that may contain cells with missing data, typically labelled with a string. I'd like to plot the series ignoring non-numeric cells, but Excel wants to plot them as zeros. Here is how it looks:

strings are converted into zeros

A workaround could be to create another column with something like

=IF(C13="#N/A N/A",NA(),C13)

as suggested here.

However, I'd like to know if there is a way to avoid creating another column. Please note that I have selected the option: "Hidden and Empty Cell Settings"/"Show empty cells as: Gaps", but that doesn't seem to help.

Upvotes: 2

Views: 14956

Answers (3)

curious
curious

Reputation: 1500

Yes that's right, Excel Graph treats strings as zeros. The "Show empty cells as Gaps" option under "Hidden and Empty Cell Settings" won't work either, since these aren't empty cells.

Apart from creating another column, there are a couple of alternatives that you can think of.

Possible solutions:

  • Option 1 - The solution to this is what was actually considered as an issue in your provided link. If the "Series" column contains an actual #N/A error, rather than a string (eg #N/A N/A or any other string) as shown in your screenshot, Excel Graph will ignore those items and will display what is desired. If this column contains formula such as VLOOKUP, it should automatically generate "#N/A" for errors and the graph will ignore that.

  • Option 2 - The other option being not to show the date if the "Series" column data is not a number. So if the cell address with the date "28/12/2016" is A3, replace it with the formula =IF(ISNUMBER(B3),A2-1,"") and copy it to rest of the rows below.

Upvotes: 1

Paco
Paco

Reputation: 1

I found a SOLUTION THAT WORKS:

Problem: All the cells with "n/a" plotted on the graph as zero (0) values.

My formula producing the graphing problem WAS this: =IFERROR(AVERAGE(D$90:D$107),"n/a")

Other Failed "solutions" (in my case):
Replacing the "n/a" to these forms, also with quotation marks in that part of the formula: "NA", "#NA", "#N/A()", "#NA()", and even an empty cell "" (quotes with no spaces or text inside).
Nothing worked.

THIS WORKED :-) :

I clicked the Fx function button, typed ERROR in function types to search.
Among function options listed below was the FUNCTION: NA() This function returns an Excel-generated #N/A error output, not just my text that says N/A.

So I changed the end of my formula from "n/a" to NA() No quotes.
No "=" sign allowed there either (or Excel alerts error).

Result: =IFERROR(AVERAGE(D$90:D$107),NA())

That worked!!! Those cells in the table display show #N/A, and the GRAPH has NO zeros or graph lines in the range of those #N/A cells.

CONCLUSION:
The graph option to Not plot N/A's needed Excel-generated #N/A's, not just text strings that appear that way.

Thanks for posting other options and solutions, that led me to try this.

Upvotes: 0

Jsleshem
Jsleshem

Reputation: 735

Surround your function with an iferror. For example, write: =IfError(YourFunction, ""). This way, you will have a blank cell if there is an error and the graph with not represent it.

Upvotes: 0

Related Questions