IF in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig Coope - 24 Mar 2007 16:20 GMT

Is there a maximum amout of multiple IFs you can have in one cell?

I have a formula that should work but says there is an error, cut it down it
works.

I have 24 ifs in the cell.....

Cheers,

Craig...

Reply to this Message

Nick Hodge - 24 Mar 2007 16:23 GMT

Craig

It's seven in versions up to 2007, 2007 has more. Consider a data table and
using VLOOKUP will normally work better

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> Is there a maximum amout of multiple IFs you can have in one cell?
>
[quoted text clipped - 7 lines]
>
> Craig...

Reply to this Message

Dave Peterson - 24 Mar 2007 16:27 GMT

There's a limit on how long the formula can be. In xl2003 and below, you get
1024 characters (measured in R1C1 reference style).

And you can only nest 7 functions.

But if the "if's" aren't nested and you don't exceed the length limit, then
there's no problem.

Without knowing what your formula is...

Chip Pearson offers some alternatives to nesting if statements:
http://cpearson.com/excel/nested.htm

(It was written before xl2007 raised the limits.)

> Is there a maximum amout of multiple IFs you can have in one cell?
>
[quoted text clipped - 6 lines]
>
> Craig...

Signature

Dave Peterson

Reply to this Message

Don Guillett - 24 Mar 2007 17:01 GMT

How about telling us what you are trying to do

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Is there a maximum amout of multiple IFs you can have in one cell?
>
[quoted text clipped - 7 lines]
>
> Craig...

Reply to this Message

Craig Coope - 24 Mar 2007 17:11 GMT

> How about telling us what you are trying to do

I'm trying to use this:

=IF((W1=1),"HIN",IF((W1=2),"HIN VAN",IF((W1=3),"LET",IF((W1=4),"LET VAN
1",IF((W1=5),"LET VAN 2",IF((W1=6),"LET VAN
3",IF((W1=7),"EXE",IF((W1=8),"LIV",if((W1=9),"LIV VAN 1",IF((W1=10),"LIV VAN
2",IF((W1=11),"LIV VAN 3",IF((W1=12),"PRE",IF((W1=13),"PRE
VAN",IF((W1=14),"CRO",IF((W1=15),"SWA",IF((W1=16),"SWA
VAN",IF((W1=17),"LEE",IF((W1=18),"LEE
VAN",IF((W1=19),"NEW",IF((W1=20),"CHE",IF((W1=21),"CHE VAN
1",IF((W1=22),"CHE VAN 2",IF((W1=23),"CHE VAN 3",IF((W1=24),"CHE VAN
4",""))))

It is probably very sloppy but I'm trying to get the cell to display a name
depending on what number it contains.

Cheers....

Craig....

Reply to this Message

Don Guillett - 24 Mar 2007 17:47 GMT

In this case I would suggest a lookup table since you may want to change
later. Have a look in the help index for VLOOKUP.
1 Hin
2 Hin Van

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
>> How about telling us what you are trying to do
[quoted text clipped - 19 lines]
>
> Craig....

Reply to this Message

Ragdyer - 24 Mar 2007 18:01 GMT

Hey Don, where are you located?

I'm in So. Cal. on daylight saving time, and your post wasn't there 5
minutes ago.

Are you in Texas on std. time?

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> In this case I would suggest a lookup table since you may want to change
> later. Have a look in the help index for VLOOKUP.
[quoted text clipped - 23 lines]
> >
> > Craig....

Reply to this Message

Don Guillett - 24 Mar 2007 18:30 GMT

RD,
I'm in Austin and its 12:20 CDST

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hey Don, where are you located?
>
[quoted text clipped - 30 lines]
>> >
>> > Craig....

Reply to this Message

Ragdyer - 24 Mar 2007 17:50 GMT

You could enter your list in say A1 to A24, then use this formula if there
will always be data present:

=INDEX(A1:A24,W1)

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>
> > How about telling us what you are trying to do
[quoted text clipped - 17 lines]
>
> Craig....

Reply to this Message

Craig Coope - 24 Mar 2007 18:18 GMT

> You could enter your list in say A1 to A24, then use this formula if there
> will always be data present:
[quoted text clipped - 4 lines]
>
> RD

Thanks for everyones help. I used a vlookup...I just want to get rid of
those pesky #N/As now!

Reply to this Message

Dave Peterson - 24 Mar 2007 18:27 GMT

=if(iserror(vlookup(...)),"",vlookup(...))

You could use any old string instead of ""--maybe "Invalid" or "Missing"

> > You could enter your list in say A1 to A24, then use this formula if there
> > will always be data present:
[quoted text clipped - 7 lines]
> Thanks for everyones help. I used a vlookup...I just want to get rid of
> those pesky #N/As now!

Signature

Dave Peterson

Reply to this Message

Tom Ogilvy - 24 Mar 2007 23:25 GMT

if you don't want to tie up a bunch of cells with a look up table, you can
use

=if(And(W1>=1,W1<=24),Choose(W1,"HIN","HIN VAN","LET","LET VAN 1","LET VAN
2","LET VAN 3","EXE","LIV","LIV VAN 1","LIV VAN 2","LIV VAN 3","PRE","PRE
VAN","CRO","SWA","SWA VAN","LEE","LEE VAN","NEW","CHE","CHE VAN 1","CHE VAN
2","CHE VAN 3","CHE VAN 4"),"")

Signature

Regards,
Tom Ogilvy

>> How about telling us what you are trying to do
>
[quoted text clipped - 18 lines]
>
> Craig....

Reply to this Message

Craig Coope - 24 Mar 2007 19:55 GMT

OK...last bit of help for today I hope!

I have a very simple =A1 in cell f45 which is fine when there is something
in A1 but if the cell is blank I get a zero in f45. Any ideas on how to get
a blank cell?

Cheers...

Reply to this Message

Pete_UK - 24 Mar 2007 21:42 GMT

Try this:

=IF(A1="","",A1)

Hope this helps.

Pete

> OK...last bit of help for today I hope!
>
[quoted text clipped - 3 lines]
>
> Cheers...

Reply to this Message