so I am struggling to get my function for Google Sheets to work properly.
For some reason, all of the values after the first two will not populate the cell properly. The idea is to have a cell determined by the two values after the first cell, based on a randomly chosen value. See the reference image attached.
Here is the function below for a clear view and what is trying to be accomplished.
Function
=IF(AND(D26 = "CLEAR", E26 = "CLEAR"), "CLEAR",
IF(AND(D26 = "CLEAR", E26 = "CARRIER"), (CHOOSE(RANDBETWEEN(1,2), "CLEAR", "CARRIER",
IF(AND(D26 = "CARRIER", E26 = "CLEAR"), (CHOOSE(RANDBETWEEN(1,2), "CLEAR", "CARRIER",
IF(AND(D26 = "CLEAR", E26 = "AFFECTED"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED",
IF(AND(D26 = "CARRIER", E26 = "CARRIER"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED",
IF(AND(D26 = "CARRIER", E26 = "AFFECTED"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED",
IF(AND(D26 = "AFFECTED", E26 = "AFFECTED"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED",
IF(AND(D26 = "AFFECTED", E26 = "CLEAR"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED",
IF(AND(D26 = "AFFECTED", E26 = "CARRIER"), (CHOOSE(RANDBETWEEN(1,3), "CLEAR", "CARRIER", "AFFECTED","")))))))))))))))))))))))))
Cells with the function should choose a random value determined by the two cells following it. I’ve attempted to reorder them, change their values, lower-case values, etc..
The first two values work as expected, but then the rest just stop.
>Solution :
Issue seems to be on how brackets are arranged:
=IF(AND(D28 = "CLEAR", E28 = "CLEAR"), "CLEAR",
IF(AND(D28 = "CLEAR", E28 = "CARRIER"), CHOOSE(RANDBETWEEN(1,2), "CLEAR", "CARRIER"),
IF(AND(D28 = "CARRIER", E28 = "CLEAR"), CHOOSE(RANDBETWEEN(1,2), "CLEAR", "CARRIER"),
.......