Text Is Greater Than Number

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.

Explanation

The TRANSPOSE function automatically transposes values in a horizontal orientation to vertical orientation and vice versa.

However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF function that checks first to see if a cell is blank or not. When a cell is blank, the IF function supplied an empty string (”") to transpose. If not, IF supplies the value normally.

Without IF, the array going into TRANSPOSE looks like this:

{"Item","apples","pears","limes",0;"Qty",14,10,4,0}

After IF, it looks like this:

{"Item","apples","pears","limes","";"Qty",14,10,4,""}