Empty cells not recognized by formula as being blank

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vrzimmerm@hotmail.com - 12 Apr 2007 15:40 GMT

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?

Reply to this Message

Peo Sjoblom - 12 Apr 2007 16:17 GMT

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?

Signature

Regards,

Peo Sjoblom

> 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.
[quoted text clipped - 5 lines]
>
> Any suggestions?

Reply to this Message

Pete_UK - 12 Apr 2007 16:59 GMT

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

On Apr 12, 3:40 pm, vrzimm...@hotmail.com wrote:

> 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.
[quoted text clipped - 5 lines]
>
> Any suggestions?

Reply to this Message

vrzimmerm@hotmail.com - 12 Apr 2007 21:15 GMT

> Try this:
>
[quoted text clipped - 21 lines]
>
> > Any suggestions?

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!

Reply to this Message

Pete_UK - 13 Apr 2007 00:32 GMT

Glad to be of help - thanks for feeding back.

Pete

On Apr 12, 9:15 pm, vrzimm...@hotmail.com wrote:

> > Try this:
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Reply to this Message