| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOre than 8 argurement
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 -- wan ------------------------------------------------------------------------ wan's Profile: http://www.excelforum.com/member.php...o&userid=37523 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MOre than 8 argurement
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MOre than 8 argurement
=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,I 3,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)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "wan" wrote: 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 -- wan ------------------------------------------------------------------------ wan's Profile: http://www.excelforum.com/member.php...o&userid=37523 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MOre than 8 argurement
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) "Dav" wrote in message ... 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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MOre than 8 argurement
Thanks for quick repondes, Cheers, Wan -- wan ------------------------------------------------------------------------ wan's Profile: http://www.excelforum.com/member.php...o&userid=37523 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MOre than 8 argurement
Dav Wrote: 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 -- wan ------------------------------------------------------------------------ wan's Profile: http://www.excelforum.com/member.php...o&userid=37523 View this thread: http://www.excelforum.com/showthread...hreadid=571675 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
