conditional formating to keep the #N/A error from being visible

[irvine79]

I am putting together a line graph that will be updated hourly. I do not want
it to show the next data point until the update is entered on the graph. When
the related cells on the graph are blank though the line graph dips to zero
for that data point.

My formula is basically:
=IF(A1>0,A1,"")

The advice I recieved:

Excel treats "" as text, with a numerical value of zero. And even if A1 is
blank, its value is brought over as zero in the first formula. Use this
formula instead:

=IF(A1>0,A1,NA())

This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in
the cell, which you can hide with conditional formatting.

So...
I either need a new solution to the original question or help with
conditional formatting so I don't have to see the ugly #N/A error.


Thanks alot!

 

[pinmaster]

Hi,

In conditional formatting use:

=ISERROR(A1)

Cheers!
Jean-Guy

 

[JE McGimpsey]

I'd suggest creating a dynamic range in the worksheet that includes only
non-blank data points, then using that range as your source. See

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

for a good description.

 

[irvine79]

So if I am understanding you... in the conditional formatting i would select
"equal to" and then type "=ISERROR(A1)"? If so it is not working. Please
clarify if I misunderstood.

thanks

 

[pinmaster]

Hi,

No, use "Formula is:" instead of "Cell value is:"

Cheers!
Jean-Guy

 

[irvine79]

Thanks JE, dynamic range is just what I needed. And the tutorial was a big
help.