WPS Spreadsheets: Correct an IF Formula for Risk Status by Sales Percentage
Question details
The user needs to correct a nested IF formula that assigns risk statuses based on event type, sales percentage, and elapsed days.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet uses columns E, F, G, and I to evaluate event types A or B, sales percentages, and elapsed days before returning a status.
- Observed behavior
- The formula does not evaluate the conditions correctly because comparisons were grouped incorrectly inside AND expressions.
Confirm that event type is in column E, sales percentage is in column F, and the date or day values are in columns G and I. Select the target result cell and check whether the sales percentages are stored as decimal values such as 0.69 or 69%.
Replace the nested IF formula with separate AND conditions
Use this formula when each risk status depends on a specific combination of event type, sales range, and elapsed days.
Each comparison must be supplied as its own argument to AND. The formula below checks the high-risk rules first, then the low-risk and successful outcomes, and returns Unknown when no rule matches.
Open the worksheet in WPS Spreadsheets and click the cell where the risk status should appear, such as the result cell on row 2.
In the formula bar or directly in the selected cell, enter: =IF(OR(AND(E2="A",F2<0.69,G2-I2>1),AND(E2="B",F2<=0.49,G2-I2>3)),"High Risk",IF(AND(E2="A",F2>=0.69,F2<0.99),"Low Risk",IF(AND(E2="A",F2=1),"Great Job",IF(AND(E2="B",F2>0.5,F2<0.99),"Low Risk",IF(AND(E2="B",F2=1),"Great Job","Unknown"))))).
Press Enter to calculate the result. If the same column layout continues down the sheet, drag the fill handle from the formula cell to copy the formula to the required rows.
Change a test row's event type, sales percentage, or day values and verify that WPS Spreadsheets returns High Risk, Low Risk, Great Job, or Unknown according to the matching condition.
Build and test nested IF risk formulas in WPS Spreadsheets
WPS Spreadsheets lets you enter, inspect, copy, and test nested IF formulas with familiar formula-bar and worksheet controls. Use it to classify risk statuses while preserving compatibility with common Excel workbook formats.
- 1. Open WPS Spreadsheets: Launch WPS Office and choose Spreadsheets, then open the workbook containing the risk-status table.
- 2. Select the output cell: Click the cell used for the status result and review the referenced event, percentage, and day columns.
- 3. Enter the corrected formula: Paste the corrected nested IF formula into the formula bar and press Enter to calculate the status.
- 4. Copy and validate: Drag the fill handle down the result column, then test representative rows for High Risk, Low Risk, Great Job, and Unknown.
Frequently Asked Questions
Why does the original nested IF formula return an incorrect risk status?
The conditions inside AND were not consistently provided as separate logical arguments. Enter each comparison separately, such as AND(F2>=0.69,F2<0.99), so WPS Spreadsheets can evaluate both limits.
Which cells does the corrected formula use?
The formula checks E2 for event type, F2 for sales percentage, and G2-I2 for the elapsed-day calculation. Update these references if your worksheet uses different columns or rows.
What result is returned when no condition matches?
The final nested IF returns "Unknown" when the row does not meet any High Risk, Low Risk, or Great Job condition.
How are percentages represented in this formula?
The formula uses decimal equivalents: 0.69 means 69%, 0.99 means 99%, and 1 means 100%. If your cells contain whole numbers such as 69, change the thresholds to match that format.
