+ Reply to Thread
Results 1 to 3 of 3

Using two different formulas which should give the same result, but doesn`t

  1. #1
    Registered User
    Join Date
    10-08-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2007
    Posts
    35

    Using two different formulas which should give the same result, but doesn`t

    Hi,

    I have a very weird problem in Excel which I hope someone can help me with.

    When I use the following formula: "=SUM(ABB151:ABB154)-ABB140" I get zero (I.e "-" instead of "0" with one decimal. But when I use this: "=SUM(ABB140-SUM(ABB151:ABB154))" I get very close zero, but when I multiply the latter with "10000000000" I get 0.0027 which I don`t understand. I have tried two other formulas using IF and MAX before typing the above and also there I get either zero or close to zero.

    Is this some kind of Excel bug that sometime excel automatically rounds after numerous decimals and sometimes not? Anyone familiar with this? I am just afraid that this is a problem on other formulas where I have not been able to implement control functions to check that everthing is correct.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Using two different formulas which should give the same result, but doesn`t

    Sounds like routine floating point error. Note that this isn't limited to Excel, but is inherent in all computer calculations no matter what programming language you use. Solutions are varied. Usual first suggestion is to nest your current functions in a ROUND() function to round the result to the desired precision.

    links to more information here: https://www.excelforum.com/groups/ma...nd-errors.html
    Last edited by MrShorty; 10-05-2017 at 10:27 AM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-08-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Using two different formulas which should give the same result, but doesn`t

    Excellent, thanks a lot! I will look more into your link. Appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 04-10-2017, 10:03 AM
  2. [SOLVED] Formulas that give a blank result graph as zero, want a gap in the graph instead.
    By neeners36 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-02-2015, 01:02 PM
  3. [SOLVED] Solver doesn't give absolute minimum
    By Siard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 09:06 AM
  4. Formula doesn't give same results when surrounded by brackets
    By EMaster in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2013, 12:06 AM
  5. Hovering cursor over columns doesn't give values
    By kesmithjr in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-09-2013, 09:27 AM
  6. [SOLVED] Vlook up doesn't give correct result
    By Fabienne88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-15-2013, 10:49 AM
  7. Trimmed mean doesn't give expected result
    By Winton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-03-2012, 06:31 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1