Anyone using Google Drive spreadsheet ever caught a math error?

Printable View

08-01-2018, 16:03
Irving

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.
08-01-2018, 16:07
00tec

Just tested on mine and got the same result.
08-01-2018, 16:13
Sawin

Millennials.
08-01-2018, 16:15
mattiooo

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.
08-01-2018, 16:23
Irving

Quote:

Originally Posted by 00tec

Just tested on mine and got the same result.

Really, or are you just messing with me because my post is vague?

Quote:

Originally Posted by mattiooo

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.
08-01-2018, 16:34
mattiooo

Quote:

Originally Posted by Irving

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.
08-01-2018, 16:46
00tec

Really. Did it once, then I went to grab a screenshot and couldn't get it to do it again.
08-01-2018, 16:48
Irving

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.
08-01-2018, 16:50
Irving

Quote:

Originally Posted by 00tec

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.
08-01-2018, 19:51
Rooskibar03

Common Core Math
08-01-2018, 20:59
def90

Quote:

Originally Posted by mattiooo

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.
08-01-2018, 21:01
Irving

Wouldn't it be one cent short in that situation though, instead of one cent over?
08-01-2018, 22:32
mackbamf

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.
08-01-2018, 22:38
Irving

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.
08-01-2018, 22:55
CS1983

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.
08-01-2018, 23:22
Irving

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.
08-01-2018, 23:36
Gman

Quote:

Originally Posted by CavSct1983

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.
08-02-2018, 05:06
thedave1164

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
08-02-2018, 06:34
Jeffrey Lebowski

Quote:

Originally Posted by CavSct1983

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
08-02-2018, 08:30
Jer

Quote:

Originally Posted by CavSct1983

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.
08-02-2018, 15:14
DOC

My abacus never rounded up with another bead.
08-02-2018, 15:45
TFOGGER

This is the kind of shit that makes spacecraft miss entire planets...
08-02-2018, 17:28
Irving

https://lh3.googleusercontent.com/wt...g=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.
08-02-2018, 17:39
mattiooo

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).
08-02-2018, 17:45
Irving

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.
08-02-2018, 21:30
GilpinGuy

1 Attachment(s)

Quote:

Originally Posted by mattiooo

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).

Attachment 75557
08-02-2018, 21:42
Irving

Now for sale.

https://www.engadget.com/2018/08/02/...voice-command/
08-02-2018, 21:47
mattiooo

Quote:

Originally Posted by GilpinGuy

Attachment 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>
08-02-2018, 21:48
Irving

I read that series earlier this year and it was pretty great.
08-02-2018, 21:49
mattiooo

Quote:

Originally Posted by Irving

Now for sale.

https://www.engadget.com/2018/08/02/...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. "
08-02-2018, 21:51
Irving

Seems like it'd be easy enough to make.

For someone else to make, not me.
08-03-2018, 21:17
RblDiver

Quote:

Originally Posted by Irving

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.
08-03-2018, 21:27
Irving

If I had better internet connection, I'd post a clip from Life of Brian.
08-05-2018, 11:55
O2HeN2

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
08-05-2018, 11:59
brutal

Quote:

Originally Posted by GilpinGuy

Attachment 75557

Everyone knows the theory on the HAL name?
08-05-2018, 12:12
TFOGGER

Quote:

Originally Posted by brutal

Everyone knows the theory on the HAL name?

Yup
08-05-2018, 13:27
O2HeN2

Quote:

Originally Posted by brutal

Everyone knows the theory on the HAL name?

Yes. How about the confirmed story behind Windows NT "WNT"?

O2
08-05-2018, 23:54
brutal

Quote:

Originally Posted by O2HeN2

Yes. How about the confirmed story behind Windows NT "WNT"?

O2

I read Cutler said it was coincidence only.
08-06-2018, 12:18
Gman

Quote:

Originally Posted by Irving

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
08-06-2018, 22:30
brutal

LOL @ 3:55, they were ahead of their time.