Over the years I've noticed that summing (SUM) even 10 numbers can result in 567.0000000000002158736. Anyone have an answer?
I've verified the numbers have nothing past the decimal, or don't have anything past the 2nd position, yet I get crazy remainders/fractions.
Please explain!
Thanks,
BT
Unfortunately the computers concept of 10c (ie, 0.1) leaves much to be desired, and is, I believe, a repeating set of 3 zeros and 2 ones.Originally Posted by batommey
If your amounts had 10c, or a similar number, then your problem will occur.
I took to using =Int(A1*100)/100 as this was simple for me to understand, moreso on the comparisons than on the actual figurework, thus
=If(Int(A1*100)/100=Int(A2*100)/100,"eq","not")
gave me an accurate response to my balancing or otherwise.
Does this help?
---
Si fractum non sit, noli id reficere.
That is an interesting approach. Most of my numbers are at least two places past the decimal. I'm just really bothered that excel truly cannot add up a small set of numbers. This problem isn't unique to me. I've watched it for years, throughout many versions of the program.
I am looking for an easy answer to easy math. I have to share my files in the workplace and I can't have formulas that may confuse them.
Thanks very much for taking the time to respond,
BT
You say you have to share the files - do you mean that others read the values given, or input the values into other formulae? Because if it's the former, I recommend simply formatting the cell which the number's in as a number with 0 (or 1 or 2, as appropriate) decimal places. Then Excel will simply round the figure to the nearest value, whih as far as I can see is exactly what you want!
As for the problem, Bryan is exactly correct - it's just that it's simply not possible to truly express 1/10 as a binary decimal, as it were. It's highly annoying and one of many reasons 10 is a stupid base for us to use :D
Hi Aelyn, others use my files so they'll see the formulas. I found a simple example of how I can take two identical numbers and find a difference between them when using excel to subtract one from the other. This is because computers can't store numbers properly? I've been under the assumption that computers were a bit more precise. Please let me know what you think.
Thanks,
BT
PS...well, it doesn't look like I'm allowed to upload an excel file! Can I email it to you?
I guess for precision, one would ask how precise can you be when expressing 1/3 as a decimal. The word 'recurring' is hardly precision.
To upload your Excel.xls - in Explorer (My Computer) rightmouse the file and 'Add to Archive', rename that to a .zip
The file can then be uploaded.
hth
---
Thanks, here's the zipped file!
BT
The problem isn't exclusive to Excel, but is related to the way that computers store numbers that fall to the right of the decimal point.
The =Round(A1,4) should remove the 'insignificant' portion, but the 10c problem remains
Chip Pearson's site http://www.cpearson.com/excel/rounding.htm might help you.
note
Thanks Aelyn, I guess humans should have counted on their fingers and used their thumbs as pointers, rather than selecting all ten digits to use.
---
Last edited by Bryan Hessey; 11-03-2006 at 01:29 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks