View Full Version : Anyone using Google Drive spreadsheet ever caught a math error?
I use Google Drive spreadsheets to track things. I'll have to look up the exact name, but it is basically the Google version of Microsoft Excel. Today I noticed that when it was summing a certain column of numbers, it was off by one cent. $.01. For the life of me I cannot figure out where the error is. Well, basically when it adds a number that ends in .51 to a number that ends in .11, it comes up with .63 instead of .62. I can't figure out why. At this point it really doesn't matter, and I don't have time right this second to try a bunch of tests, but I'm wondering if anyone has ever caught a program making a math error before.
Just tested on mine and got the same result.
mattiooo
08-01-2018, 16:15
Sounds like a rounding result. Are the numbers longer than that and being rounded to 2 decimal places? Even though they display rounded, the full number is calculated in formulas.
Just tested on mine and got the same result.
Really, or are you just messing with me because my post is vague?
Sounds like a rounding result. Are the numbers longer than that and being rounded to 2 decimal places? Even though they display rounded, the full number is calculated in formulas.
I thought it might be that, but I checked the same columns in different months and those numbers all came out correct. In this specific case it is all currency, so rounding shouldn't be an issue. I thought it might have something to do with the fact that the entire column is generated by taking a percentage of a number in the prior column, and that was somehow causing a rounding error, but again, I tried the same columns in other months and everything is fine. Further, the column is broken down into weeks. When I add the week totals together, everything works out. When I have time later tonight I'll see if I can post some screen shots to give a more clear picture.
mattiooo
08-01-2018, 16:34
I thought it might be that, but I checked the same columns in different months and those numbers all came out correct. In this specific case it is all currency, so rounding shouldn't be an issue. I thought it might have something to do with the fact that the entire column is generated by taking a percentage of a number in the prior column, and that was somehow causing a rounding error, but again, I tried the same columns in other months and everything is fine. Further, the column is broken down into weeks. When I add the week totals together, everything works out. When I have time later tonight I'll see if I can post some screen shots to give a more clear picture.
Currency by default rounds up to the nearest penny, so my guess would still be rounding - for example:
If you took 43% of $97.37, the formula is 97.37 * .43 you would get 41.8691, but in a currency column, that would display as $41.87. However under that display is still 41.8691, and that is what is used in subsequent formulas.
Without seeing actual numbers, it's hard for me to say if that is what is happening to you, but that seems what is most likely to me.
One way you can check is to change the formatting of all the currency columns or rows to numbers.
Hope that helps.
Really. Did it once, then I went to grab a screenshot and couldn't get it to do it again.
I think that's what I'll mess around with just to check and see what the percentages calculate out to. Currently, if I click on a figure, it only displays the equation used to generate the number, but not the raw number itself.
Really. Did it once, then I went to grab a screenshot and couldn't get it to do it again.
I knew it! Turns out mankind is just as responsible for math errors as rising temperatures.
Rooskibar03
08-01-2018, 19:51
Common Core Math
Currency by default rounds up to the nearest penny, so my guess would still be rounding - for example:
If you took 43% of $97.37, the formula is 97.37 * .43 you would get 41.8691, but in a currency column, that would display as $41.87. However under that display is still 41.8691, and that is what is used in subsequent formulas.
Without seeing actual numbers, it's hard for me to say if that is what is happening to you, but that seems what is most likely to me.
One way you can check is to change the formatting of all the currency columns or rows to numbers.
Hope that helps.
This.. it displays the result as a rounded number but the original number remains behind the scenes to be used in any additional formulas.
Wouldn't it be one cent short in that situation though, instead of one cent over?
mackbamf
08-01-2018, 22:32
If you are dealing with formulas then this is just a rounding issue. If the column is adding hard numbers that you are entering to a couple of decimal places and it isn't adding up then that would be a separate issue. I have to deal with large spreadsheets at times and these type of rounding issues are not uncommon with formulas.
I've been doing this for years, and this is the first time I've noticed a discrepancy that I couldn't explain. So one cent in years is pretty good. If it makes a difference it's a currency column, times a percentage. So everything is actually in currency format. I'm just glad to know that it's not abnormal and I can still trust my spread sheet.
Google spreadsheets was a forked project written by Peter Gibbons, Michael Bolton, and Samir Nagheenanajar after they lost their previous jobs working together at a dot com bubble tech company. This might explain the issues you are seeing.
At the end of the year, I'll just slip a check for one cent into an envelope and slide it under the door at the IRS.
Google spreadsheets was a forked project written by Peter Gibbons, Michael Bolton, and Samir Nagheenanajar after they lost their previous jobs working together at a dot com bubble tech company. This might explain the issues you are seeing.
Oh, an Initech company.
thedave1164
08-02-2018, 05:06
make sure the formulas are being applied against values, not other formulas.
that is my best guess, I have a spreadsheed sheet at work that combines a lot of formulas in one sheet and we have to be careful that we are using actual values or it goes all awry
Jeffrey Lebowski
08-02-2018, 06:34
Google spreadsheets was a forked project written by Peter Gibbons, Michael Bolton, and Samir Nagheenanajar after they lost their previous jobs working together at a dot com bubble tech company. This might explain the issues you are seeing.
lol, bravo
Google spreadsheets was a forked project written by Peter Gibbons, Michael Bolton, and Samir Nagheenanajar after they lost their previous jobs working together at a dot com bubble tech company. This might explain the issues you are seeing.
I was thinking this same thing as I was reading through the thread. Well played sir.
My abacus never rounded up with another bead.
This is the kind of shit that makes spacecraft miss entire planets...
https://lh3.googleusercontent.com/wtFdcTPvbjLto12JinQAq6ZMTxtui4ZafPALPOzE_o0Wo4IPdM JfppSzt8BgEFJxcj9Xa7uCFxog7KsW_6mVrLNrup7SibIjeTvY uZ2YrWwzQeJjYBXHznTZR6DpJXZpJPIQiUA3qc1XLKYc_Uvgdq cD90UB30e4NcpGOpsnxNoKQmm5R3tQD_nTEroAoHr01HfpY-KMIzMOdoBAIIWCBrtd0rXq6GSj4NDmFa62KRyAfKiK6oiC8YoI 5fTf3thXqu5K64NcCo5w67zJ0c8zXJi--XXbSIQWlUgAFkc2qT6LKrb3ov7BdRqC9J4Filrxmf04KzzVMW8 izEYfwJ6iY9uuRb3sSdldpAuqOAG9pdPX1zJBW328SAhbaA99q _FODpvDXUsIjrZn-hToSanJfdNE1l-lXa01jDq843UPjD2JsAkyYK57w3vRW_Ao8C_koQ1YWLzgghoUj smsVLEalHtAi5otRvykHdxFkiOQCp93-mYQ6GSMge056yb_5mZ7cn9v3HAexrHVNTuNK_7AVVn-cTBGtGk9-dwVT-yq29i7E-2kH5fazgQUijihuWksqJ39Irhy4VJP7mjEV2_f6q2rZK34uklt hturrF3XFKqYzgz_G5ErY5F_tMNfH9GzLJIerfjFK0cokVqszm RFXBi0vMzl-BRlFg=w265-h494-no
Okay, here is my best guess.
The figure 545.19 x .60 = 327.114
and 460.19 x .60 = 276.114
In the second column, each of the results is rounded down to display the correct number to the hundredth column. When you add those numbers together, it must change that thousandths 4 to an 8 and then round up. That also explains why if you sum the numbers up by week (redlines) all the totals add correctly. Then if you just sum up the totals, you get the one cent difference. This was a healthy exercise, thanks for the participation.
ETA: That's definitely it, because if you change the columns to just "Number" instead of "Currency" then the total comes to 5,069.028.
Crazy that after years and years of using Excel (and the like) to track things, this is the first time I've ever hit the magic combination to throw a number off.
mattiooo
08-02-2018, 17:39
Yep. Computers are never wrong - like my wife. They just do EXACTLY what we tell them to do - unlike my wife. If something is wrong it's always something a person did (or didn't do).
Tell that to Google maps when it refused to send me on the route I chose today about 6 times in a row! I think it knew I was questioning the legitimacy of Google apps.
GilpinGuy
08-02-2018, 21:30
Yep. Computers are never wrong - like my wife. They just do EXACTLY what we tell them to do - unlike my wife. If something is wrong it's always something a person did (or didn't do).
75557
Now for sale.
https://www.engadget.com/2018/08/02/hal-9000-replica-bluetooth-voice-command/
mattiooo
08-02-2018, 21:47
75557
LOL - Exactly. Not sure if you were supporting my point or arguing it, but even in fiction: In 2010 we find out that HAL's crisis was caused by a programming contradiction - something a person did. <g>
I read that series earlier this year and it was pretty great.
mattiooo
08-02-2018, 21:49
Now for sale.
https://www.engadget.com/2018/08/02/hal-9000-replica-bluetooth-voice-command/
That's awesome but....a bit rich for my blood. I'm about to spend that on a handgun.
"The higher end model will eventually cost $1,199, but is available for $889 on Indigogo now. It will include a replica command console to mount the Bluetooth HAL into, and it will recognize your voice commands and respond with lines from the movie. "
Seems like it'd be easy enough to make.
For someone else to make, not me.
RblDiver
08-03-2018, 21:17
I knew it! Turns out mankind is just as responsible for math errors as rising temperatures.
I forget the exact values, but in the game Mass Effect 2, an AI character Legion explains that the difference between his group (the "True Geth") and a splinter group (the "Heretics") is that a calculation that his group evaluates as 0.051 is evaluated by their group as 0.052.
If I had better internet connection, I'd post a clip from Life of Brian.
Initially I thought the error was due to bistromathics, but then realized that the error would be much larger if that were the case.
O2
75557
Everyone knows the theory on the HAL name?
Everyone knows the theory on the HAL name?
Yup
Everyone knows the theory on the HAL name?
Yes. How about the confirmed story behind Windows NT "WNT"?
O2
Yes. How about the confirmed story behind Windows NT "WNT"?
O2
I read Cutler said it was coincidence only.
If I had better internet connection, I'd post a clip from Life of Brian.1st world problems, presenting the machine that goes 'Bing!'?
http://youtu.be/NcHdF1eHhgc
LOL @ 3:55, they were ahead of their time.
Powered by vBulletin® Version 4.2.3 Copyright © 2025 vBulletin Solutions, Inc. All rights reserved.