Combining IF, AND and OR functions - need some guidance

[nmlynch]

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

[#2 · Accepted solution]

@nmlynch 

Here's the formula:

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

[#3]

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.

[#4]

Thank you @Rodrigo_. This worked perfectly! Upon studying your formula, I suddenly saw my errors. Your answer taught me something about these functions. Thanks!

[#5]

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.

[#6]

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

[#7]

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/

[#8]

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.

[#9]

Hey Nancy, it looks like you’re almost there with your formula! Combining IF, AND, and OR can be tricky, but a slight adjustment should fix those errors. Try this:

=IF(OR(A1="Screening", A1="Baseline"), IF(AND(B1>=40, B1<=80), "Eligible", "Ineligible"), "Not Applicable")

This should help you get the correct results. By the way, when you're done with this, if you need a break, feel free to check out the website (theartpics.com) for some creative inspiration. Sometimes a bit of art is the perfect escape from spreadsheet stress!

[#10]

@Rodrigo_ 

Hello,

I have a formula that 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." How can I adjust this formula to use a different keyword, like "Dutch bros menu"?

Additionally, you can explore more about photo editing tools and resources at https://dutchbrsmenu.com.

Thank you.