Computer Hope
Software => Computer software => Topic started by: mlakar on March 16, 2006, 10:56:47 AM
-
I'm using the IF formula in Excel, and I want to make the cell blank if the statement is false. How do I do that?
The formula =IF(logical expression, 1, "") doesn't work, because if you check to see if the cell is blank using the ISBLANK function, it comes up FALSE (meaning the cell is NOT blank). Go ahead, try it. It doesn't make sense.
So, what do I do? I've tried replacing "" with NULL, BLANK, EMPTY, etc. with no luck. Does anyone know how to do this?
Thanks,
Joe
-
So, what do I do?
Use a different test - i.e. instead of IF(ISBLANK(A1)...) use IF(A1=""...).
-
With the formula =IF(logical expression, 1, ""), the cell is not empty because it contains a formula.
Why would you want the cell to be totally blank?
-
Hmmm, interesting. So, any cell that contains a formula can never be blank?
I want it to be blank because I'm plotting the data. If a cell contains "", Excel plots it as a 0 (zero). However, I don't want it to be plotted as a zero, I want it NOT to show up on the plot. Any ideas?
-
How could a cell be considered blank while it contains a formula?
Cold you just manually exclude the empty cells from your data range?
-
I could manually exlude them... but that would be over a thousand manual exclusions. So, I was looking for a better way...
-
If a cell contains "", Excel plots it as a 0 (zero).
That is ONLY because of the way the formatting is set up for that cell. Have you tried my above suggestion?
-
Normally, I don't delv into such topics as many things are truely to variable in software of this size. However, I have a bad habit of reaching out and slapping those that go "Duh! You can't do this because of this!" without taking a second to think, or show reason why.
First of all... Because a cell contains a formula, does not mean that it cannot be counted as blank. You can in fact create a worksheet, add a text value to certain cells between A2 and B5, leaving some blank and one with a formula in it (ie: =IF(B4<30,"",B4)), and get the end results using =COUNTBLANK(A2:B5). The result of this would conclude in the sum of blank cells including the cell with a solution inside, but no set value.
=COUNTBLANK(?:?) will indeed count all blank cells, and cell with "" value, but not cells with "0" value.
=ISBLANK(?) checks to see if a cell is "empty". Empty and Blank are not the same thing.
Now, if I understand your question properly. What you are trying to do is validate a field and based on the validity, judge whether the cell should be blank or not. (ie: FALSE = "", and "" counting as a blank cell").
What I don't understand is what your doing with the results. Whether your just trying to validate, count, sort, etc... Also, while I'm typing this, I should ask if you've tried what I call "outside solutions? Devoting a page of your worksheet to remote solutions/cells?
Allow me more information, and I'll have your problem solved. This all of course is based on the assumption that you have Office '03, since you haven't stated otherwise.
Good Luck,
Robbie
-
Hmmm, interesting. So, any cell that contains a formula can never be blank?
I want it to be blank because I'm plotting the data. If a cell contains "", Excel plots it as a 0 (zero). However, I don't want it to be plotted as a zero, I want it NOT to show up on the plot. Any ideas?
Robbie, read before you post ;)
-
Hmmm, interesting. So, any cell that contains a formula can never be blank?
I want it to be blank because I'm plotting the data. If a cell contains "", Excel plots it as a 0 (zero). However, I don't want it to be plotted as a zero, I want it NOT to show up on the plot. Any ideas?
Robbie, read before you post
This is not a clear explanation of whats being done with the end results, or even the blank and/or non-blank cells. I could just be reading it wrong though.
"When a cell contains "", Excell plots it as a 0 (zero)"
Do you mean visually? There is a way to turn a feature on/off in Excel, that when a cell contains "", it will visually show a "0". By default, I believe this is off, but he could have it on and be referring to that. On the otherhand, he could simply mean that in the background for things like ISBLANK, it reads the clear cell as a "0". If that is the case, it's possible to use another formula that looks for blank and not empty.
For the sake of arguement, it cannot be assumed that the original poster even knows that in Excel, blank and empty are very different. They are read differently, and most of the time there is an equivelant solution for each to conclude in a like result of their own meaning.
As I mentioned in my original post, I don't normally reply to software problems of this nature. Because normally someone, like you, will feel offended or feel the need to defend themselves in an imaginary arguement. I am not now, nor do I wish to argue with anyone. I am simply stating options, and seeking more detailed feedback to further help the original poster.
Good Luck,
Robbie
-
*cough*
If a cell contains "", Excel plots it as a 0 (zero).
That is ONLY because of the way the formatting is set up for that cell. Have you tried my above suggestion?
-
Hey mlakar, before everyone is getting deeper in argument, I suppose what you mean by plotting the data means plotting a graph?