Exceljet

Quick, clean, and to the point

Text is greater than number

Excel formula: Text is greater than number
Summary 

One of Excel's quirks is that text can sometimes be evaluated as greater than a number, even a large number. In the example shown, the formula in D5 is:

=B5>100

As the formula is copied down, you can see that Excel evaluates text values in D9:D11 as greater than the number 100.

Explanation 

Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:

=90>100 // returns FALSE
="A">100 // returns TRUE

The second formula above returns TRUE when you probably expect it to return FALSE. You can see this behavior in the worksheet shown in cells D9:D11. We are comparing each value in column B to 100, and the values in these cells return TRUE because they contain text. Essentially, any text value (even a space " ") will be evaluated as greater than any number.

Counting values greater than

This behavior can affect how other formulas count values that are greater than a specific number. For example, the COUNTIF and COUNTIFS functions don't exhibit this behavior. The formula in G5 returns 1:

=COUNTIF(B5:B11,">100") // returns 1

However a formula that deals with logical expressions directly will show Excel's native behavior. For example, the SUMPRODUCT formula in cell F7 returns 4:

=SUMPRODUCT(--(B5:B11>100)) // returns 4

This is an example of using Boolean logic in a formula.

Ignoring text values

To ignore text values in a formula like this, you can add an additional check with the ISNUMBER function. The SUMPRODUCT formula below has been modified to check that values are (1) larger than 100 and (2) actually numeric:

=SUMPRODUCT(--(B5:B11>100)*ISNUMBER(B5:B11)) // returns 1

This formula returns 1 as a result. 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.