Forum Discussion
Solved
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'v...
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})
SergeiBaklan Both the functions worked great, thank you so much for the help! It seems I was also incorrect in the way I placed my parentheses in separating the conditions. Thank you!
Sydwizz , you are welcome. IFS works till first condition which is met, and it's not necessary to repeat previous conditions with AND or by another way. Similar is with nested IF.