inserting blanks
Posted by Eric on May 24, 2001 12:16 PM
I want column (B)to report the value in column (A) only if it is greater than 0. I used this formula
=if(a3>0,a3,"")
Now the problem is that I want to treat the cells in column (B) that did not return a number (those less than or equal to 0) as if they were blank. I have tried pasting as values but it still gives me some sort of code in the apparently blank cells. Any help?
Posted by Aladin Akyurek on May 24, 2001 12:29 PM
Eric
The cell whose value is a blank (d.i. "") has a formula [your =if(a3>0,a3,"")] that produces that blank value. There is thus a difference between a cell with no value and no formula and a cell with no value (or a "blank" value) and a formula that produces that blank value.
Hope this helps.
Aladin
Posted by Eric on May 24, 2001 1:01 PM
Sorry if I wasnt clear
Thanks for the reply. My actual frustration though, is that the "paste as values" function is also not giving me a blank cell.
Posted by Barrie Davidson on May 24, 2001 1:18 PM
Re: Sorry if I wasnt clear
How about changing your formula to =IF(A3>0,A3," "), then copying them as values, then use FIND AND REPLACE (where you look for a space and replace it with nothing)?
Barrie
Posted by Aladin Akyurek on May 24, 2001 1:55 PM
Blank Vs Empty
Eric & Barrie
OK, lets forget that "elementary bit" in my first post. :)
Now, back to your observation. Consider the following:
{1;3;5;0.6;-4}
These numbers occupy A1:A5.
I applied in B1 =IF(A1>0,A1,"")
You get:
{1;3;5;0.6;""} in B.
Now try in C5:
=ISBLANK(B5)
You get as result: FALSE.
Eric's attempt Paste Special, Values does not change the result just mentioned.
Barrie -- I applied your version of IF with " " and did Find/Replace (" " --> "").
The ISBLANK test results again in FALSE.
Now the funny thing.
Activate C5 and hit Delete key on the keyboard.
The ISBLANK test now gives TRUE.
How about that?
This issue was a subject in another thread:
Aladin
=================================== : Thanks for the reply. My actual frustration though, is that the "paste as values" function is also not giving me a blank cell.
Posted by Barrie Davidson on May 25, 2001 6:34 AM
Re: Blank Vs Empty
Excel can be frustrating sometimes, can't it? Thanks for the info Aladin. Eric & Barrie OK, lets forget that "elementary bit" in my first post. :) Now, back to your observation. Consider the following: {1;3;5;0.6;-4} These numbers occupy A1:A5. I applied in B1 =IF(A1>0,A1,"") You get: {1;3;5;0.6;""} in B. Now try in C5: =ISBLANK(B5) You get as result: FALSE. Eric's attempt Paste Special, Values does not change the result just mentioned. Barrie -- I applied your version of IF with " " and did Find/Replace (" " --> ""). The ISBLANK test results again in FALSE. Now the funny thing. Activate C5 and hit Delete key on the keyboard. The ISBLANK test now gives TRUE. How about that? This issue was a subject in another thread: 16565.html
Posted by Eric on May 25, 2001 7:27 AM
Thanks to you both for your help
Ill fiddle with a couple of other ideas to see if I cant get this lis to do what I want, Thx again
Posted by Eric on May 25, 2001 8:15 AM
Hey that worked! Much thanks
Thanks for the workaround
