Closed Thread
Results 1 to 15 of 15

Formulas Keep Returning a Value of Zero

  1. 03-18-2011, 12:08 PM #1
    jbcoe
    jbcoe is offline
    Registered User
    Join Date
    05-10-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    11

    Formulas Keep Returning a Value of Zero

    Someone in our office has an interesting condition that I cannot seem to solve. Whenever they type a simple formula in their spreadsheet, the value keeps returning a 0. When you type something like 10/2 the value returns as 5. Once the = sign is used (=10/2), the value is 0. This also happens when we try to reference other cells (i.e. =k12/1500 will also return a value of 0.)

    I suspect it is a formatting issue, but I am not seeing it. From what I can tell, text cells are formatted as "General" and numbers are formatted as numbers.

    Thanks
    Last edited by jbcoe; 03-18-2011 at 04:27 PM.
    Reply With QuoteRegister To Reply
  2. 03-18-2011, 12:22 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Formulas Keep Returning a Value of Zero

    Try going to Tools|Options and in the Transition tab, make sure all the checkboxes are unchecked. Does that change/fix anything?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-18-2011, 04:01 PM #3
    jbcoe
    jbcoe is offline
    Registered User
    Join Date
    05-10-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Formulas Keep Returning a Value of Zero

    No Luck. We are still getting the same results. Thanks for the suggestion.

    Would importing data from somewhere else cause Excel to behave this way? It was explained to me that data from our accounting software was imported into the spreadsheet. I do not see any evidence that this has caused a problem anywhere else though.
    Reply With QuoteRegister To Reply
  4. 03-18-2011, 04:26 PM #4
    jbcoe
    jbcoe is offline
    Registered User
    Join Date
    05-10-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Formulas Keep Returning a Value of Zero

    Although we did not "solve" the problem, it seems to be working as expected. We closed Excel and opened the file again and the issue seemed to resolve itself. Thanks for your help.
    Reply With QuoteRegister To Reply
  5. 06-24-2014, 03:30 PM #5
    McFarvo
    McFarvo is offline
    Registered User
    Join Date
    06-24-2014
    Location
    USA
    MS-Off Ver
    2007
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    I had this same problem in Excel 2007! I have no idea why it started, but no matter the formula, when I pressed Enter on that cell, it returned a "0" which was very frustrating.

    The extremely tedious work-around (and the only solution that worked for me) was to go to

    Excel Options
    Formulas
    Calculation options
    Workbook Calculation
    Automatic

    changed from "Automatic" to "Manual"

    Now, I get the correct answers for formulas only if I click into the formula bar and press enter OR press F9 to recalculate all fields. I tried re-opening the file, but Automatic workbook calc still messes everything up, giving me 0s all over the place.

    This seems like a major bug that would probably be fixed in a more recent version of Excel.

    All hail F9 key!
    Last edited by McFarvo; 06-25-2014 at 10:39 AM.
    Reply With QuoteRegister To Reply
  6. 08-07-2019, 11:40 PM #6
    devansh
    devansh is offline
    Registered User
    Join Date
    10-29-2013
    Location
    Stuttgart
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Formulas Keep Returning a Value of Zero

    I eventually did the same thing McFarvo. I had to re-enter some 20-odd formulae to reach my final result.
    I tried restarting my MS office, and even computer yesterday jbcoe. It didn't work. But today's a new day and the same sheet, the same formulae, all are updating automatically. This isn't the first time! Crazy bug (Excel 2010)!
    Reply With QuoteRegister To Reply
  7. 08-07-2019, 11:56 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Formulas Keep Returning a Value of Zero

    devansh thanks for the update

    (Even though this thread is over 5 years old, all feedback is welcome)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  8. 11-01-2019, 11:45 AM #8
    nishkiara
    nishkiara is offline
    Registered User
    Join Date
    03-03-2010
    Location
    West Bloomfield, MI
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.
    Reply With QuoteRegister To Reply
  9. 04-19-2020, 02:24 PM #9
    nanioc
    nanioc is offline
    Registered User
    Join Date
    04-19-2020
    Location
    São Paulo - SP, Brasil
    MS-Off Ver
    Office 365
    Posts
    1

    Thumbs up Re: Formulas Keep Returning a Value of Zero

    Quote Originally Posted by nishkiara View Post
    I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.
    Hey Nishkiara! Thanks! I followed your tip and work on my sheet too. After fixing the circular references my formulas star to work again! Thank you again!!!
    Reply With QuoteRegister To Reply
  10. 10-30-2020, 08:12 AM #10
    TaraUm
    TaraUm is offline
    Registered User
    Join Date
    10-30-2020
    Location
    Wellington, New Zealand
    MS-Off Ver
    2013
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    Quote Originally Posted by nishkiara View Post
    I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.
    Thank you. You save my life.
    Reply With QuoteRegister To Reply
  11. 03-02-2022, 08:16 AM #11
    1oduro
    1oduro is offline
    Registered User
    Join Date
    03-02-2022
    Location
    Accra, Ghana
    MS-Off Ver
    2021
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    Quote Originally Posted by nanioc View Post
    Hey Nishkiara! Thanks! I followed your tip and work on my sheet too. After fixing the circular references my formulas star to work again! Thank you again!!!
    I would have kissed you if I could.
    Circular cell referencing error nearly marred my 3 years' worth of data. You're a saviour.
    Office 2021 and this still works flawlessly
    Thank you and God really bless you.
    Reply With QuoteRegister To Reply
  12. 03-04-2022, 02:08 PM #12
    TryingtoHelp
    TryingtoHelp is offline
    Registered User
    Join Date
    03-04-2022
    Location
    Glendale, California
    MS-Off Ver
    1902
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    I've had this problem, and the issue was a merged cell. I'd entered = and then clicked on a merged cell on a different worksheet, and it was returning 0.

    The formula read something like ='Sheet 1'!A1:A5. The solution was to just edit so that it didn't reference the whole range of merged cells, just one cell. So instead, it'd be something like ='Sheet 1'!A1.
    Reply With QuoteRegister To Reply
  13. 03-05-2022, 01:04 AM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Formulas Keep Returning a Value of Zero

    Another instance showing that merged cells are the devils work and should be avoided at all cost.
    Reply With QuoteRegister To Reply
  14. 03-15-2022, 07:24 AM #14
    BenRich
    BenRich is offline
    Registered User
    Join Date
    03-15-2022
    Location
    Swindon, England
    MS-Off Ver
    Microsoft 365 for business
    Posts
    1

    Re: Formulas Keep Returning a Value of Zero

    Dear All
    I have followed all the detail in the threads and still come up with Zero's
    It only occurs when I download a document from the software we are using
    Help!!!
    Cheers BenRich
    Reply With QuoteRegister To Reply
  15. 03-15-2022, 07:33 AM #15
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Formulas Keep Returning a Value of Zero

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1