IF blank!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rexmann - 20 Sep 2007 11:42 GMT

Hi All

I have an IF statement which I want to identify if it has a blank cell or not.
So I do one formula if it is empty and one if it has a value

I have managed to do it with multiple columns using =istext but I want the
formula all in one cell. I have tried if cell=null and variations but can't
crack it.

Any suggestions? any help greatly appreciated

Rexmann
PS using excel 2003

Reply to this Message

Mike H - 20 Sep 2007 11:50 GMT

Hi,

2 options. The first looks for an empty string in A1 and the second looks
for a blank cell

=IF(A1="","My first formula","My other formula")
=IF(ISBLANK(A1),"My first formula","My other formula")

Mike

> Hi All
>
[quoted text clipped - 9 lines]
> Rexmann
> PS using excel 2003

Reply to this Message

rexmann - 20 Sep 2007 12:10 GMT

Hi Mike

Nice one, worked as it says.

Just for the record (anyohe else reading the post) I used the ISBLANK function

Cheers Rexmann

> Hi,
>
[quoted text clipped - 19 lines]
> > Rexmann
> > PS using excel 2003

Reply to this Message

Dave Peterson - 20 Sep 2007 13:19 GMT

Why would you use =if(isblank(a1), ... instead of =if(a1="",....

Just curious.

> Hi Mike
>
[quoted text clipped - 27 lines]
> > > Rexmann
> > > PS using excel 2003

Signature

Dave Peterson

Reply to this Message

David Biddulph - 20 Sep 2007 13:45 GMT

He'd need to do it that way if he were trying to distinguish between a truly
empty cell and one that might have an empty string returned by a formula.

Signature

David Biddulph

> Why would you use =if(isblank(a1), ... instead of =if(a1="",....
>
> Just curious.

>> Hi Mike
>>
>> Nice one, worked as it says.
>>
>> Just for the record (anyohe else reading the post) I used the ISBLANK
>> function

>> > Hi,
>> >
[quoted text clipped - 4 lines]
>> > =IF(A1="","My first formula","My other formula")
>> > =IF(ISBLANK(A1),"My first formula","My other formula")

>> > > Hi All
>> > >
[quoted text clipped - 12 lines]
>> > > Rexmann
>> > > PS using excel 2003

Reply to this Message

Dave Peterson - 20 Sep 2007 13:55 GMT

I'm still curious if that's the distinction that the OP wanted.

> He'd need to do it that way if he were trying to distinguish between a truly
> empty cell and one that might have an empty string returned by a formula.
[quoted text clipped - 37 lines]
> >> > > Rexmann
> >> > > PS using excel 2003

Signature

Dave Peterson

Reply to this Message

Mike H - 20 Sep 2007 14:06 GMT

The OP referred to both Blank and Empty which is why both were provided.

>I have an IF statement which I want to identify if it has a blank cell or not.
>So I do one formula if it is empty and one if it has a value

Mike

> I'm still curious if that's the distinction that the OP wanted.
>
[quoted text clipped - 39 lines]
> > >> > > Rexmann
> > >> > > PS using excel 2003

Reply to this Message

Dave Peterson - 20 Sep 2007 16:48 GMT

I wasn't curious why you provided both answers. That made sense to me.

> The OP referred to both Blank and Empty which is why both were provided.
>
[quoted text clipped - 50 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

susann - 29 May 2008 05:41 GMT

Hi Mike, and anyone else reading this.

This formula is exactly what I'm looking for - except I can't get it to work
properly.

My formula is:

=IF(B5="","=MAX(C5-D5+E5,0)","=MIN(B5+D5-E5)")

So, if C5 is blank, run the first formula which shows the positive value of
certain cells, or run the other formula which shows the negative value of
other cells.

However instead of getting a value the text just shows in the cell.

What am I doing wrong?

Thanks
Susan

> Hi,
>
[quoted text clipped - 19 lines]
> > Rexmann
> > PS using excel 2003

Reply to this Message

Rick Rothstein (MVP - VB) - 29 May 2008 06:03 GMT

The problem is you put quote marks around the formulas (turning them into
pure text) and, after you remove the quote marks, you will also have to
remove the equal signs (the only equal sign needed to tell Excel what
follows is a formula is the leading equal sign)...

=IF(B5="",MAX(C5-D5+E5,0),MIN(B5+D5-E5))

Rick

> Hi Mike, and anyone else reading this.
>
[quoted text clipped - 44 lines]
>> > Rexmann
>> > PS using excel 2003

Reply to this Message