function if problem

Microsoft Office 2007 professional (aca...
June 3, 2010 at 06:58:19
Specs: Windows
Dear pro office user out there,i need your help
with some excel problem. The problem is like
this, i am using this function
= IF(AF37="0"," ",AF37) in cell H30
the cell af37 is the sum of three other cell
added together. When the addition is equal to
zero, i want cell H30 to appear as " " (space
bar) if not it will appear as the value shown on
AF37. However the problem is that when the 3
value added together in AF37 = zero, it do not
return as " "(space bar) in cell H30.
Could anyone enlighten me a bit? thanks alot

See More: function if problem

Report •

June 3, 2010 at 07:06:09
When you enclose the 0 (zero) in quotes, Excel thinks you mean the text string 0, not the value 0.

Remove the quotes and you should be fine.

= IF(AF37=0," ",AF37)

P.S. Do you really want a space " " or do you want an empty cell ""?

Excel will treat a " " differently than a "", so it depends on what you doing with H30 when deciding which to use.

Report •

June 3, 2010 at 07:12:06
YESH!! you are my saviour haha.. hmm its like this.. i'm working
on this excel spreadsheet that has already a if rule that state if
that box is " "(space bar) it will return a space bar so its empty.
So i'm just trying to adhere to the spreadsheet. Everything is
now correct already but can the excel return empty cell when a
value is met? i just want to know more. =)

Report •

June 3, 2010 at 07:31:41
re: "can the excel return empty cell when a value is met?"



Put this in H30 then check the Length.


"" will return 0 while your formula (" ") will return 1.

There are other funtions that will return different results for a "" vs. a " " so it really depends on what you are doing with the result.

Report •

Related Solutions

June 3, 2010 at 08:01:56
hmm.. i think i get it!
i will be using the " " for this cell though
thanks so much for willing to spent your precious time explaining
to me. I really appreciate it. Thank you once again =)

Report •

Ask Question