Fix Wrong IF Comparison Results for Numbers Stored as Text in Excel
Question details
The user needs to understand why an IF formula comparison may produce an unexpected result and how to ensure compared cell values are treated as numbers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using an IF formula, such as =IF(G2>60,1,0), to return one value when a cell exceeds a numeric threshold and another value when it does not.
- Observed behavior
- The direct formula =IF(10>60,1,0) correctly returns 0, but comparisons that reference cells can be incorrect when one or more values are stored as text.
Select the cells used in the comparison and check whether their values are left-aligned or display a green error indicator, which can indicate numbers stored as text. Confirm that the threshold in the IF formula is intended to be a number rather than quoted text.
Convert text-formatted cell values to numbers before comparing them
Use this method when an IF formula references cells containing numeric-looking values that were imported, pasted, or stored as text.
Excel compares numeric values differently from text values. The formula =IF(10>60,1,0) returns 0 as expected, but a referenced value may need conversion before a reliable numeric comparison can be made.
In an empty cell, enter =ISNUMBER(G2). If the result is FALSE, the value in G2 is text rather than a numeric value.
Edit the IF formula in the formula bar and use a double unary conversion, such as =IF(--G2>60,1,0). The -- converts a numeric text value to a number.
Enter =IF(VALUE(G2)>60,1,0) when G2 contains a text number. For a number extracted from the first two characters of E2, use =IF(--LEFT(E2,2)>60,1,0).
Select the affected cells, click the warning icon if it appears, and choose Convert to Number. Then use the regular formula, such as =IF(G2>60,1,0).
Use quotation marks only when a text comparison is intended
Use this method when the values should be compared as text labels or text codes instead of numeric quantities.
Check and Correct IF Comparisons in WPS Spreadsheets
WPS Spreadsheets lets you test whether cells contain numbers, convert text-formatted values, and update IF formulas with familiar spreadsheet functions. Open the workbook, correct the source data or formula, and recalculate the result without changing the workbook layout.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the IF formula.
- 2. Check the source cell type: Select the referenced cell, such as G2, and enter =ISNUMBER(G2) in an empty cell to verify whether the value is numeric.
- 3. Update the IF formula: Select the formula cell, click the formula bar, and change the condition to =IF(--G2>60,1,0) or =IF(VALUE(G2)>60,1,0) when conversion is needed.
- 4. Confirm the calculation result: Press Enter and verify that =IF(10>60,1,0) displays 0 and that the cell-based comparison now returns the expected result.
Frequently Asked Questions
What does =IF(10>60,1,0) return in Excel?
It returns 0 because 10 is less than 60, so the logical test 10>60 is FALSE.
How can I tell whether an Excel cell contains a number stored as text?
Enter =ISNUMBER(G2) in another cell. FALSE means G2 is not a numeric value. You may also see a green triangle or find that the value is left-aligned.
Which formula converts a text number during an IF comparison?
Use =IF(--G2>60,1,0) or =IF(VALUE(G2)>60,1,0). Both convert a valid numeric text value before comparing it with 60.
Should I write 60 or "60" in an IF formula?
Use 60 for a numeric comparison. Use "60" only when you specifically need to compare text values, because quotation marks make the value text.
How do I convert an Excel column of text numbers to real numbers?
Select the cells, click the displayed error indicator, and choose Convert to Number. Alternatively, use a helper formula such as =VALUE(G2), then copy and paste the results as values.
