Oct 05 2019 04:17 PM
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!!
Oct 05 2019 08:40 PM - edited Oct 05 2019 08:48 PM
SolutionHow 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)
Oct 05 2019 11:02 PM
@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!
Oct 06 2019 01:35 AM
You're welcome! Glad it worked as desired.
The issue was, if you have only one logical condition, you constructed it correctly in the formula but when you have two AND logical conditional to be evaluated, you need to use the function AND like below...
AND( B4>0.16, B4<0.33)
The above logical condition will be considered as one logical condition and if both are evaluated as True, then only the control passes to the True value you mention else it will pass to the False value.
So as per the above condition, it will check if B4 is greater than 0.16 AND B4 is less than 0.33, then only the formula will return 2 else it will skip to the False value which is another IF function and that will be evaluated next.
Hope that helps.
Oct 06 2019 11:14 AM
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})
Oct 06 2019 11:36 AM
@Sergei Baklan 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!
Oct 06 2019 11:46 AM
@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.
Oct 05 2019 08:40 PM - edited Oct 05 2019 08:48 PM
SolutionHow 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)