Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 1st 06, 02:17 PM posted to microsoft.public.excel.misc
ulfah
 
Posts: n/a
Default Blank (empty) cell always equal to 0??


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


  #2   Report Post  
Old February 1st 06, 02:42 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Blank (empty) cell always equal to 0??

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
  #3   Report Post  
Old February 1st 06, 03:42 PM posted to microsoft.public.excel.misc
Sloth
 
Posts: n/a
Default Blank (empty) cell always equal to 0??

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

  #4   Report Post  
Old February 1st 06, 04:55 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Blank (empty) cell always equal to 0??

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
blank cell turns to 0 LMB New Users to Excel 2 April 25th 05 03:57 PM
Look for change next blank cell in Range Nigel Bennett Excel Worksheet Functions 1 March 13th 05 09:45 PM
Text wider than one cell is not displayed in the next empty cell Len Pace Excel Discussion (Misc queries) 5 February 18th 05 12:24 AM
How do I make a cell equal to another cells value and not it's fo. TroutKing Excel Worksheet Functions 2 January 17th 05 06:15 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM


All times are GMT +1. The time now is 10:24 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017