Announcement

Collapse
No announcement yet.

Is Excel Infalliable?

Collapse

Unconfigured Ad Widget

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Is Excel Infalliable?

    Question for the computer and math savvy among us:
    I maintain Excel spread sheets for numerous tax documents and bank accounts. I have two columns of identical numbers, checked tick for tick, that Excell adds up $0.45 differently. One total agrees with bank statement. Since I round to dollar for tax returns, it does not make any difference but still bugs me. How is that possible?

  • #2
    Excel has a setting for "set precision as displayed". If you have data that is the result of a calculation, that can cause problems, or it can be exactly what you want.

    If I have $1.00 / 3 that calculates to $0.333... If I add that three times, Excel will sum that to $1.00.

    But if you display to 2 places and check "set precision as displayed", as you might do if you are splitting that dollar and writing three checks for $0.33 each, the sum is $0.99, and you just made a penny.
    Sunlight is said to be the best of disinfectants

    Comment


    • #3
      basic answer is that every product that Microfuck turns out is eminently fallable!

      Comment


      • #4
        Originally posted by gh View Post
        basic answer is that every product that Microfuck turns out is eminently fallable!
        As a generality, I'm not going to debate 40 years of software, but in this case, the software works as intended. Do you want the accounting result, or do you want to deal with the precise result? We all remember Jack Nicklaus describing his first paycheck:

        All through the years,” Jack explained, “people have said, 'You won WHAT the first time?! $33.33?!’ ” The Golden Bear then jokes, “Yes and I wonder whatever happened to that extra penny."
        Rather than Excel dictating the way it does the calculation, it leaves it to the user. The accountant wants $99.99. The sponsor gave out $100. Neither is wrong.
        Sunlight is said to be the best of disinfectants

        Comment


        • #5
          Originally posted by gh View Post
          basic answer is that every product that Microfuck turns out is eminently fallable!
          After a decade of being microfluncked from every possible direction I turned to Linux and will never look back at that cluster flunk.

          Comment


          • #6
            Let me be more specific: Bank account with two corroborating balance methods.
            Commencing 1/1/20 with bank's balance and no checks outstanding with10 deposits and 30 debits
            1. One column adding deposits and deducting debits agrees with bank balance to date.
            2. Adding total deposits and deducting total debits from same starting balance, gives 45 cent greater result than method 1. No typos, every amount the same. No included hidden formulas.'

            On another spread sheet, I have an instance of Master 403 example but that is not a problem, it changes with every entry not evenly divisible.
            I suspect gh has the answer. I have had other instances of Excel apparently corrupted cells

            Comment


            • #7
              Originally posted by lonewolf View Post
              Let me be more specific: Bank account with two corroborating balance methods.
              Commencing 1/1/20 with bank's balance and no checks outstanding with10 deposits and 30 debits
              1. One column adding deposits and deducting debits agrees with bank balance to date.
              2. Adding total deposits and deducting total debits from same starting balance, gives 45 cent greater result than method 1. No typos, every amount the same. No included hidden formulas.'

              On another spread sheet, I have an instance of Master 403 example but that is not a problem, it changes with every entry not evenly divisible.
              I suspect gh has the answer. I have had other instances of Excel apparently corrupted cells
              I tried to PM you, but I received a response that you have exceeded your PM quota.
              Sunlight is said to be the best of disinfectants

              Comment


              • #8
                Originally posted by Master403 View Post

                I tried to PM you, but I received a response that you have exceeded your PM quota.
                Hmmm???? I can't find PM on the new forum format. don't know how to clear space.. just try

                Good idea Pego.. Lord knows I don't need any more hackers. Several on the forum have my email. always glad to communicate that way..
                lonewolf
                Senior Member
                Last edited by lonewolf; 03-24-2020, 12:21 AM.

                Comment


                • #9
                  Lonewolf, Excel is great until it's not!

                  As Master403 pointed out, the number you see displayed is not always the number used in calculations.

                  This is the most likely reason:

                  1. "Two columns of identical numbers" usually means you have two columns that look the same but the numbers are actually slightly different.

                  2. This is especially the case where one set is provided to you by somebody else and you generate the other column yourself.

                  3. This is usually because the master data is using unrounded numbers for each transaction to calculate the total (then rounded). However, the transaction are dispalyed as rounded to you, so if you enter these, they will not include the hidden fractions.

                  4. Depending on the distribution of fractions, you should be out by different amounts each month.

                  5. Your bank should have an explanation of how they handle the numbers in their statements.


                  Hope this helps.



                  PS. For Lonewolf or any others interested, here a some links on this topic:

                  https://www.officetooltips.com/excel...g_numbers.html

                  https://www.officetooltips.com/excel..._in_excel.html



                  Comment


                  • #10
                    Thanks for the suggestions, guys. As I said, it is not a big deal, just puzzling.. All entries were generated by me to two decimal places, all simple addition and subtraction, no hidden multiplication or division. .. and the difference is my favor

                    Comment


                    • #11
                      Lonewolf, as soon as you get e-mail from Master403, delete your e-mail from this thread. The longer it stays here, the best chance for the hackers to steal it.
                      "A beautiful theory killed by an ugly fact."
                      by Thomas Henry Huxley

                      Comment


                      • #12
                        Originally posted by Pego View Post
                        Lonewolf, as soon as you get e-mail from Master403, delete your e-mail from this thread. The longer it stays here, the best chance for the hackers to steal it.
                        I have the eMail address, so a Mod could redact it.
                        Sunlight is said to be the best of disinfectants

                        Comment


                        • #13
                          Originally posted by lonewolf View Post

                          Hmmm???? I can't find PM on the new forum format. don't know how to clear space..
                          I like the upgrade, and am still trying to learn its features. You can get to PM by scrolling to the top of the page and clicking on "Messages", just to the right of "Notifications".

                          There is also a Chat feature available via the cartoon balloon next to the member's name at the top of some posts.
                          Master403
                          Senior Member
                          Last edited by Master403; 03-23-2020, 02:52 PM.
                          Sunlight is said to be the best of disinfectants

                          Comment


                          • #14
                            Originally posted by user4 View Post

                            After a decade of being microfluncked from every possible direction I turned to Linux and will never look back at that cluster flunk.
                            Many years ago someone described Linux to me as having all the parts for a car delivered to your front yard with no assembly instructions.

                            Can I use Linux today on a phone or laptop with a full suite of Office-type products?
                            Sunlight is said to be the best of disinfectants

                            Comment


                            • #15
                              Considering my career & retirement are built on babysitting Microsoft software running on Dell servers I can't complain too much about either company.

                              But since Excel is the subject I'll pass along my longtime mini-rant on MS Excel stupidity. It's based on one simple thing that's been broken for over three decades: in Excel, a spreadsheet program dedicated to the manipulation of numeric data, when you enter numbers in a cell they are treated as text instead of numbers unless preceded by an operator (=,+,-, etc). This is dumb: this is a numeric spreadsheet program - when I type in a number it should be treated like a number unless I say otherwise. Back in 1989 when I was forced to move from Lotus 1-2-3 - which treated a number like a number (cue the Prisoner's "I am not a number!")- to Excel I couldn't believe that Excel would treat numeric input as text by default. 31 years later this lunacy still exists - I still have to remember to put an = or somesuch in front of any number I want to use in a calculation.

                              In other MS news I've become a bit of a luddite in retirement and just moved up from Windows 7 to Windows 10 this winter, which turned out to be less of a PITA than I was anticipating. Win10's been around for over four years now to solidify and is basically Win7 with some good upgrades and new skin. New laptop with most bells/whistles helps too (old one was seven years old and cheap to begin with).

                              Too lazy to be a linux user on the computer side - my days of wanting to geek out are far far behind me. Of course linux is what our Android phones are based on, so many of us use it every day.



                              Comment

                              Working...
                              X