How do I get an IF statement to display a blank cell instead of "0" when it encounters an empty cell in the formula?
I want E2 to be blank is B2 is blank
In Cell E2
=IF($A2="media",B,"")
A | B | C | D | E | F | G | |
1 | media | 0.1% | 0.5% | 1.5% | 0.1% | 0.5% | 1.5% |
2 | media | 0.0% | 0.0% | 0.0% |
=IF($A2="media",if(counta(B2)=0, "", B2),"")
Hi kilograham,
My preferred approach would be to hide the 0% with a conditional highlighting rule:
If the value is 0 then the text is white. I tend to allow a very pale grey when I do this for myself.
Another approach would be to test B2 before you proceed with your original IF
E3= IF(LEN(B>0),IF($A3="media",B,""),"")
This tests if there is a character in B.
quinn
Thanks Quinn, but if the figure is still there (albeit unseen to the naked eye), it will change median calculation applied later to that column.
Boom! Thanks, Wayne.
Thank you Wayne. Worked like a charm.
Or, a more compact formula that avoids nested ifs:
=IF(OR(A2<>"media",B2=""),"",B2)
SG
Thanks SG. Also works, but I can't figure out why. I get the other solution: If count if 0, then make cell blank, but your logic is smarter than me. Can you explain what you did? Might help me understand the logic and create new formulas.
kilograham wrote:
your logic is smarter than me. Can you explain what you did?
Sure. I actually find the logic easier to follow than nested ifs, which sometimes can get confusing.
All the formula says is:
If A2 contains something other than "media" or B2 is blank then put a blank. Otherwise, put the value that appears in B2.
The OR(A2<>"media",B2="") is the part that tests whether A2 has something other than "media" or B2 is blank.
Put that part as the <test> in the usual IF(<test>,<value-if-true>,<value-if-false>) and you get:
=IF(OR(A2<>"media",B2=""),"",B2)
Short and sweet.
If you haven't done so already already, you can check out Formulas and Functions Help for IF. (You can also get there via Help > Formulas and Functions Help in your menu.)
SG
IF statement returning "0" on blank cells
