Solved

# Nested "If" statements don't always work...

Posted on 2013-02-02

I have four columns. In column BD, cells 9-16 have the following data:

"W1", "W1", "W1", "W2", "W2", "W3", "W3", "W4"

In column BE, cells 9-16 have the following data:

"NR", "R1", "R2", "R3", "R4", "R5", "R6", "R7"

You'll notice that W1 corresponds to NR, R1,& R2, W2 corresponds to R3 & R4, W3 corresponds to R5 & R6, and W4 corresponds to R7.

In column AF, cells 9-30 have the following function assigned:

=INDEX($BD9:$BD16,RANDBETWEEN(1,COUNTA($BD9:$BD16)),1)

Therefore, each of the cells in AF9:AF30 will read any of "W1", "W2", "W3", or "W4" randomly.

Here is my problem: In AV9:AV30 are supposed to analyze AF9:AF30. If, for example, the cell AF9 reads as "W1", then I want it to randomly fill AV9 with any of "NR", "R1", or "R2" (BE9:BE11).

I am currently just trying to get the formula to work, so in cell AV9 my formula currently reads as the following nested "IF" statement:

=IF(AF9="W1",INDEX($BE9:$BE11,RANDBETWEEN(1,COUNTA($BE9:$BE11)),1),+IF(AF9="W2",INDEX($BE12:$BE13,RANDBETWEEN(1,COUNTA($BE12:$BE13)),1))+IF(AF9="W3",INDEX($BE14:$BE15,RANDBETWEEN(1,COUNTA($BE14:$BE15)),1))+IF(AF9="W4","R7","NA"))))

It functions only some of the time, and only at specific times. If AV9="W1", then AV9="NR" or "R1" or "R2" randomly. But if AF9="W2" or "W3" or "W4", it reads as an error. I notice the first part of my nested IF statement refers to W1, so I can see the function only looks at the first part. What must be done to correct this?

I am only focusing on the functions of cells AF9 and AV9 right now, I will modify the final function later.