Return a Value Based on Which Cell Contains 1 in WPS Spreadsheets
Question details
Return a specific number based on which of four cells contains the value 1.

- Product
- Spreadsheet software, including WPS Spreadsheets and Microsoft Excel
- Device & OS
- not provided
- Scenario
- Only one cell in D2:G2 contains 1, and the result depends on its position.
- Observed behavior
- The desired result is 3 for D2 or G2, 7.5 for E2, 6 for F2, and 0 if none contains 1.
Check that D2:G2 are the cells you want to evaluate and that any 1 is entered as a numeric value. Choose an empty cell outside that range for the formula.
Use IF and OR to return the matching result
Use this formula when the result depends on which cell in D2:G2 contains 1.
OR checks D2 and G2 together because both return 3. The nested IF functions then check E2 and F2, returning 0 if none of the conditions is met.
In WPS Spreadsheets or Excel, click an empty cell outside D2:G2, such as H2.
Type =IF(OR(D2=1,G2=1),3,IF(E2=1,7.5,IF(F2=1,6,0))) in the selected cell or formula bar, then press Enter.
Enter 1 in one of D2:G2 and confirm that H2 shows the corresponding result. Clear the 1 to check that the formula returns 0.

Calculate the Result in WPS Spreadsheets
Enter the IF and OR formula in WPS Spreadsheets to return the right value for the cell containing 1.
- 1. Open your worksheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Select the result cell: Click an empty cell outside D2:G2, such as H2.
- 3. Enter the formula: Type =IF(OR(D2=1,G2=1),3,IF(E2=1,7.5,IF(F2=1,6,0))) and press Enter.
- 4. Save the workbook: Use File > Save or Save As to save the worksheet in your chosen format.

Frequently Asked Questions
What does the formula return if none of the four cells contains 1?
It returns 0. The final argument of the nested IF formula supplies that result when no condition matches.
What happens if more than one cell contains 1?
The first matching condition wins: D2 or G2 takes priority, followed by E2, then F2.
Can I use the formula in Excel?
Yes. Enter the same IF and OR formula in an empty Excel cell, such as H2.
How can I use the formula for other rows?
Select the formula cell and drag its fill handle down. The relative references, such as D2 and G2, will adjust for each row.




