EXCEL SUM RoundingMake COUNTIFS count duplicate values only onceHow to divide the sum of cells by 7 and then...

How to deal with an incendiary email that was recalled

Why zero tolerance on nudity in space?

Gear reduction on large turbofans

Is there a weight limit to Feather Fall?

Difference between i++ and (i)++ in C

Is it possible to grant users sftp access without shell access? If yes, how is it implemented?

Why am I able to open Wireshark in macOS without root privileges?

Finding a logistic regression model which can achieve zero error on a training set training data for a binary classification problem with two features

Cookies - Should the toggles be on?

Why publish a research paper when a blog post or a lecture slide can have more citation count than a journal paper?

When can a QA tester start his job?

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Increment each digit in a number to form a new number

False written accusations not made public - is there law to cover this?

Is using an 'empty' metaphor considered bad style?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

Nested word series [humans only]

Why avoid shared user accounts?

Why did Luke use his left hand to shoot?

Why would space fleets be aligned?

Absorbing damage with Planeswalker

Can I make estimated tax payments instead of withholding from my paycheck?

Eww, those bytes are gross

Why do neural networks need so many training examples to perform?



EXCEL SUM Rounding


Make COUNTIFS count duplicate values only onceHow to divide the sum of cells by 7 and then transpose them in ExcelPerform calculations on a cell-by-cell basisAvoiding Long Nested IFs in Excel FunctionReverse Hash with MS ExcelExcel count across multiple criteriaExcel sum if formulain Excel I want trailing 0's on a rounded value that ends up in a text formatted cell, how can I do that?Excel 2016, turning a number into Years and Months automaticallyExcel: How to set up data for Feedback Analysis













0















Hello to all Super Users!



I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full decimal places - no other decimal numbers following in each of the numbers):



0.140495867768595,
0.173553719008264,
0.206611570247934,
0.076741440377804,
0.0826446280991736,
0.118063754427391,
0.063754427390791,
0.0814639905548997,
0.0566706021251476,


When I perform a simple sum function in excel result is equal to 1.0000000000000000 but when I put these numbers into calculator result is equal to 0.9999999999999999 (which is correct) -> my question, therefore, is there any way how to set the behavior of Excel to calculate this correctly and why is it calculating it wrong? Thank you all in advance for your suggestions!










share|improve this question





























    0















    Hello to all Super Users!



    I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full decimal places - no other decimal numbers following in each of the numbers):



    0.140495867768595,
    0.173553719008264,
    0.206611570247934,
    0.076741440377804,
    0.0826446280991736,
    0.118063754427391,
    0.063754427390791,
    0.0814639905548997,
    0.0566706021251476,


    When I perform a simple sum function in excel result is equal to 1.0000000000000000 but when I put these numbers into calculator result is equal to 0.9999999999999999 (which is correct) -> my question, therefore, is there any way how to set the behavior of Excel to calculate this correctly and why is it calculating it wrong? Thank you all in advance for your suggestions!










    share|improve this question



























      0












      0








      0








      Hello to all Super Users!



      I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full decimal places - no other decimal numbers following in each of the numbers):



      0.140495867768595,
      0.173553719008264,
      0.206611570247934,
      0.076741440377804,
      0.0826446280991736,
      0.118063754427391,
      0.063754427390791,
      0.0814639905548997,
      0.0566706021251476,


      When I perform a simple sum function in excel result is equal to 1.0000000000000000 but when I put these numbers into calculator result is equal to 0.9999999999999999 (which is correct) -> my question, therefore, is there any way how to set the behavior of Excel to calculate this correctly and why is it calculating it wrong? Thank you all in advance for your suggestions!










      share|improve this question
















      Hello to all Super Users!



      I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full decimal places - no other decimal numbers following in each of the numbers):



      0.140495867768595,
      0.173553719008264,
      0.206611570247934,
      0.076741440377804,
      0.0826446280991736,
      0.118063754427391,
      0.063754427390791,
      0.0814639905548997,
      0.0566706021251476,


      When I perform a simple sum function in excel result is equal to 1.0000000000000000 but when I put these numbers into calculator result is equal to 0.9999999999999999 (which is correct) -> my question, therefore, is there any way how to set the behavior of Excel to calculate this correctly and why is it calculating it wrong? Thank you all in advance for your suggestions!







      microsoft-excel worksheet-function microsoft-excel-2010






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 21 at 19:50









      teylyn

      17.3k22539




      17.3k22539










      asked Jan 21 at 19:35









      AdysAdys

      1




      1






















          3 Answers
          3






          active

          oldest

          votes


















          1














          Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.



          Microsoft support article



          Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.






          share|improve this answer































            0














            Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.



            See https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ for more information about how Excel stores numbers.






            share|improve this answer































              0














              Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.



              Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.



              Input                   Sum (by Excel)          Sum (Actual)
              ------------------- ------------------- -------------------
              0.140495867768595 0.140495867768595 0.140495867768595
              0.173553719008264 0.314049586776859 0.314049586776859
              0.206611570247934 0.520661157024793 0.520661157024793
              0.076741440377804 0.597402597402597 0.597402597402597
              0.0826446280991736 0.680047225501771 0.6800472255017706
              0.118063754427391 0.798110979929162 0.7981109799291616
              0.063754427390791 0.861865407319953 0.8618654073199526
              0.0814639905548997 0.943329397874852 0.9433293978748523
              0.0566706021251476 1.00000000000000 0.9999999999999999
              0.000000000000001 1.00000000000000 1.0000000000000009 <-- Bonus information


              Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.



              Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel





              share























                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "3"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1396746%2fexcel-sum-rounding%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                3 Answers
                3






                active

                oldest

                votes








                3 Answers
                3






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                1














                Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.



                Microsoft support article



                Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.






                share|improve this answer




























                  1














                  Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.



                  Microsoft support article



                  Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.






                  share|improve this answer


























                    1












                    1








                    1







                    Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.



                    Microsoft support article



                    Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.






                    share|improve this answer













                    Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.



                    Microsoft support article



                    Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Jan 21 at 19:50









                    teylynteylyn

                    17.3k22539




                    17.3k22539

























                        0














                        Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.



                        See https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ for more information about how Excel stores numbers.






                        share|improve this answer




























                          0














                          Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.



                          See https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ for more information about how Excel stores numbers.






                          share|improve this answer


























                            0












                            0








                            0







                            Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.



                            See https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ for more information about how Excel stores numbers.






                            share|improve this answer













                            Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.



                            See https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ for more information about how Excel stores numbers.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jan 21 at 19:51









                            Doug DedenDoug Deden

                            777213




                            777213























                                0














                                Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.



                                Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.



                                Input                   Sum (by Excel)          Sum (Actual)
                                ------------------- ------------------- -------------------
                                0.140495867768595 0.140495867768595 0.140495867768595
                                0.173553719008264 0.314049586776859 0.314049586776859
                                0.206611570247934 0.520661157024793 0.520661157024793
                                0.076741440377804 0.597402597402597 0.597402597402597
                                0.0826446280991736 0.680047225501771 0.6800472255017706
                                0.118063754427391 0.798110979929162 0.7981109799291616
                                0.063754427390791 0.861865407319953 0.8618654073199526
                                0.0814639905548997 0.943329397874852 0.9433293978748523
                                0.0566706021251476 1.00000000000000 0.9999999999999999
                                0.000000000000001 1.00000000000000 1.0000000000000009 <-- Bonus information


                                Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.



                                Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel





                                share




























                                  0














                                  Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.



                                  Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.



                                  Input                   Sum (by Excel)          Sum (Actual)
                                  ------------------- ------------------- -------------------
                                  0.140495867768595 0.140495867768595 0.140495867768595
                                  0.173553719008264 0.314049586776859 0.314049586776859
                                  0.206611570247934 0.520661157024793 0.520661157024793
                                  0.076741440377804 0.597402597402597 0.597402597402597
                                  0.0826446280991736 0.680047225501771 0.6800472255017706
                                  0.118063754427391 0.798110979929162 0.7981109799291616
                                  0.063754427390791 0.861865407319953 0.8618654073199526
                                  0.0814639905548997 0.943329397874852 0.9433293978748523
                                  0.0566706021251476 1.00000000000000 0.9999999999999999
                                  0.000000000000001 1.00000000000000 1.0000000000000009 <-- Bonus information


                                  Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.



                                  Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel





                                  share


























                                    0












                                    0








                                    0







                                    Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.



                                    Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.



                                    Input                   Sum (by Excel)          Sum (Actual)
                                    ------------------- ------------------- -------------------
                                    0.140495867768595 0.140495867768595 0.140495867768595
                                    0.173553719008264 0.314049586776859 0.314049586776859
                                    0.206611570247934 0.520661157024793 0.520661157024793
                                    0.076741440377804 0.597402597402597 0.597402597402597
                                    0.0826446280991736 0.680047225501771 0.6800472255017706
                                    0.118063754427391 0.798110979929162 0.7981109799291616
                                    0.063754427390791 0.861865407319953 0.8618654073199526
                                    0.0814639905548997 0.943329397874852 0.9433293978748523
                                    0.0566706021251476 1.00000000000000 0.9999999999999999
                                    0.000000000000001 1.00000000000000 1.0000000000000009 <-- Bonus information


                                    Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.



                                    Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel





                                    share













                                    Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.



                                    Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.



                                    Input                   Sum (by Excel)          Sum (Actual)
                                    ------------------- ------------------- -------------------
                                    0.140495867768595 0.140495867768595 0.140495867768595
                                    0.173553719008264 0.314049586776859 0.314049586776859
                                    0.206611570247934 0.520661157024793 0.520661157024793
                                    0.076741440377804 0.597402597402597 0.597402597402597
                                    0.0826446280991736 0.680047225501771 0.6800472255017706
                                    0.118063754427391 0.798110979929162 0.7981109799291616
                                    0.063754427390791 0.861865407319953 0.8618654073199526
                                    0.0814639905548997 0.943329397874852 0.9433293978748523
                                    0.0566706021251476 1.00000000000000 0.9999999999999999
                                    0.000000000000001 1.00000000000000 1.0000000000000009 <-- Bonus information


                                    Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.



                                    Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel






                                    share











                                    share


                                    share










                                    answered 6 mins ago









                                    wilsonwilson

                                    3,68811536




                                    3,68811536






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Super User!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1396746%2fexcel-sum-rounding%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        VNC viewer RFB protocol error: bad desktop size 0x0I Cannot Type the Key 'd' (lowercase) in VNC Viewer...

                                        Couldn't open a raw socket. Error: Permission denied (13) (nmap)Is it possible to run networking commands...

                                        Why not use the yoke to control yaw, as well as pitch and roll? Announcing the arrival of...