Sometimes, the look of the data is important and #n/a's are pretty ugly. And
sometimes those values could be used in other formulas and errors could mess up
those subsequent formulas.
They could be hidden with format|Conditional formatting, though. And other
formulas could be rewritten to avoid the errors.
(I'd just hide the whole helper column after I made the chart.)
But your post does show a problem with my formula.
I didn't notice that the OP used " " (I always use "" (no space)).
And the OP uses a semicolon instead.
I think that this formula is better:
=if(trim(b2)="";na();b2)
But I'd actually change the original formula:
=IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;"")
Those spaces are can be a problem.
Sloth wrote:
why add another column, when you can just replace " " with NA()?
"Dave Peterson" wrote:
I'd add another column and use a formula like:
=if(b2="",na(),b2)
then use that column in the chart.
ulfah wrote:
Is it possible to have Excel read an empty cell as "no value" rather
than the value=0?
I have a graph and corresponding range with values and somtimes cells
in this range should be read as a "blank" cell! Resulting in *-no
point-* in the graph.
Assume that I have, in cell C5, the following function:
IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;" ").
If, in cell A5, I have "-" (a dash) = no value (no reading), this will
result in C5 being a Blank cell, due to the " " in the IF-function.
C5 is copied and pasted into the range connected to my graph. I want it
to result in no point in the graph!
But the graph is reading this cell as if the value was 0.0!
Is there a way to avoid this??
Your help is very much appreciated.
--
ulfah
------------------------------------------------------------------------
ulfah's Profile: http://www.excelforum.com/member.php...o&userid=29204
View this thread: http://www.excelforum.com/showthread...hreadid=507215
--
Dave Peterson
--
Dave Peterson