# Why does IF say a number stored as text is greater than 100?

Text always ranks above numbers in a comparison. Convert first: =IF(--B2>=$F$1,"Bulk","Standard").

**Excel:** `=IF(--B2>=$F$1,"Bulk","Standard")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | Qty (imported) | Size |  | Bulk from | 100 |
| O-1 | 150 |  |  |  |  |
| O-2 | 85 |  |  |  |  |
| O-3 | 99 |  |  |  |  |
| O-4 | 240 |  |  |  |  |
| O-5 | 40 |  |  |  |  |
| O-6 | 101 |  |  |  |  |

Formula in C2, filled to C7: `=IF(--B2>=$F$1,"Bulk","Standard")`

Result: Bulk; Standard; Standard; Bulk; Standard; Bulk

## How it works

When a comparison mixes text and a number, Excel ranks every text value above every number, so "99" >= 100 is TRUE and the imported quantities all look large. The double minus turns the text into a number first, so the comparison is between numbers.

## Other ways

- `=IF(VALUE(B2)>=$F$1,"Bulk","Standard")`: VALUE does the same conversion.
- `=IF(B2>=$F$1,"Bulk","Standard")`: Without converting, text compares as larger than any number. ("99" and "40" are labeled Bulk.)

## Common mistakes

- Real numbers pass through `--` unchanged, so the formula works on a mixed column.
- Text that isn't a number, like "n/a", makes `--` return #VALUE!.

## Questions

**How do I find the numbers stored as text?**

`=ISTEXT(B2)`, or look for left-aligned numbers with a green triangle.

**Do SUM and MAX have the same problem?**

They skip text numbers instead of ranking them high, so totals come out too low.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-greater-than-with-numbers-stored-as-text