Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula NOT displaying 0 (zero)

    On one tab I have a cell that has the value 0 (zero) in it and formatted to display as five zeros (00000). Another sheet has a formula that looks at that cell and returns either a null (blank) character or the value of the cell (IF(Data!AB17="","",Data!AB17)). I would expect the formula to display a 0 since that is what is in the referenced cell... but it returns a blank instead. Options has zero values with a check mark. If I change the formula to read something like... IF(Data!AB16="","",IF(Data!AB16=0,0,Data!AB16)), I still get a blank cell. Any help would be greatly appreciated.

    DennisK

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    It sounds to me like the cell does not have a zero. Can you attach a sample file that demonstrates the problem

    Steve

  3. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 227 Times in 210 Posts
    Dennis,

    Check the format of the cells that contain the formulas. Some formats will change a zero to a blank (eg. #####)

  4. #4
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    77
    Thanks
    9
    Thanked 1 Time in 1 Post
    Try =IF(Data!AB17<>"",Data!AB17,"")

    It worked for me
    Last edited by AlanWade; 2014-03-14 at 13:36.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK... will upload a file called 0test0. If you look on the tab called "Data" at cell D18 you will see that the cell if formatted to display 5 digits... the actual value in the cell is 0 (zero). If you then look at the tab called "LinkedSheet" at cell D14 you will see that the cell is blank. I did change the formula to try to look at the way that Alan Wade suggested but to no success.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You have the spreadsheet to evaluate as Lotus123 did. In Lotus123 a null evaluated as equal to a zero, which makes the IF comparison evaluate as false, so it displays a null.
    in XL2010:
    File-options
    Advanced
    Lotus compatibility settings
    Uncheck: "Transition formula evaluation"

    In earlier versions I believe it was under:
    Tools-Options -Transition(tab)

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    dlkorinek (2014-03-15)

  8. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    THAT'S IT!!! Thanks so much.

Tags for this Thread

Posting Permissions

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