Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Simple version: There may or may not be a value in cell A50. Cell B50 contains the formula =IF(A50="", "", A50). The desired result is that B50 stays blank if A50 is blank - otherwise it displays the same result. It works except when A50 equals zero. Then B50 displays as blank. (This is simplified from a pre-existing worksheet in a pre-existing workbook in Excel XP.)

    The "Zero values" option is checked. (Besides, A50 is displaying zero properly.) There's no conditional formatting that I can find. And a test (in C50) confirms B50 contains the empty string ""! So why does =IF(A50="", "", A50) evaluate to "" when A50 = 0?

    As an added puzzle, if I try this on a newly created worksheet in the same workbook, it works as expected. That is, if B1=IF(A1="", "", A1), then B1 displays 0 if A1=0.

    Any suggestions?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Has a custom format been applied?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's one I forgot to mention. I tried both Fixed (1 decimal place) and General and neither changed anything.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by B. Julien View Post
    ..... if I try this on a newly created worksheet in the same workbook, it works as expected. That is, if B1=IF(A1="", "", A1), then B1 displays 0 if A1=0.
    If you copy the data down to Row 50, do you get the desired result? If so, how old is the workbook & does the worksheet require rebuilding? HTH
    Gre

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, copying down to Row 50 - in the new worksheet but in the original workbook - still does give the desired result.

    I'm not sure how old the workbook is - several years old at least and used in Excel 2000 when I first got into this office.

    Worksheet rebuilding is not a concept I'm familiar with. Is it basically redoing the worksheet in a brand new workbook? Or something else?

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I ended up basically copying everything to a new workbook. Had to weed out some external references the copying introduced, but it still didn't take very long. And now the formulas work as expected. So some cruft that had accumulated from years and versions past? (It's possible this thing is descended from a Lotus 1-2-3 version I did over 15 years ago in another office.) Don't know for sure and I'm not going to worry about it anymore - although if anybody still has a suggestion, I'm still a little curious.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What you've experienced can happen - in some cases - with a workbook that is onlya few years old. Hopefujlly, you were able to remove the external references copying introduced by using the method described here.
    Gre

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yep, that method you linked to is more or less what I did. There was only one link that the Edit -> Links process didn't work for and that turned out to be buried in a chart.

    So this was all educational - I wasn't aware you could suffer this sort of slow corruption in a spreadsheet. Had I thought about it, I would have assumed they either work as always or don't work at all. Something to be aware of in the future.

Posting Permissions

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