Excel Graph NAs

[forcewalker]

I am trying to make a graph that changes colors when it reaches its target. I.E. Below Target = Green Above Target = Red.

I have the data set up in three columns, <=Target, >Target, and Target.

The formulas I have are =IF(U121<=CF121,U121,NA()) and =IF(U121>CF121,U121,NA())

My problem lies whenever the data gets transferred into a graph, instead of Excel only graphing the numeric values and changing based on below or above target it graphs #N/As.

Is there a way to tell excel not to graph the #N/A but only the numeric values?

 

[Jon Peltier]

What kind of chart? The #N/A thing only suppressed plotting of points in marker-based charts (line and scatter). Otherwise you still get a point plotted (a bar) and its value gets displayed as #N/A.

If it's a column chart, use stacked columns, or use clustered columns with an overlap of 100%. Instead of NA() in your formulas, use "" (two double quotes). This gets plotted as zero, but a data label won't show, because the text of the label will be "".