MOre than 8 argurement

[wan]

Hi all,
I have done if function with 8 argurement/condition with no problem.Is
there
anyway for me to have more than 8 condition using if function?

here is the example,
=IF($S$1=1,B3,IF($S$1=2,C3,IF($S$1=3,D3,IF($S$1=4,E3,IF($S$1=5,F3,IF($S$1=6,G3,IF($S$1=7,H3,IF($S$1=8,I3,0))))))))

cheers

Wan

 

[Dav]

No is the short answer, you can combine 2 cells to overcome the proble
but what are you wanting to do anyway

It seems that a lookup or a simple formula could work instead and yo
would not need the multiple ifs. You may need to put the formula insid
an if statement to stop it going out of range or if whole numbers do no
appear

but basically you are saying

=OFFSET(A3,0,S1)

or

=if(and(s1>=1,s1<=8,trunc(s1)=s1),OFFSET(A3,0,S1),0)

excludes none whole numbers and allows numbers between 1 and 8

Regards

Da

 

[Guest]

=IF($S$1=1,B3,IF($S$1=2,C3,IF($S$1=3,D3,IF($S$1=4,E3,

IF($S$1=5,F3,IF($S$1=6,G3,IF($S$1=7,H3,IF($S$1=8,I3,0))))))))

Perhaps a better, simpler alternative to achieve the above ..
(wo being limited by the max nested IFs)
is to use something like this in say, T1:
=IF(S1="","",INDEX(B3:IV3,,S1))

---

 

[Bob Phillips]

Actually you can. I am not advocating this approach in this particular
instance, but just to show how

=IF($S$1=1,B3,"")&IF($S$1=2,C3,"")&IF($S$1=3,D3,"")&IF($S$1=4,E3,"")&IF($S$1
=5,F3,"")&IF($S$1=6,G3,"")&IF($S$1=7,H3,"")&IF($S$1=8,I3,"")&IF($S$1=9,J3,""
)&IF($S$1=10,K3,"")&IF($S$1=11,L3,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[wan]

Thanks for quick repondes,

Cheers,
Wan

 

[wan]

No is the short answer, you can combine 2 cells to overcome the problem
but what are you wanting to do anyway

It seems that a lookup or a simple formula could work instead and you
would not need the multiple ifs. You may need to put the formula inside
an if statement to stop it going out of range or if whole numbers do not
appear

but basically you are saying

=OFFSET(A3,0,S1)

or

=if(and(s1>=1,s1<=8,trunc(s1)=s1),OFFSET(A3,0,S1),0)

excludes none whole numbers and allows numbers between 1 and 8

Regards

Dav

Thanks Dav,
I decided to use offset and it works well.Basically I got drop down
menu format which link to cell s1.

Cheers