Return a blank value for ""

  • Thread starter Thread starter TS
  • Start date Start date
T

TS

When you use cells containing formulas that return empty
double quotation marks in the plot range for a chart, the
result for the point values of those cells is zero. For
example, a line chart shows a continuous line going from
the previous point to zero and then to the next point.
Blank cells, however, result in a break in the line rather
than a zero value.

Does anyone know how to modify this so that "" returns a
blank value?
 
Hi
one way instead of "" return NA() in your formula. To hide this error
choose a conditional format and format these cells with a white font

HTH
Frank
 
Thank you for your suggestion, however, it does not appear
to work. My formula reads =IF(V4="","",SUM(T10:V10)/COUNT
(T10:V10)), so replacing the second "" with NA does not
produce the desired results.

Any other thoughts?

Thanks

TS
 
Hi TS
what goes wrong? I assume you entered the following formula:
=IF(V4="",NA(),SUM(T10:V10)/COUNT(T10:V10))

Does the chart look wrong or is the #NA error not displayed
Frank
 
Thank you for your reply. The issue with doing it that way
is there is a trend line associated with the value, so
while you you may hide the value, the chart displays an
incorrect trend line.

Thanks

T
 
Thank you for your suggestion, however, it does not appear
to work. My formula reads =IF(V4="","",SUM(T10:V10)/COUNT
(T10:V10)), so replacing the second "" with NA does not
produce the desired results.

Any other thoughts?
Click to expand...

One thought - try the suggestions made by others before dismissing them.

Peo's suggestion *DOES* work as you originally indicated *IN CHARTS* - the #N/A
value is handled exactly the same way that truly blank cells are. If all you
want to do with these formulas' results is plot them, then Peo's suggestion is
the *ONLY* way you can do this.

However, #N/A isn't handled the same way as either blank or "" in other
calculations. If you also need to use these formulas' results in other
calculations, then leave these formulas as-is and use another range to plot
them. The formulas in that range would look like

=IF(ISNUMBER(SomeCellRef),SomeCellRef,NA())

If you want to plot *AND* calculate values while treating nonnumbers as if they
were blank cells, you *MUST* use separate ranges. THERE IS NO ALTERNATIVE
because there's no #BLANK! value. Blank is *ONLY* a state that a cell is in if
it contains no formula at all.
 
Back
Top