kilograham

Level 1 (4 points)

iWork

Q: IF statement returning "0" on blank cells

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%

Posted on Aug 25, 2016 3:09 PM

I have this question too

Solved

Level 6 (19,421 points)

iWork

A:

=IF($A2="media",if(counta(B2)=0, "", B2),"")

Posted on Aug 25, 2016 5:05 PM

Q: IF statement returning "0" on blank cells

All replies

Helpful answers

by Wayne Contello,Solvedanswer

Wayne Contello Aug 25, 2016 5:05 PM in response to kilograham
Level 6 (19,421 points)

iWork

Aug 25, 2016 5:05 PM in response to kilograham

=IF($A2="media",if(counta(B2)=0, "", B2),"")
Reply options

Link to this post

by t quinn,

t quinn Aug 25, 2016 4:52 PM in response to kilograham
Level 5 (5,063 points)

Mac OS X

Aug 25, 2016 4:52 PM in response to kilograham

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
Reply options

Link to this post

by kilograham,

kilograham Aug 25, 2016 4:57 PM in response to t quinn
Level 1 (4 points)

iWork

Aug 25, 2016 4:57 PM in response to t 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.
Reply options

Link to this post

by kilograham,

kilograham Aug 25, 2016 5:04 PM in response to Wayne Contello
Level 1 (4 points)

iWork

Aug 25, 2016 5:04 PM in response to Wayne Contello

Boom! Thanks, Wayne.
Reply options

Link to this post

by kilograham,

kilograham Aug 25, 2016 5:07 PM in response to Wayne Contello
Level 1 (4 points)

iWork

Aug 25, 2016 5:07 PM in response to Wayne Contello

Thank you Wayne. Worked like a charm.
Reply options

Link to this post

by SGIII,

SGIII Aug 25, 2016 6:35 PM in response to kilograham
Level 6 (10,796 points)

Mac OS X

Aug 25, 2016 6:35 PM in response to kilograham

Or, a more compact formula that avoids nested ifs:

=IF(OR(A2<>"media",B2=""),"",B2)

SG
Reply options

Link to this post

by kilograham,

kilograham Aug 26, 2016 8:53 AM in response to SGIII
Level 1 (4 points)

iWork

Aug 26, 2016 8:53 AM in response to SGIII

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.
Reply options

Link to this post

by SGIII,

SGIII Aug 26, 2016 9:22 AM in response to kilograham
Level 6 (10,796 points)

Mac OS X

Aug 26, 2016 9:22 AM in response to kilograham

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
Reply options

Link to this post