Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    'blank' cells (2002)

    I have an imported file from GIS (has dbf extension and imports easily and correctly) and an excerpted portion is in the attached file. I used the following formula:

    =IF(AND(A9>300,B9="ufas"),1,0)

    For cells with numbers, the formula worked correctly. For cells that appeared blank, it would return a

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'blank' cells (2002)

    The cells in the A column that appear blank actually have spaces in them (probably from the import!)

    Try this : =IF(TRIM(A1)="","",IF(AND(A1>300,B1="ufas"),1,0)) to see if it does what you want!
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'blank' cells (2002)

    Thanks, it solves the problem.

    Do you know why Excel treats the non-numeric "blank" cell such that it produces a true condition for the if statement?

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

    Re: 'blank' cells (2002)

    If you look up 'sort order' in the answer wizard, you'll find that all alphanumeric strings are sorted after all numbers, i.e. text strings are considered to be "higher" than numbers.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'blank' cells (2002)

    makes sense, once you know about it <g>
    also this variant seems to work correctly::

    =IF(ISNUMBER(W2),IF(AND(W2<100,Y2="ufas"),1,0),0)

Posting Permissions

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