Forum Discussion

Copper Contributor

Oct 05, 2019

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...

excel

Excel on Mac

Formulas and Functions

office 365

Subodh_Tiwari_sktneer

Oct 06, 2019

Sydwizz

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)

Sydwizz

Copper Contributor

Oct 06, 2019

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!

Subodh_Tiwari_sktneer

Silver Contributor

Oct 06, 2019

Sydwizz

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.