IF in cells

[Craig Coope]

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...

 

[Nick Hodge]

Craig

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dave Peterson]

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.)

 

[Don Guillett]

How about telling us what you are trying to do

 

[Craig Coope]

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....

 

[Don Guillett]

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

 

[Craig Coope]

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)

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

 

[Dave Peterson]

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

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

 

[Don Guillett]

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

 

[Ragdyer]

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)

 

[Ragdyer]

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?

 

[Craig Coope]

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...

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[Tom Ogilvy]

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"),"")