+ Reply to Thread
Results 1 to 3 of 3

IF problem - inconsistent results

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    bedford, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    IF problem - inconsistent results

    EXCEL 2003
    Competition and competitor data is a data array A2..L50.

    Col A is a sequential number list (fully populated)
    Col B contains either character "S" or is blank (90% blanks)
    Col C is a names list (fully populated),
    Cols D..L contain either character "X" or a blank (65% populated) -these are the 9 competitions. "X"'s indicate the entrants

    I need NINE filtered lists of entrants with 3 elements, one for each competition.

    First list is in Cols M..O, but it's printing Zero's, not blanks, in Col N when the value is not "S". Same problem will undoubtedly exist on the further 8 lists!
    Formulae -
    Cell M2 =IF(D2="X",A2,"") correctly returns either the number or is blank
    Cell N2 =IF(D2="X",B2,"") returns either the "S" (correctly) or a numeric "0"
    Cell O2 =IF(D2="X",C2,"") and correctly returns either the name or a blank.

    I've tried conditionally formatting cell N2 to print the 0 in white, with no success.

    Why is Cell N2 returning ZERO's? How can I get it to return a blank?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF problem - inconsistent results

    Quote Originally Posted by silvabod View Post
    Why is Cell N2 returning ZERO's? How can I get it to return a blank?
    That's just the way it is, not sure why.
    If a Link formula refers to a blank cell, it returns it as 0.

    There are many ways to get around it, in your case since the expected return is a Text string "S"
    Then you can just use
    =T(IF(D2="X",B2,""))

  3. #3
    Registered User
    Join Date
    05-13-2013
    Location
    bedford, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: IF problem - inconsistent results

    Thanks, that works for me!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1