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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Steve

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    Dennis,

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

  5. #4
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    76
    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.

  6. #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

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 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

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

    dlkorinek (2014-03-15)

  9. #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
  •