Empty cells not recognized by formula as being blank

[vrzimmerm]

I'm new to Excel and am having trouble writing a formula that uses a
column which might contain any of the 3 following types of data: 1.
a date, 2. nothing at all or 3. appear to be be blank, but
actually shows a small carat in the formula window. It's situation
#3 that my formula doesn't seem to recognize. I want the formula to
treat these entries as tho they were totally blank, but I've not
found anything that works.

Any suggestions?

 

[Peo Sjoblom]

What's your formula and what do you want to do, if you want to sum a range
then all dates that are not text will be included?

 

[Pete_UK]

Try this:

=IF(A1="",empty_situation,non_empty_situation)

assuming the data is in A1. Substitute what you want to do for the
empty_situation and non_empty_situation, then copy down. If there is a
chance that you might have a <space> in there, amend it as follows:

=IF(OR(A1="",A1=" "),empty_situation,non_empty_situation)

Hope this helps.

Pete

 

[vrzimmerm]

Try this:

=IF(A1="",empty_situation,non_empty_situation)

assuming the data is in A1. Substitute what you want to do for the
empty_situation and non_empty_situation, then copy down. If there is a
chance that you might have a <space> in there, amend it as follows:

=IF(OR(A1="",A1=" "),empty_situation,non_empty_situation)

Hope this helps.

Pete

Pete, many thanks! You not only helped me decipher the 2 different
types of "blank" cells, but you showed me how to use the OR command
to embed multiple conditions into my formula. I'm familiar with
Lotus 123, but Excel works entirely differently. It worked like a
charm!

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete