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

8 Replies
best response confirmed by HansVogelaar (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 @Rodrigo_. 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.

This formula assesses the eligibility of a pain score based on the visit name and pain score values. If the visit is "Screening" or "Baseline" and the pain score falls between 40 and 80, it's considered "Eligible"; otherwise, it's labeled "Not eligible". If the visit is neither "Screening" nor "Baseline", it's designated as "Not applicable". Adjusting the formula for a different keyword like "PicsArt Mod APK" would require altering the criteria to fit the context of that keyword.

This formula evaluates pain score eligibility based on visit name and score values. If the visit is "Screening" or "Baseline" and the pain score falls between 40 and 80, it's deemed "Eligible"; otherwise, it's marked "Not eligible". For visits other than "Screening" or "Baseline", it's labeled "Not applicable". Adjusting the formula for a different keyword, like "PicsArt Mod APK", would require modifying the criteria to suit that context. You can explore more about photo editing tools and resources at [thepicsapp.com](https://www.thepicsapp.com/).

This formula evaluates pain score eligibility based on visit name and score values. If the visit is "Screening" or "Baseline" and the pain score falls between 40 and 80, it's deemed "Eligible"; otherwise, it's marked "Not eligible". For visits other than "Screening" or "Baseline", it's labeled "Not applicable". Adjusting the formula for a different keyword, like "Blooket Join", would require modifying the criteria to suit that context. You can explore more Blooket Join" at https://blooketjoining.com/

This algorithm evaluates the suitability of a pain score by examining both the visit name and the pain score values. If the visit is labeled as "Screening" or "Baseline" and the pain score falls within the range of 40 to 80, it's deemed "Eligible"; otherwise, it's categorized as "Not eligible". In cases where the visit is neither "Screening" nor "Baseline", it's marked as "Not applicable". Modifying the formula for a different keyword such as "PicsArt APK" necessitates adjusting the criteria to align with the specifics of that keyword's context.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (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