SOLVED
Home

IFS function with multiple conditions per value

%3CLINGO-SUB%20id%3D%22lingo-sub-895251%22%20slang%3D%22en-US%22%3EIFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895251%22%20slang%3D%22en-US%22%3E%3CP%3EHELP!%20My%20assignment%20is%20to%20simulate%20a%20dice%20roll%20with%201000%20trials%2C%20using%20the%20random%20number%20generator%20%3DRAND()%20and%20assigning%20those%20values%20to%20each%20side%20of%20the%20die.%20To%20make%20things%20short%20and%20sweet%20I%20would've%20just%20used%20%3D%3CSPAN%3ERANDBETWEEN(1%2C6)%20but%20for%20the%20purpose%20of%20the%20assignment%20I%20need%20to%20approach%20it%20using%20IFS%20to%20generate%20the%20outcome%20based%20on%20the%20number%20generated.%20Here's%20the%20statement%20I've%20tried%20using%20with%20no%20avail%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIFS(%20B4%26lt%3B0.16%2C%20%221%22%2C%20B4%26gt%3B0.16%2C%20B4%26lt%3B0.33%2C%20%222%22%2C%20B4%26gt%3B0.33%2C%20B4%26lt%3B0.5%2C%20%223%22%2C%20B4%26gt%3B0.5%2C%20B4%26lt%3B0.66%2C%20%224%22%2C%20B4%26gt%3B0.66%2C%20B4%26lt%3B0.83%2C%20%225%22%2C%20B4%26gt%3B0.83%2C%20%226%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20I'm%20trying%20to%20set%20up%20the%20statement%20so%20that%20the%20outcomes%20(aside%20from%201%20and%206)%20are%20generated%20if%20they%20are%20greater%20than%20one%20value%20but%20less%20than%20the%20other.%20I'm%20sure%20I'm%20doing%20this%20completely%20wrong%20but%20I%20haven't%20found%20a%20way%20to%20fix%20it%20or%20format%20the%20statement%20differently.%20Any%20help%20is%20much%20appreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-895251%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895341%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420736%22%20target%3D%22_blank%22%3E%40Sydwizz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(B4%26lt%3B0.16%2C1%2CIF(AND(%20B4%26gt%3B0.16%2C%20B4%26lt%3B0.33)%2C2%2CIF(AND(B4%26gt%3B0.33%2C%20B4%26lt%3B0.5)%2C3%2CIF(AND(B4%26gt%3B0.5%2C%20B4%26lt%3B0.66)%2C4%2CIF(AND(B4%26gt%3B0.66%2C%20B4%26lt%3B0.83)%2C5%2CIF(B4%26gt%3B0.83%2C6%2C1))))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOr%20you%20may%20also%20try%20this...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCHOOSE(MATCH(B4%2C%7B0%2C0.16%2C0.33%2C0.5%2C0.66%2C0.83%7D)%2C1%2C2%2C3%2C4%2C5%2C6)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895357%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%20The%20first%20result%20worked%20great!%20It%20gave%20me%20exactly%20what%20I%20needed!%20Would%20you%20mind%20explaining%20how%20you%20wrote%20out%20the%20statement%3F%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895462%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420736%22%20target%3D%22_blank%22%3E%40Sydwizz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20was%2C%20if%20you%20have%20only%20one%20logical%20condition%2C%20you%20constructed%20it%20correctly%20in%20the%20formula%20but%20when%20you%20have%20two%20AND%20logical%20conditional%20to%20be%20evaluated%2C%20you%20need%20to%20use%20the%20function%20AND%20like%20below...%3C%2FP%3E%3CPRE%3EAND(%20B4%26gt%3B0.16%2C%20B4%26lt%3B0.33)%3C%2FPRE%3E%3CP%3EThe%20above%20logical%20condition%20will%20be%20considered%20as%20one%20logical%20condition%20and%20if%20both%20are%20evaluated%20as%20True%2C%20then%20only%20the%20control%20passes%20to%20the%20True%20value%20you%20mention%20else%20it%20will%20pass%20to%20the%20False%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20per%20the%20above%20condition%2C%20it%20will%20check%20if%20B4%20is%20greater%20than%200.16%20AND%20B4%20is%20less%20than%200.33%2C%20then%20only%20the%20formula%20will%20return%202%20else%20it%20will%20skip%20to%20the%20False%20value%20which%20is%20another%20IF%20function%20and%20that%20will%20be%20evaluated%20next.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895786%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420736%22%20target%3D%22_blank%22%3E%40Sydwizz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20both%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFS(%20B4%26lt%3B0.16%2C%201%2C%20B4%26lt%3B0.33%2C%202%2C%20B4%26lt%3B0.5%2C%203%2C%20B4%26lt%3B0.66%2C%204%2C%20B4%26lt%3B0.83%2C%205%2C%20TRUE%2C%206)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%20B4%26lt%3B0.16%2C%201%2C%20IF(B4%26lt%3B0.33%2C%202%2C%20IF(B4%26lt%3B0.5%2C%203%2C%20IF(B4%26lt%3B0.66%2C%204%2C%20IF(B4%26lt%3B0.83%2C%205%2C%206)))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eshall%20work%2C%20as%20well%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(B4%2C%7B-1%2C0.16%2C0.33%2C0.5%2C0.66%2C0.83%7D%2C%7B1%2C2%2C3%2C4%2C5%2C6%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895826%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BBoth%20the%20functions%20worked%20great%2C%20thank%20you%20so%20much%20for%20the%20help!%20It%20seems%20I%20was%20also%20incorrect%20in%20the%20way%20I%20placed%20my%20parentheses%20in%20separating%20the%20conditions.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895837%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20multiple%20conditions%20per%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420736%22%20target%3D%22_blank%22%3E%40Sydwizz%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20IFS%20works%20till%20first%20condition%20which%20is%20met%2C%20and%20it's%20not%20necessary%20to%20repeat%20previous%20conditions%20with%20AND%20or%20by%20another%20way.%20Similar%20is%20with%20nested%20IF.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sydwizz
New Contributor

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

6 Replies
Solution

@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)

 

@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!

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

@Sydwizz 

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})

 

@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!

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies