How does Excel recognise a blank cell within IF function...

[papichulo184]

Hi All,

How can i return a blank cell if the formula is looking at a blank cell with IF function?

E.g. IF(A1=" "," "," ")

(This is part of a bigger statement which returns a date if and when there is a date in the cell, if there isn't it comes up with an answer even though it should be blank)

Many thanks

 

[steve the excel guy]

Oh sorry. I thought it was the same problem, different issue.

Anyway. The way this works is you type in a word or words in A12 then the Mid formulas separate the letters including spaces as you can see at G17. Then on the next row down, it is converted to a number via the Lookup command. All the letters correctly encrypt to a number except for the space. G17 is a space and is converted to an error on G18. I tried including a blank to blank cell in the LOOKUP section after your suggestion. In other words, I left the A column cell blank and the B column cell blank and all I really changed was the LOOKUP command. It was A$50$ and B$50$ and I changed it to A$51 and B$51 to include a blank cell. This didn't work.

I may be confusing my question by the overexplanation above but do you see what I'm trying to do and the error I am getting?

 

[pgc01]

You don't have to change the table. Just exclude the space character from the lookup.

In A18:

=IF(OR(A17="",A17=" "),"",(LOOKUP(A17,$A$25:$A$50,$B$25:$B$50)))

Copy accross.

This way you are excluding both blank cells and cells with a space.

Hope this helps.

 

[steve the excel guy]

ok, I see how that works. I tried it and it does work! Thank you much!

 

[pgc01]

I'm glad your problem is solved.

By the way, if you wanted to include the space in the conversion table you should include it before the letters and not after (in your case in row 24 and not 51).

Check the help for Lookup(). The lookip values should be in ascending order.