'If' Function and empty cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is what I have:

=if(H11>B$2, "X", " ")

B2 in this case is equal to 1 and H11 is equal to 2, therefore 2>1 and the result is X in the cell.

The problem I am having is when I copy that formula down and the value in the H column that I am refering to is " " (nothing). For some reason, "nothing" is greater than 1 and I get an X in my cell. Does anyone have any suggestions? I don't want to change the nothing values to zeros in my H column, I would like the cell to appear empty.

Thanks.
ANB
 
Are the nothings a space (" ") or null ("")?

--
HTH,
Laura Cook
Appleton, WI


ANB said:
Here is what I have:

=if(H11>B$2, "X", " ")

B2 in this case is equal to 1 and H11 is equal to 2, therefore 2>1 and the result is X in the cell.

The problem I am having is when I copy that formula down and the value in
Click to expand...
the H column that I am refering to is " " (nothing). For some reason,
"nothing" is greater than 1 and I get an X in my cell. Does anyone have any
suggestions? I don't want to change the nothing values to zeros in my H
column, I would like the cell to appear empty.
 
Using a space (that is, " ") for nothing is not a good idea. If column H
houses formulas that return a space, make them return "" instead. That said,
try:

=IF(ISNUMBER(H11)*(H11>B$2), "X", "")

ANB said:
Here is what I have:

=if(H11>B$2, "X", " ")

B2 in this case is equal to 1 and H11 is equal to 2, therefore 2>1 and the result is X in the cell.

The problem I am having is when I copy that formula down and the value in
Click to expand...
the H column that I am refering to is " " (nothing). For some reason,
"nothing" is greater than 1 and I get an X in my cell. Does anyone have any
suggestions? I don't want to change the nothing values to zeros in my H
column, I would like the cell to appear empty.
 
I was putting in spaces (" "), so I changed them all to nulls (""). Thanks for that tip, makes sense now. I also used the formula you suggested and that returned the results I was expecting. Could you tell me in words what that formula is doing? I don't understand why the multiplication sign is in the formula? Thanks for your help

----- Aladin Akyurek wrote: ----

Using a space (that is, " ") for nothing is not a good idea. If column
houses formulas that return a space, make them return "" instead. That said
try

=IF(ISNUMBER(H11)*(H11>B$2), "X", ""

ANB said:
Here is what I have
Click to expand...
the H column that I am refering to is " " (nothing). For some reason
"nothing" is greater than 1 and I get an X in my cell. Does anyone have an
suggestions? I don't want to change the nothing values to zeros in my
column, I would like the cell to appear empty
 
=IF(ISNUMBER(H11)*(H11>B$2), "X", "")

is a shorter version of:

=IF(AND(ISNUMBER(H11),(H11>B$2)), "X", "")

The * is the algebraic version of AND, so to speak. The first formula
exploits the fact that a non-zero number in the condition part of IF is
interpreted as TRUE and a 0 as false. So, when H11 is a number and H11 > B2,
we get:

=IF(TRUE*TRUE,"X","")

The multiplication operator coerces TRUE into 1, which stands for TRUE in
Excel . Thus:

=IF(1*1,"X","")

=IF(1,"X","")

leads to X as result.

ANB said:
I was putting in spaces (" "), so I changed them all to nulls ("").
Click to expand...
Thanks for that tip, makes sense now. I also used the formula you suggested
and that returned the results I was expecting. Could you tell me in words
what that formula is doing? I don't understand why the multiplication sign
is in the formula? Thanks for your help!
 
Back
Top