Forum Discussion
IFS function with multiple conditions per value
HELP! My assignment is to simulate a dice roll with 1000 trials, using the random number generator =RAND() and assigning those values to each side of the die. To make things short and sweet I would've just used =RANDBETWEEN(1,6) but for the purpose of the assignment I need to approach it using IFS to generate the outcome based on the number generated. Here's the statement I've tried using with no avail:
=IFS( B4<0.16, "1", B4>0.16, B4<0.33, "2", B4>0.33, B4<0.5, "3", B4>0.5, B4<0.66, "4", B4>0.66, B4<0.83, "5", B4>0.83, "6")
As you can see I'm trying to set up the statement so that the outcomes (aside from 1 and 6) are generated if they are greater than one value but less than the other. I'm sure I'm doing this completely wrong but I haven't found a way to fix it or format the statement differently. Any help is much appreciated!!
How about this?
=IF(B4<0.16,1,IF(AND( B4>0.16, B4<0.33),2,IF(AND(B4>0.33, B4<0.5),3,IF(AND(B4>0.5, B4<0.66),4,IF(AND(B4>0.66, B4<0.83),5,IF(B4>0.83,6,1))))))Or you may also try this...
=CHOOSE(MATCH(B4,{0,0.16,0.33,0.5,0.66,0.83}),1,2,3,4,5,6)
6 Replies
How about this?
=IF(B4<0.16,1,IF(AND( B4>0.16, B4<0.33),2,IF(AND(B4>0.33, B4<0.5),3,IF(AND(B4>0.5, B4<0.66),4,IF(AND(B4>0.66, B4<0.83),5,IF(B4>0.83,6,1))))))Or you may also try this...
=CHOOSE(MATCH(B4,{0,0.16,0.33,0.5,0.66,0.83}),1,2,3,4,5,6)Subodh_Tiwari_sktneer The first result worked great! It gave me exactly what I needed! Would you mind explaining how you wrote out the statement? Thank you so much!
IMHO, both
=IFS( B4<0.16, 1, B4<0.33, 2, B4<0.5, 3, B4<0.66, 4, B4<0.83, 5, TRUE, 6)and
=IF( B4<0.16, 1, IF(B4<0.33, 2, IF(B4<0.5, 3, IF(B4<0.66, 4, IF(B4<0.83, 5, 6)))))shall work, as well as
=LOOKUP(B4,{-1,0.16,0.33,0.5,0.66,0.83},{1,2,3,4,5,6})
