Hi,
There are two tabs in this example:
First tab (Developments) pulls data from a second tab (Sensitivities) based on what scenario is chosen for a particular project.
At the minute i have a need for 10 nested if statements (as there are 10 scenarios to chose from) but i don't think excel will allow me to do this.
this is what the forumula looks like up to 7 IF statements - which works
=IF($A$43=1,Sensitivites!C29,IF($A$43=2,Sensitivites!C384,IF($A$43=3,Sensitivites!C739,IF($A$43=4,Sensitivites!C1094,IF($A$43=5,Sensitivites!C1449,IF($A$43=6,Sensitivites!C1804,IF($A$43=7,Sensitivites!C2157,0)))))))
I need to go up to 10, I have been reading that there is a vlookup work around but not sure how that works for me?
I also tried using a concatenation - it returns the numbers but when i sum them up in a formula it doesn't recognise that there are any numbers and sums up the line to zero.
=IF($A$32=1,Sensitivites!C18,"")&IF(Developments!$A$32=2,Sensitivites!C373,"")&IF(Developments!$A$32=3,Sensitivites!C728,"")&IF(Developments!$A$32=4,Sensitivites!C1083,"")&IF(Developments!$A$32=5,Sensitivites!C1438,"")&IF($A$32=6,Sensitivites!C1793,"")&IF(Developments!$A$32=7,Sensitivites!C2146,"")&IF(Developments!$A$32=8,Sensitivites!C2500,"")&IF(Developments!$A$32=9,Sensitivites!C2854,"")&IF(Developments!$A$32=10,Sensitivites!C3208,"")
Any help would be much appreciated?
There are two tabs in this example:
First tab (Developments) pulls data from a second tab (Sensitivities) based on what scenario is chosen for a particular project.
At the minute i have a need for 10 nested if statements (as there are 10 scenarios to chose from) but i don't think excel will allow me to do this.
this is what the forumula looks like up to 7 IF statements - which works
=IF($A$43=1,Sensitivites!C29,IF($A$43=2,Sensitivites!C384,IF($A$43=3,Sensitivites!C739,IF($A$43=4,Sensitivites!C1094,IF($A$43=5,Sensitivites!C1449,IF($A$43=6,Sensitivites!C1804,IF($A$43=7,Sensitivites!C2157,0)))))))
I need to go up to 10, I have been reading that there is a vlookup work around but not sure how that works for me?
I also tried using a concatenation - it returns the numbers but when i sum them up in a formula it doesn't recognise that there are any numbers and sums up the line to zero.
=IF($A$32=1,Sensitivites!C18,"")&IF(Developments!$A$32=2,Sensitivites!C373,"")&IF(Developments!$A$32=3,Sensitivites!C728,"")&IF(Developments!$A$32=4,Sensitivites!C1083,"")&IF(Developments!$A$32=5,Sensitivites!C1438,"")&IF($A$32=6,Sensitivites!C1793,"")&IF(Developments!$A$32=7,Sensitivites!C2146,"")&IF(Developments!$A$32=8,Sensitivites!C2500,"")&IF(Developments!$A$32=9,Sensitivites!C2854,"")&IF(Developments!$A$32=10,Sensitivites!C3208,"")
Any help would be much appreciated?