Empty String > 0??? (2000 (9.0.4402 SR-1))

Topic

New Reply

When a cell containing an empty string (or any text, for that matter) is compared (in another cell) to see if it’s greater than some number (say 1, for example), the result is TRUE. Can anyone provide an explanation for this behavior?

In logical expressions comparing cell values to numbers, truely blank cells appear to be treated as if they contain the value zero (which makes sense). Why can’t cells containing text (empty strings or otherwise) be treated the same way?

Why would one want to compare numbers to empty strings?? Normally you wouldn’t, but I’ve moved over the results of a crosstab query from MS Access. The blank cells in the table are not blank (ISBLANK(A1) returns FALSE), but appear to be empty strings (A1=”” returns TRUE). When using array formulas to evaluate the range of cell values (e.g., “how many of the cells have values greater than 50?”), the blank cells are messing up the results of the formulas because of the above behavior. If Access populated these cells with zeros instead of blanks, this particular problem would go away, but Excel’s peculiar (to me, anyway) behavior is still there.

Thanks.

Reply | Quote

Replies

If you type sort order into the answer wizard, you’ll find a help topic dealing with this. Digits are sorted before any text when sorting in ascending order, while empty values are sorted last.

I use the following macro to clear empty and empty-looking strings in the selected area (thanks to the Lounge):

Sub ClearEmpties()
Dim aCell As Range
On Error GoTo ExitHandler
Application.ScreenUpdating = False
For Each aCell In Selection
If Trim$(aCell.Value) = “” Then aCell.ClearContents
Next
ExitHandler:
Application.ScreenUpdating = True
End Sub

Reply | Quote

Thanks Hans (yet again!). That helps explain the behavior. Playing around with this (using Excel to sort rows with various combinations of text, numbers, empty strings, and blank cells is quite informative). It’s important to note that the “ascending” order is: numbers first, then empty strings (=””), then text, then blank cells last. One would think that this ordering would be used when comparing cells with “” logic; i.e., numbers are always less than strings, empty strings are always greater than any number, and strings are ordered by their character-wise ASCII code values. What it doesn’t explain is why blank cells (at the “high” end of the sort order) evaluate to be less than any number (like they ought to be at the “low” end of the sort order), less than text strings, and equal to empty strings.

Nonetheless, using your routine to get rid of those empty strings in my numerical table will help get around this problem. (I may change the code to replace empty or empty-appearing strings with numerical zeros, rather than just clearing the contents, just to further reduce the ambiguity.)

Thanks again.

Reply | Quote

There are several older threads in Excel related to the Access import vbNullString annoyance, of which this is probably the most complete discussion, especially see Servando’s neat text-to-columns trick.

Reply | Quote

Tom:
“… What it doesn’t explain is why blank cells…evaluate to be less than any number …” An empty cell has a numerical value of zero, thus is less than any positive number and greater than any negative number.
Tony.

Reply | Quote

Tony,

That’s exactly what’s so annoying! An empty cell is indeed usually treated as though it has a numerical value of zero (if, for example, it’s referenced in a cell arithmetic formula). But when you include blank cells in amongst cells containing numbers in a range you’re sorting on, the blank cells end up at the bottom of the sorted list (regardless of whether you’re sorting in ascending or decending order), not between the negative and positive numbers (where it would end up if it “has a numerical value of zero”).

Reply | Quote

If you’re interested in another way to solve your original problem…Convert your null values to zeroes while still in Access.

I have a crosstab query where I need to sum up product sales units (at the sku level) by month. This query was then exported to Excel. If a particular product had no sales in a month, I ran into that same null/blank/empty thing that screwed up other formulas.
I changed my crosstab value to
Expr1: IIf(Sum([ProdHist]![QtyShipd]) Is Null,0,Sum([ProdHist]![QtyShipd]))
And life was good.

Reply | Quote

Hi Vicky,
Just for your info, you could use:
sum(nz([ProdHist]![QtyShipd],0))
to achieve the same thing. The NZ function allows you to choose what you want to substitute for any null values that occur.
FWIW.

Reply | Quote

Rory,

I had come across the nz function when I was trying to figure out the sku by month null/blank/empty thing problem. I spent some time playing with it but couldn’t get it to work at all. Didn’t give me errors, but didn’t give me zeroes either.

Since I try not to take shipment history home with me, I just threw a similar query together with the trusty Northwind DB to see if I was crazy…

This works:
TRANSFORM IIf(Sum([Order Details]![Quantity]) Is Null,0,Sum([Order Details]![Quantity])) AS Expr1
SELECT Month([Orders]![ShippedDate]) AS Shipmonth
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Month([Orders]![ShippedDate])
PIVOT [Order Details].ProductID;

This doesn’t:
TRANSFORM Sum((NZ([Order Details]![Quantity],0))) AS Expr1
SELECT Month([Orders]![ShippedDate]) AS Shipmonth
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Month([Orders]![ShippedDate])
PIVOT [Order Details].ProductID;

So am I crazy? Or does this work for everyone but me?

Reply | Quote

Vicky,
(Ignoring the fact we’re in the wrong forum for this ) The difference between the two is what you’re actually testing to see if it’s null. In the iif statement you’re saying “if the sum of these is null, then use 0” so where there is no data and hence no sum, you will still get a 0 in your crosstab. The Nz version says “Sum this field, but where the field is null, use a zero”. Where there is no data at all for the crosstab, no sum takes place, hence the null. (If that makes sense?? )
If you use:

TRANSFORM (NZ(sum([Order Details]![Quantity]),0)) AS Expr1
SELECT Month([Orders]![ShippedDate]) AS Shipmonth
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Month([Orders]![ShippedDate])
PIVOT [Order Details].ProductID;

then you should get the same effect as the iif statement.
Hope that clarifies a little?

Reply | Quote

Ahhh, it makes perfect sense now.

Reply | Quote

Rory & VIcky,

I used the nz(sum(),0) scheme Rory suggested in my Access crosstab query and, as promised, it fills the “blanks” with zeros. Now the Excel annoyance is avoided.

Thanks!!

Reply | Quote

I’m not sure how XL does comparisons for different types. In VB you get errors.

To answer your question, For Arrays include an ISNUMBER(A1) term to make sure that you only include numbers.

Steve

Reply | Quote

Tom:
Excel uses the ASCII values of characters when making comparisons (case ignored). This is illustrated by the sort order 0 1 2 3 4 5 6 7 8 9 (space) ! ” # $ % & ( ) * , . / : ; ? @ [ ] ^ _ ` { | } ~ + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z . Thus, an alphbetical character is ‘greater’ than a numerical value.
To test a range of cells, a conditional function would do the job:
=IF(ISNUMBER(A1),A1>1,A1<1) would return TRUE for any numerical value greater than one and FALSE for any numerical value of 1 or less and text values. Perhaps you could incorporate such a column in your pivot table.
(The ASCII value of a character can be seen using the CODE function.)
Tony.

Reply | Quote