+ Reply to Thread
Results 1 to 14 of 14

Formula calculating invisible decimals

  1. #1
    Registered User
    Join Date
    07-04-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    43

    Formula calculating invisible decimals

    Hi i have downloaded data in cells A1 A2 A3 A4 .
    9669.86
    9669.62
    9669.62
    9669.51

    this formula in B1
    =AND((A1-A2)>(A3-A4)).

    in evaluate formula it is showing like this
    =AND(0.23999999999782>0.110000000001582)
    =TRUE.
    how to remove this invisible decimal calculation?

    actually it should calculate like this
    =AND((9669.86-9669.62)>(9669.62-9669.51))
    =AND(0.24>0.11)
    =TRUE.
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Formula calculating invisible decimals

    Floating point arithmetic errors.

    Read http://support.microsoft.com/kb/78113
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    Re: Formula calculating invisible decimals

    =AND(ROUND(A1-A2,2)>ROUND(A3-A4,2)).
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-04-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    43

    Re: Formula calculating invisible decimals

    Hi Olly i have read your link...still clue less what to do? iam bad at maths... i need to format the data ?
    previously many times used this formula with same type of data but never faced such decimal calculations...why i am getting like this now..because of this sometime my calculations are showing TRUE instead of FALSE

    Hi ChemistB .. formula not working..

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    Re: Formula calculating invisible decimals

    Sigh,perhaps you could explain what you mean by "not working" See attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-04-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    43

    Re: Formula calculating invisible decimals

    sorry, will check this now properly

  7. #7
    Registered User
    Join Date
    07-04-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    43

    Re: Formula calculating invisible decimals

    working fine.. thank you. do i need to do this for every formula? can i format the data to avoid this decimals?

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    Re: Formula calculating invisible decimals

    You can set the workbook to do "Precision as displayed" That means all calculations will only use what is formatted on the sheet.
    File>Options>Advanced>"When calculating this workbook"
    You will permanently lose any values you enter greater than the formatted amount.
    For example, if you have a cell formatted as 2 decimals and you enter 1.749, it will instantly convert it to 1.75. If you convert it back out of "precision as displayed" it will remain 1.75

  9. #9
    Registered User
    Join Date
    07-04-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    43

    Re: Formula calculating invisible decimals

    will check this thoroughly thank you.

  10. #10
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula calculating invisible decimals

    Quote Originally Posted by rajre View Post
    Hi i have downloaded data in cells A1 A2 A3 A4 .
    9669.86
    9669.62
    9669.62
    9669.51
    this formula in B1
    =AND((A1-A2)>(A3-A4)).
    in evaluate formula it is showing like this
    =AND(0.23999999999782>0.110000000001582)
    =TRUE.
    how to remove this invisible decimal calculation?
    Setting "Precision as displayed" (PAD) will not remedy the problem with the formula exactly as shown.

    PAD only ensures that the final value in a cell is rounded to the precision indicated by the cell format.

    So, for example, if B2 is =A1-A2 and B3 is =A3-A4, both formatted with 2 decimal places, =(B2>B3) will behave as you might expect based on the displayed values.

    But =(A1-A2>A3-A4) might still continue to surprise you due to the increased precision and anomalies of 64-bit binary floating-point arithmetic.

    (Note: The use of AND is superfluous in this context.)
    Last edited by joeu2004; 09-11-2014 at 02:07 PM. Reason: remove superfluous use of AND()

  11. #11
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula calculating invisible decimals

    Quote Originally Posted by rajre View Post
    Hi i have downloaded data in cells A1 A2 A3 A4 .
    9669.86
    9669.62
    9669.62
    9669.51
    this formula in B1
    =AND((A1-A2)>(A3-A4)).
    in evaluate formula it is showing like this
    =AND(0.23999999999782>0.110000000001582)
    [....]
    how to remove this invisible decimal calculation?
    PS.... Why do you care if =(A1-A2>A3-A4) appears differently than expected when using Evaluate Formula?

    The result of the comparison is what we should expect, given those values in A1, A2, A3 and A4.

    Perhaps you simply chose a poor example. Certainly there are combinations of numbers where =(A1-A2>A3-A4) has surprising results. For example:

    A1: 9669.28
    A2: 9669.17
    A3: 967.28
    A4: 967.17

    The formula =(A1-A2>A3-A4) returns TRUE(!) because A1-A2 is 0.110000000000582 and A3-A4 is 0.110000000000014 (approximately).

    (The actual subexpression values have even more precision than Excel is willing to format.)
    Last edited by joeu2004; 09-11-2014 at 02:04 PM. Reason: cosmetic

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    Re: Formula calculating invisible decimals

    With simplistic arithmetic expressions, one would expect differences only at the 15th decimal place so as long as we round each side of the > before comparing, we should not have any issues rounding to 2 decimal places.

  13. #13
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula calculating invisible decimals

    Quote Originally Posted by joeu2004 View Post
    A1: 9669.28
    A2: 9669.17
    A3: 967.28
    A4: 967.17
    The formula =(A1-A2>A3-A4) returns TRUE(!) because A1-A2 is 0.110000000000582 and A3-A4 is 0.110000000000014 (approximately).
    Quote Originally Posted by ChemistB View Post
    With simplistic arithmetic expressions, one would expect differences only at the 15th decimal place
    The example I provided demonstrates the fallacy of that assertion. The difference is in the 13th significant digit.

    In fact, where the difference occurs depends on the magnitude of the numbers. For example, =70368744177663.1-70368744177663 is 0.1015625, a difference in the 3rd significant digit.

    Quote Originally Posted by ChemistB View Post
    so as long as we round each side of the > before comparing, we should not have any issues rounding to 2 decimal places.
    That is, explicit rounding. I agree.

    I took expection to the (incorrect) suggestion that setting "Precision as displayed" (PAD) would remedy the problem of comparing subexpressions, e.g. =(A1-A2>A3-A4). It does not.

    @rajre: With my example, you might notice that =A1-A2-A3+A4 is exactly zero as you would expect, but =(A1-A2-A3+A4>0) is TRUE(!).

    This is due to a dubious Excel heuristic that is poorly described under the title "... close to zero" in support.microsoft.com/kb/78113. Sometimes, Excel treats values as equal when they are "close enough". The problem is: the criteria for "close enough" are not defined, and the heuristic is applied inconsistently.

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    Re: Formula calculating invisible decimals

    Yes, on consideration, the use of precision as displayed would not fix the problem unless you broke the formula up into two cells (A1-A2) and (A3-A4) and then compared (thus doing the same thing as rounding).

+ 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: 06-06-2012, 01:29 PM
  2. Calculating Decimals
    By leena671 in forum Excel General
    Replies: 5
    Last Post: 09-20-2010, 10:11 PM
  3. Copying invisible columns, but not invisible rows
    By petalred in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2008, 11:17 AM
  4. [SOLVED] Easy calculating ending with wrong decimals
    By Ronny Andersen in forum Excel General
    Replies: 2
    Last Post: 03-09-2006, 07:29 AM
  5. Decimals in Formula
    By Mestrella31 in forum Excel General
    Replies: 2
    Last Post: 01-05-2005, 02:06 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