Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / Excel Woes and Rounding

Author
Message
GIDustin
16
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 1st Mar 2012 21:45 Edited at: 1st Mar 2012 21:45
Link
So I have this spreadsheet that I use to keep track of my finances. It works much better than that little book they ship out with your checks. Problem is that I keep getting off by fractions of a penny. I isolated it down to just a few cells and copied them to an entire new book. Here is what I got:



You can see each cell's contents displayed to some insane amount of decimal places, as well as the formula of the total cell. Why is it off by a fraction of a penny?

Attachments

Login to view attachments
Back to top
Profile PM Email Website
GIDustin
16
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 1st Mar 2012 21:45
Link
Double post so I can attach the XLS. Does anyone else get this really off result or is it just me?

Attachments

Login to view attachments
Back to top
Profile PM Email Website
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 1st Mar 2012 22:11
Link
Displays the same in libreoffice.

Could be due to binary rounding. I recommend using currency rather than number to store dollars, as it should do special things that make it add up right.

However, I can't find a source off hand that actually says that it does anything different, so I'd test.

I also can't read the gibberish MS uses for formatting, cuz I'd have to study it for some time, so I'll leave this as an exercise for you

darkBIT TGC Torrent Tracker / File Server - AGK Chat: #agk : irc.devhat.net
Back to top
Profile PM Email Website
zeroSlave
15
Years of Service
User Offline
Joined: 13th Jun 2009
Location: Springfield
Posted: 1st Mar 2012 22:42 Edited at: 1st Mar 2012 22:45
Link
Does it in openOffice, too.
It's weird. It's line 31 that's doing it, though. The -100.00

When I delete it, it's fine. When I add a line and add 100.00, it's fine. when I subtract another 100.00 it gives me:
$(23.130000000000100000000)

Quote: "Could be due to binary rounding"

Probably this is probably the culprit: http://support.microsoft.com/kb/78113
Weird that it affects the "-100"

Back to top
Profile PM Email
GIDustin
16
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 1st Mar 2012 23:36
Link
I actually went through and randomly deleted lines and UNDID them back in, and it isnt just the -100. Item 25, if deleted, fixes the problem as well.

Quote: "I recommend using currency rather than number to store dollars"


I have tried this as well. The problem isn't "visible", but it is still there.

Quote: "http://support.microsoft.com/kb/78113"


I looked at that and it appears I just need to make my sum formula "=round(SUM(D334),2)". Not the answer I wanted to hear, but it works. At the very end of my spreadsheet is a "Enter bank account total here:", and it compares to what I have totaled: '=if(a1=b1,"Balanced!","Not Balanced!")', and it keeps saying Not Balanced, and this fraction of a penny is the reason why. At least that went away with the rounding function.

At least you guys can reproduce the problem. I was about to reinstall my Office just to test that...
Back to top
Profile PM Email Website
Pincho Paxton
22
Years of Service
User Offline
Joined: 8th Dec 2002
Location:
Posted: 2nd Mar 2012 12:36
Link
Don't tell me...... you work for the Royal Bank Of Scotland.

Back to top
Profile PM Email Website
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 2nd Mar 2012 12:57
Link
I'm surprised excel doesn't do special number storage for currency, like SQL does:

http://stackoverflow.com/questions/2313895/is-sql-server-money-data-type-a-decimal-floating-point-or-binary-floating-poin

darkBIT TGC Torrent Tracker / File Server - AGK Chat: #agk : irc.devhat.net
Back to top
Profile PM Email Website
Pincho Paxton
22
Years of Service
User Offline
Joined: 8th Dec 2002
Location:
Posted: 2nd Mar 2012 14:36 Edited at: 2nd Mar 2012 14:37
Link
I've seen a similar thread on another site. The solution in the end was that not all of the cells were set up individually for currency. Some cells had a different setting. Mind you, that was a sorting order problem.

Back to top
Profile PM Email Website
BiggAdd
Retired Moderator
20
Years of Service
User Offline
Joined: 6th Aug 2004
Location: != null
Posted: 3rd Mar 2012 22:33 Edited at: 3rd Mar 2012 22:38
Link
This should sort the problem also:

Highlight the currency column then simply select accounting.
Or use the 0.00 buttons to increase or decrease the decimal places if you want to keep your special red/black format.

Or when you highlight the currency cells, right click and then select "format cells", you'll be able to change it in there.

Attachments

Login to view attachments
Back to top
Profile PM
old_School
15
Years of Service
User Offline
Joined: 29th Aug 2009
Location:
Posted: 4th Mar 2012 04:56
Link
Solution: Hire a accountant
Back to top
Profile PM
Thraxas
Retired Moderator
19
Years of Service
User Offline
Joined: 8th Feb 2006
Location: The Avenging Axe, Turai
Posted: 4th Mar 2012 05:25
Link
I've used AceMoney for years to keep track of my personal finances.

http://thraxocorp.webs.com/ Visit my totally awesome website: Thraxocorp. It's my own company and I'm totes the CEO.
Back to top
Profile PM Email
Fallout3fan
16
Years of Service
User Offline
Joined: 9th May 2009
Location:
Posted: 4th Mar 2012 06:18
Link
For a second I thought it was a trillion dollars for one item. By then I was like what? Oh!

_!!!!_
,0~U -Well I do say, its been quite a fancy forum for
__-____TheZachadoodle.________________________________
Back to top
Profile PM Email
Back to top

Login to post a reply

Server time is: 2025-05-19 15:58:37
Your offset time is: 2025-05-19 15:58:37