Ignore Zeros and 1/0/1900 in graph

[Kscotten]

Hi everyone. I have a workbook that automatically generates a scatter plot based on the data that is entered on a daily basis, however the graph will not show up unless I delete all the Zeros and all the dates that show up 01/0/1900. Is there anyway to have the graph ignore the Zeros and 01/0/1900?

[MarvinP]

Hi Kscotten and welcome to the forum,

From your description, I believe an Advanced Filter of your data to eliminate data and build a table of good data to the right, using the Copy To: option, is appropriate. Then do a scatter chart of the filtered data.

[Kscotten]

Hi Marvin,

Thank you for the response I'm going to try and play around with this some tonight to see if I can get it to work. I was hoping to be able to solve this with an IF then statement of some kind but no matter what I try the graph crashes because the data is entered on a daily basis and the graph is trying to account for the zeros and/or the incorrect dates (01/0/1900) that excel puts in as a placeholder if that date has not been entered for the month yet.

[MrShorty]

It's not clear to me exactly how these 0 values fit into your data and chart. This essay may have some ideas for ignoring blank data: https://peltiertech.com/mind-the-gap...g-empty-cells/

[Kscotten]

Thanks for the response, I believe I solved the "0" value issue with the help of that article. My only issue now is how to have the line graph ignore cells that don't have a date in them and are assigned a "01/0/1900"

This causes the entire chart to be a jumbled mess until you clear out these values.

[MrShorty]

It's still not clear to me exactly what you are doing or what the problem is. I would note that 1/0/1900 is the number 0 formatted as date, so it looks to me like the same kind of problem for the x axis data as for the value data. As noted in Peltier's essay, line and scatter charts seem to work well by replacing 0 value with NA(). Without more detail, all I can suggest is to add a helper column =IF(date=0,NA(),date). Then use that helper column for the horizontal category axis data (and maybe make sure the Excel is using a date axis in your line chart) and see if that is enough to get Excel to ignore these points.