How to Use an Excel IF Formula in WPS Office Spreadsheets to Return 1 When a Value Exceeds 90
Question details
The user needs a WPS Office Spreadsheets formula that checks whether a referenced cell value is greater than 90 and returns 1 when the condition is true.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a worksheet formula to flag individual values above 90 or count all qualifying values in a range.
- Observed behavior
- The formula should return 1 for each cell above 90, or return the total count of cells above 90 when using COUNTIF.
Open the WPS Office spreadsheet containing the values you want to evaluate and confirm the cells you will reference, such as E7:E22, are formatted as numbers.
Use an IF Formula in a Helper Column to Return 1 for Values Greater Than 90
Use this when you need a row-by-row indicator that shows 1 beside each qualifying value.
WPS Office Spreadsheets accepts Excel formula syntax, so you can use the same IF function without switching applications.
Click cell G7 or the first cell in the column where you want the row-by-row result.
Type =IF(E7>90,1,"") and press Enter. The formula returns 1 when E7 is greater than 90 and leaves the cell blank otherwise.
Select G7 and drag the fill handle down through the remaining data rows to apply the formula to E8, E9, and so on.
Use COUNTIF to Count All Values Greater Than 90 in a Range
Use this when you do not need a helper column and only want the total number of cells above 90.
Create Excel IF and COUNTIF Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same Excel formula syntax, so you can add an IF formula that returns 1 for values above 90, or use COUNTIF to count them, without leaving the free WPS Office suite.
- 1. Open your workbook in WPS Office: Launch WPS Office, open the spreadsheet file containing the values you want to evaluate.
- 2. Enter the IF formula: Select G7 and type =IF(E7>90,1,""), then press Enter.
- 3. Apply the formula or use COUNTIF: Drag the fill handle down to copy the IF formula, or enter =COUNTIF(E7:E22,">90") in an empty cell for a total count.
- 4. Save the workbook: Click Save or press Ctrl+S to save the file in its original Excel-compatible format.
Frequently Asked Questions
Can I use this IF formula in WPS Office Spreadsheets?
Yes. WPS Office supports Excel formulas, so =IF(E7>90,1,"") works the same way in WPS Office Spreadsheets.
How do I return a blank instead of 0 when the value is not greater than 90?
Use "" as the value_if_false argument, as in =IF(E7>90,1,""). If you use =IF(E7>90,1,0), it returns 0 instead.
What formula counts all values greater than 90 in a range?
Use =COUNTIF(E7:E22,">90"). Adjust E7:E22 to match your actual data range.
Can I make the formula return text instead of 1?
Yes. Replace 1 with the text in quotes, for example =IF(E7>90,"Above","").
Why does my formula return 0 or FALSE?
Check that the referenced cell contains a number and that the comparison operator is entered as >90 inside the IF test. Text values or empty cells are not greater than 90.
