WPS Spreadsheet: Return YES, CHECK, or NO from Risk Scores
Question details
The user needs to classify numeric risk scores as YES, CHECK, or NO and apply a matching cell color.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A risk score is entered in a worksheet, and the user wants a text classification with visual color coding.
- Observed behavior
- The worksheet needs to return YES for scores 1–4, CHECK for scores 5–7, NO for scores 8–20, and an out-of-range message for other values, with green, blue, and red formatting.
Confirm that the risk score is in column A and that the result will be entered in column B. Decide whether scores outside 1–20 should display “Out of range” or another message before creating the formula.
Use XLOOKUP to classify the risk score and add matching colors
Use this approach when the score bands are fixed and you want a compact formula that also handles values outside the valid range.
The approximate-match XLOOKUP uses the starting value of each band: 1, 5, 8, and 21. This returns YES for 1–4, CHECK for 5–7, NO for 8–20, and an out-of-range message for other values.
In cell B2, enter =XLOOKUP(A2,{1,5,8,21},{"YES","CHECK","NO","Out of range"},"Out of range",-1), then press Enter. Replace A2 with the cell containing the risk score if needed.
Select B2 and drag the fill handle down, or copy and paste the formula into the remaining rows containing risk scores.
Select the result range, such as B2:B100, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains in WPS Spreadsheet.
Create a rule for YES with a green fill, a rule for CHECK with a blue fill, and a rule for NO with a red fill. Apply the rules to the same result range.
Use IFS when you prefer explicit score conditions
Use this alternative when you want each score range to be visible directly in the formula or your WPS Spreadsheet version does not support XLOOKUP.
Classify and color-code risk scores in WPS Spreadsheet
WPS Spreadsheet can evaluate score ranges with XLOOKUP or IFS, fill the result down a table, and apply conditional formatting so YES, CHECK, and NO are immediately recognizable.
- 1. Create the result column: Open WPS Spreadsheet, place the risk score in column A, and select B2 for the classification result.
- 2. Add the formula: Enter =XLOOKUP(A2,{1,5,8,21},{"YES","CHECK","NO","Out of range"},"Out of range",-1) in B2, or use the IFS alternative for explicit conditions.
- 3. Fill the result range: Copy B2 down alongside all score entries so every risk score receives a classification.
- 4. Add conditional formatting: Select the result range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to assign green to YES, blue to CHECK, and red to NO.
Frequently Asked Questions
What formula returns YES, CHECK, and NO for the specified score ranges?
If the score is in A2, enter =XLOOKUP(A2,{1,5,8,21},{"YES","CHECK","NO","Out of range"},"Out of range",-1) in B2. It maps 1–4 to YES, 5–7 to CHECK, and 8–20 to NO.
How do I color YES, CHECK, and NO in WPS Spreadsheet?
Select the returned text range, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and create three rules: YES with green formatting, CHECK with blue formatting, and NO with red formatting.
What happens when the risk score is outside 1 to 20?
The XLOOKUP formula returns Out of range for values below 1 or above 20. The IFS version returns the same message through its final TRUE condition.
Can I use IF instead of XLOOKUP in WPS Spreadsheet?
Yes. For example, use =IF(AND(A2>=1,A2<=4),"YES",IF(AND(A2>=5,A2<=7),"CHECK",IF(AND(A2>=8,A2<=20),"NO","Out of range"))) in B2.
