If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
can't get lookup to work when i need it to report from a cell ref
Hi
I am trying to convert the following nested if statement to allow me to be able to choose from more than 8 variables. =IF(C98="A",$F$28,IF(C98="B",$F$37,IF(C98="C",$F$4 6,IF(C98="D",$F$55,IF(C98="E",$F$64,IF(C98="F",$F$ 73,IF(C98="G",$F$82,IF(C98="H",$F$91,"")))))))) this equation does work but i need to be able to also say if cell C98="I" but due to limitations of 7 nested ifs only i can't do this. I have tried using lookup but it doesn't allow me to create an array with the individual cell references. Note C98 is blank until i enter a letter A through H. all the references have calculations eg F37=IF(ISBLANK(B64),"",(D64/E64)) Any help would be very much appreciated Matt |
#2
|
|||
|
|||
can't get lookup to work when i need it to report from a cell ref
Hi,
You can concatonate IF statements using the '&' symbol, and have lots. Not sure how many, but more than 8. Your formula would look something like: =IF(C98="A",$F$28,"")&IF(C98="B",$F$37,"")&IF(C98= "C",$F$46,"")&IF(C98="D",$F$55,"")&IF(C98="E",$F$6 4,"")&IF(C98="F",$F$73,"")&IF(C98="G",$F$82,"")&IF (C98="H",$F$91,"")&IF(C98="I",Whatever,"") Regards - Dave. |
#3
|
|||
|
|||
can't get lookup to work when i need it to report from a cell
Thankyou very much!!!
Extremelly helpful I'll be using this again and again "Dave" wrote: Hi, You can concatonate IF statements using the '&' symbol, and have lots. Not sure how many, but more than 8. Your formula would look something like: =IF(C98="A",$F$28,"")&IF(C98="B",$F$37,"")&IF(C98= "C",$F$46,"")&IF(C98="D",$F$55,"")&IF(C98="E",$F$6 4,"")&IF(C98="F",$F$73,"")&IF(C98="G",$F$82,"")&IF (C98="H",$F$91,"")&IF(C98="I",Whatever,"") Regards - Dave. |
#4
|
|||
|
|||
can't get lookup to work when i need it to report from a cell ref
Hi
Rather than lots of IF's you would be better off with a lookup table If your lookup table was on sheet 2 in columns A and B A F28 B F37 C F46 etc. Then =IF(C98="","",INDIRECT(VLOOKUP(C98,Sheet2!A:B,2,0) )) Alternatively, without a lookup table =IF(C98="","",INDIRECT("F"&28+(CODE(C98)-65)*9)) -- Regards Roger Govier "welshmatt" wrote in message ... Hi I am trying to convert the following nested if statement to allow me to be able to choose from more than 8 variables. =IF(C98="A",$F$28,IF(C98="B",$F$37,IF(C98="C",$F$4 6,IF(C98="D",$F$55,IF(C98="E",$F$64,IF(C98="F",$F$ 73,IF(C98="G",$F$82,IF(C98="H",$F$91,"")))))))) this equation does work but i need to be able to also say if cell C98="I" but due to limitations of 7 nested ifs only i can't do this. I have tried using lookup but it doesn't allow me to create an array with the individual cell references. Note C98 is blank until i enter a letter A through H. all the references have calculations eg F37=IF(ISBLANK(B64),"",(D64/E64)) Any help would be very much appreciated Matt |
#5
|
|||
|
|||
can't get lookup to work when i need it to report from a cell
You're welcome.
Dave. "welshmatt" wrote: Thankyou very much!!! Extremelly helpful I'll be using this again and again "Dave" wrote: Hi, You can concatonate IF statements using the '&' symbol, and have lots. Not sure how many, but more than 8. Your formula would look something like: =IF(C98="A",$F$28,"")&IF(C98="B",$F$37,"")&IF(C98= "C",$F$46,"")&IF(C98="D",$F$55,"")&IF(C98="E",$F$6 4,"")&IF(C98="F",$F$73,"")&IF(C98="G",$F$82,"")&IF (C98="H",$F$91,"")&IF(C98="I",Whatever,"") Regards - Dave. |
#6
|
|||
|
|||
can't get lookup to work when i need it to report from a cell
Hi Dave
I have now entered some data into my tables to ensure that the rest of my equations work and using the method you suggested has caused problems further along the path with other equations. You suggested: cell d78=IF(C78="A",$F$26,"")&IF(C78="B",$F$35,"")&IF(C 78="C",$F$44,"")&IF(C78="D",$F$53,"")&IF(C78="E",$ F$62,"")&IF(C78="F",$F$71,"") etc I know this is only six if statements and i requested help to include more than eight but the other sheets of the workbook contain up to 9 ifs. Cell c78, c81 and c84 are merged cells of 3 rows 1 column. I have used the above equation and amended slightly as follows. cell d79=IF(C78="A",$F$27,"")&IF(C78="B",$F$36,"")&IF(C 78="C",$F$45,"")&IF(C78="D",$F$54,"")&IF(C78="E",$ F$63,"")&IF(C78="F",$F$72,"") cell d80=IF(C78="A",$F$28,"")&IF(C78="B",$F$37,"")&IF(C 78="C",$F$46,"")&IF(C78="D",$F$55,"")&IF(C78="E",$ F$64,"")&IF(C78="F",$F$73,"") Also, cell d81=IF(C81="A",$F$26,"")&IF(C81="B",$F$35,"")&IF(C 81="C",$F$44,"")&IF(C81="D",$F$53,"")&IF(C81="E",$ F$62,"")&IF(C81="F",$F$71,"") cell d82=IF(C81="A",$F$27,"")&IF(C81="B",$F$36,"")&IF(C 81="C",$F$45,"")&IF(C81="D",$F$54,"")&IF(C81="E",$ F$63,"")&IF(C81="F",$F$72,"") cell d83=IF(C81="A",$F$28,"")&IF(C81="B",$F$37,"")&IF(C 81="C",$F$46,"")&IF(C81="D",$F$55,"")&IF(C81="E",$ F$64,"")&IF(C81="F",$F$73,"") cell d84=IF(C84="A",$F$26,"")&IF(C84="B",$F$35,"")&IF(C 84="C",$F$44,"")&IF(C84="D",$F$53,"")&IF(C84="E",$ F$62,"")&IF(C84="F",$F$71,"") cell d85=IF(C84="A",$F$27,"")&IF(C84="B",$F$36,"")&IF(C 84="C",$F$45,"")&IF(C84="D",$F$54,"")&IF(C84="E",$ F$63,"")&IF(C84="F",$F$72,"") cell d86=IF(C84="A",$F$28,"")&IF(C84="B",$F$37,"")&IF(C 84="C",$F$46,"")&IF(C84="D",$F$55,"")&IF(C84="E",$ F$64,"")&IF(C84="F",$F$73,"") I am then using the nine values obtained in column d and applying average, sd and then perform a calculation on the mean and sd. I am now getting a DIV/0 error in the average, sd and calculation cell. Please help Matt "Dave" wrote: You're welcome. Dave. "welshmatt" wrote: Thankyou very much!!! Extremelly helpful I'll be using this again and again "Dave" wrote: Hi, You can concatonate IF statements using the '&' symbol, and have lots. Not sure how many, but more than 8. Your formula would look something like: =IF(C98="A",$F$28,"")&IF(C98="B",$F$37,"")&IF(C98= "C",$F$46,"")&IF(C98="D",$F$55,"")&IF(C98="E",$F$6 4,"")&IF(C98="F",$F$73,"")&IF(C98="G",$F$82,"")&IF (C98="H",$F$91,"")&IF(C98="I",Whatever,"") Regards - Dave. |
#7
|
|||
|
|||
can't get lookup to work when i need it to report from a cell
Hi Welshmatt,
Could you please post any and all formulas, and their cell refs, that return a DIV/0 error. Regards - Dave. |
Thread Tools | |
Display Modes | |
|
|