Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  4. #4
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 "<" or ">" 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.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    There are several older threads in Excel related to the Access import vbNullString annoyance, of which <!post=this,136831>this<!/post> is probably the most complete discussion, especially see Servando's neat text-to-columns trick.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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").

  9. #9
    New Lounger
    Join Date
    Apr 2001
    Location
    Lititz, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    New Lounger
    Join Date
    Apr 2001
    Location
    Lititz, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    Vicky,
    (Ignoring the fact we're in the wrong forum for this <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) 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?? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    If you use:
    <pre>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;
    </pre>

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

    Microsoft MVP - Excel

  13. #13
    New Lounger
    Join Date
    Apr 2001
    Location
    Lititz, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Ahhh, it makes perfect sense now.

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  14. #14
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •