WPS Spreadsheet: Combine IF, AND, and OR to Classify Pain Score Eligibility
Question details
Create a formula that classifies pain score eligibility based on a visit type and an inclusive pain score range.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The visit type is entered in column A and the pain score is entered in column B. Screening and Baseline visits qualify for score evaluation.
- Observed behavior
- The result should be Eligible for scores from 40 through 80 at Screening or Baseline visits, Not Eligible for out-of-range scores at those visits, and Not Applicable for all other visits.
Confirm that the visit names in column A are spelled exactly as Screening or Baseline and that the values in column B are numeric. Enter the formula in the result cell for the first data row, such as C2.
Use a nested IF formula for visit type and pain score eligibility
Use this formula when Screening and Baseline visits need a score-based eligibility result, while every other visit must be marked Not Applicable.
The outer IF first checks whether the visit is Screening or Baseline. Only when that test is true does the inner IF evaluate whether the pain score is between 40 and 80, inclusive.
In WPS Spreadsheet, click the cell where you want the classification to appear, such as C2.
Type or paste =IF(OR(A2="Screening",A2="Baseline"),IF(AND(B2>=40,B2<=80),"Eligible","Not Eligible"),"Not Applicable") and press Enter.
Select the cell containing the formula and drag its fill handle down the column, or copy the cell and paste it into the remaining result cells.
Check that a Screening or Baseline row with a score from 40 to 80 returns Eligible, an out-of-range score returns Not Eligible, and another visit type returns Not Applicable.
Classify Visit Eligibility with Logical Formulas in WPS Spreadsheet
WPS Spreadsheet lets you combine IF, OR, and AND functions to evaluate visit names and pain score thresholds in one formula. Enter the formula once, then fill it down to classify every record consistently.
- 1. Open the worksheet in WPS Spreadsheet: Open your .xlsx or .xls file, then identify the visit type column, pain score column, and output column.
- 2. Select the first output cell: Click the first result cell in the eligibility column, for example C2.
- 3. Insert the nested formula: Enter =IF(OR(A2="Screening",A2="Baseline"),IF(AND(B2>=40,B2<=80),"Eligible","Not Eligible"),"Not Applicable") and press Enter.
- 4. Apply the formula to all records: Drag the fill handle from the formula cell down the eligibility column to update each row reference automatically.
Frequently Asked Questions
Why does the formula use two IF functions?
The first IF determines whether A2 is Screening or Baseline. The second IF runs only for those visits and checks whether B2 is between 40 and 80.
Are pain scores of 40 and 80 considered Eligible?
Yes. The conditions B2>=40 and B2<=80 include both boundary values, so 40 and 80 return Eligible for Screening and Baseline visits.
What happens when the visit type is Follow-up?
Because Follow-up is neither Screening nor Baseline, the outer OR test is false and the formula returns Not Applicable, regardless of the value in B2.
How can I avoid errors caused by extra spaces in visit names?
Clean the visit type entries so they exactly match Screening or Baseline. If needed, use TRIM on the visit cell, such as TRIM(A2), before comparing the text.
Can I use this formula in an Excel workbook opened with WPS Spreadsheet?
Yes. WPS Spreadsheet supports standard IF, AND, and OR functions and can open and save Microsoft Excel .xlsx and .xls workbooks.
