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.