Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Question Cell does not display formula results

    I am having a problem with displaying a formula in a cell. I can type the formula into the cell and it goes in okay, but when I hit <enter>, nothing displays in the cell. I have tried the same formula in four different cells, but it still doesn't display. This is something I have never run into before and I am at a loss to try and explain what is happening. Any insight that anyone would care to provide will be greatly appreciated. Thanks.

    Ron M

  2. 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
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ron, is it possible that you have set the text color to white somehow? Therefore what will be displayed is the same color as the background of the cell. That is my one and only shot.
    Bret

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Ron,

    Another possibility is that the formula calculates to zero and you have the options set to not display zeroes.

    Could you post a sample sheet with the formula in it?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gasman View Post
    Ron, is it possible that you have set the text color to white somehow? Therefore what will be displayed is the same color as the background of the cell. That is my one and only shot.
    Bret
    I checked, and I set the text color to "Automatic" and it will still not display.

    Quote Originally Posted by RetiredGeek View Post
    Ron,

    Another possibility is that the formula calculates to zero and you have the options set to not display zeroes.

    Could you post a sample sheet with the formula in it?
    Formula is not supposed to calculate to zero and to my knowledge it does not, or should not. As well, I am not sure I would know how to set options to not display zeros. Kind of hard to post a "sample" sheet with the formula in it. Let me try a couple of more things first.

    Thanks for the suggestions guys. I even went and picked a random cell and did some similar, but unrelated, calculations in the cell and the number displayed properly. I then deleted the contents of that cell and put the formula I am having trouble with in the cell and the result was the same - no number and no display. Go figure. It is a puzzle for me.

    I have two sheets open in the spreadsheet and I will trying doing the same calculation on the other sheet to see if that is of any help.

    Ron M
    Last edited by Ron M; 2012-07-18 at 19:46.

  6. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I discovered that if I simply put the numbers themselves into the calculation, then the results show up and are correct. It seems to be something about the formulas that it isn't liking. I also suspect is has something to do with "Names" as Excel seems to automatically assign names to columns that have a "heading". Not sure if this bit of information gives anyone any ideas, but I hope it helps.

    Ron M

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    Perhaps you could tell us what the formula is? Does it return "" for any part of it?
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Perhaps you could tell us what the formula is? Does it return "" for any part of it?
    The formula is a simple division and multiplication like =(cell1/cell2)*100. It simply calculates a yield on an investment portfolio of mine that I manage - nothing really special. It returns a blank cell. When I "click" on the cell, the formula shows up in the formula bar at the top, but nothing, not even "" shows up. The fact that I seem to get the same results, regardless of the cell that I put the formula in, is also puzzling and suggests to me that it is something that is originating with the numbers and/or cell references that are being used in the formula. Any other thoughts - anyone?

    Ron M

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    Can you tell us:1. What the cell's number format is?2. If there is any conditional formatting applied to the cell?3. What you see if you type 0 in the cell?4. What you see if you type =1/0 in the cell?
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Rory, FYI - 1) number format is "number", 2 decimal places, use 1000 separator. 2) No conditional formatting is applied to the cell. 3) typing a zero in the cell, leaves the cell blank. 4) Typing "=1/0", gives #DIV/0!

    HTH. Thanks.


    Ron M

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    Well #3 makes it sound very much as though RG was correct and you have the option to suppress the display of zeroes set. Which version of Excel is it?
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    It is Excel 2010. I am not sure how to "suppress zeros", but the answer should be 6.28 and not 0.00. Is this "zero suppression" somthing that Excel has turned on "automatically" without my knowledge, i.e., is it a default of some sort?

    Ron M

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    In 2010, it's File-Options, Advanced section then scroll down about two thirds of the way to the 'Display options for this worksheet' section and check the 'Show a zero in cells that have zero value' option.
    Regards,
    Rory
    Microsoft MVP - Excel.

  14. #13
    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
    Would it be possible to attach a copy of the workbook? You could delete anything proprietary. All we need to see is the one cell and enough items to demonstrate the issues you have.

    Steve

  15. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try changing the format in the cell and then changing it back again.

    find a cell which displays properly and copy it to the problem cell. Then retype (not paste) your formula.
    David Grugeon
    Brisbane Australia

  16. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post
    Could you copy your exact formula and paste it into a post so we can see if
    (A) it begins with =
    (B) it contains names which may be variables or ranges
    (C) if there is anything else weird about it

    Try to do this just by selecting the whole formula in the formula bar, Ctrl-C, Esc, click in the email post, Ctrl-V. Sorry to be so detailed but if you retype it in your post5 it may not be "Exactly" the same. For example, hidden characters would not show up.
    David Grugeon
    Brisbane Australia

Page 1 of 3 123 LastLast

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
  •