SOLVED

Combining IF, AND and OR functions - need some guidance

Brass Contributor

Hi - trying to combine some functions to produce a result. Having no luck. Attached is a spreadsheet.

 

I need to determine if a Pain Score at either a Screening Visit or a Baseline Visit is an eligible score. The score must be between 40 and 80 and must have occurred at the Screening Visit or the Baseline Visit. It's either Eligible (40<=PAIN SCORE<=80) or it's Ineligible.

 

However, there are other visits too where these Pain Scores are obtained. Those should be Not Applicable because they have nothing to do with eligibility. 

 

I feel like I'm entering the right formula, but I get errors - either too many arguments or missing parenthesis.

 

Here is the formula I'm trying (A = Visit Name and B = Pain Score):  '=IF(OR(A1="Screening",A1="Baseline"),AND(B1>39,B1<81),"Eligible",if(and(B1<40,B1>80),"Not eligible","Not applicable"))

 

Any guidance for me?

 

Many thanks!

 

Nancy

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@nmlynch 

Here's the formula:

=IF(OR(B26="Screening", B26="Baseline"), IF(AND(C26>=40, C26<=80), "Eligible", "Not Eligible"), "Not Applicable")

Rr__0-1705628259555.png

 

 

 

Certainly! Combining the IF, AND, and OR functions in Excel allows you to create more complex logical conditions. Here's a general guide on how you mig...

Syntax of IF, AND, and OR:

IF Function:

The IF function checks whether a condition is met and returns one value if true and another if false.

 

excelCopy code
=IF(logical_test, value_if_true, value_if_false)

 

 

AND Function:

The AND function checks whether all arguments are true and returns TRUE if they are, and FALSE if any are false.

 

excelCopy code
=AND(logical1, [logical2], ...)

 

 

OR Function:

The OR function checks whether any of the arguments are true and returns TRUE if at least one is, and FALSE if all are false.

 

excelCopy code
=OR(logical1, [logical2], ...)

 

 

Combining IF, AND, and OR:

Example 1 - Combining IF and AND:

 

excelCopy code
=IF(AND(A1>10, B1<20), "Condition met", "Condition not met")

 

 

This formula checks if both A1 is greater than 10 and B1 is less than 20. If both conditions are true, it returns "Condition met"; otherwise, it returns "Condition not met".

Example 2 - Combining IF and OR:

 

excelCopy code
=IF(OR(A1="Apple", A1="Banana"), "Fruit", "Not a Fruit")

 

 

This formula checks if A1 is either "Apple" or "Banana". If either condition is true, it returns "Fruit"; otherwise, it returns "Not a Fruit".

Example 3 - Combining IF, AND, and OR:

 

excelCopy code
=IF(AND(OR(A1="Red", A1="Blue"), B1>10), "Valid", "Not Valid")
 

This formula checks if A1 is either "Red" or "Blue" and if B1 is greater than 10. If both conditions are true, it returns "Valid"; otherwise, it returns "Not Valid".

Feel free to adapt these examples to your specific requirements, and remember to adjust cell references and conditions accordingly. The key is to nest the functions appropriately to create the desired logical structure.

Thank you @Rr_. This worked perfectly! Upon studying your formula, I suddenly saw my errors. Your answer taught me something about these functions. Thanks!
Thank you, @michalja44d - I very much appreciate your detailed reply!! This explains it in clear terms - I wasn't able to find this while searching the net the other day.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@nmlynch 

Here's the formula:

=IF(OR(B26="Screening", B26="Baseline"), IF(AND(C26>=40, C26<=80), "Eligible", "Not Eligible"), "Not Applicable")

Rr__0-1705628259555.png

 

 

View solution in original post