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

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(A10,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(A10,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!

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

Hi,

In conditional formatting use:

=ISERROR(A1)

Cheers!
Jean-Guy

"irvine79" wrote:

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(A10,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(A10,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!

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

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/...umnChart1.html

for a good description.

In article ,
irvine79 wrote:

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(A10,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(A10,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!

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

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" wrote:

Hi,

In conditional formatting use:

=ISERROR(A1)

Cheers!
Jean-Guy

"irvine79" wrote:

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(A10,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(A10,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!

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

Hi,

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

Cheers!
Jean-Guy



"irvine79" wrote:

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" wrote:

Hi,

In conditional formatting use:

=ISERROR(A1)

Cheers!
Jean-Guy

"irvine79" wrote:

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(A10,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(A10,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!

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

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

"JE McGimpsey" wrote:

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/...umnChart1.html

for a good description.

In article ,
irvine79 wrote:

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(A10,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(A10,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!