IF in cells

C

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

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

Craig Coope

Don Guillett said:
How about telling us what you are trying to do
Click to expand...

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

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
 
C

Craig Coope

Ragdyer said:
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)
Click to expand...

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

Dave Peterson

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

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

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

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?
 
C

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

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