Nested IF, IFS, AND, OR Functions

Copper Contributor

Nested IF, IFS, AND, OR Functions

Hello,

I am attempting to set up an excel sheet to give one of three answers (TRUE, FALSE, or NA) based on different combinations of two criteria (for simplicity, "a" and "b") which have different values. This is taking into consideration gender ("a") and a weight ("b"), but I need the function to only result in TRUE or FALSE if male, and if female to only result in NA. I have gotten the individual functions to work, but I can't get the nested function to work. I believe that I have to use the IFS function because if the combination isn't TRUE, it isn't necessarily FALSE because it is NA if female (basically it doesn't apply if female).

Criteria:

"a" = gender of M or F (must be M to result in TRUE or FALSE, but if F then NA)

"b" = weight (which needs to be between 60-100 kg to be TRUE)

Results:

TRUE: there is only one combination that will result in TRUE: Male weighing 60-100 kg

IFS(AND(a="M",b>=60,b<=100),"TRUE"

*if this combination does not result in TRUE, it isn't necessarily FALSE, but this is subject to gender and therefore could result in NA if a="F"

NA: there is only one combination that will result in NA: Female

IFS(a="F","NA")

*if a<>"F", it isn't necessarily TRUE or FALSE because it is then subject to weight

FALSE: all other combinations where "a"= M but weight is not 60-100 kg

Can you help figure out how to input a combined formula to either result in TRUE, FALSE, or NA appropriately if it is even possible? I think I am running into the issue of overlapping/conflicting answers if the IF/IFS combination does not result in TRUE (if that makes any sense).

Thank you so much for your time and effort to help solve this issue.

Best,

Hollenbacher

Re: Nested IF, IFS, AND, OR Functions

=IFS(A1="F","NA",AND(A1="M",B1>=60,B1<=100),"TRUE",AND(A1="M",OR(B1<60,B1>100)),"FALSE")

You can use this formula.