Excel sum() function adding precision where there is none

forsythe303

New Member
I have a worksheet which is behaving oddly. The sum() function is adding together numbers which are all formatted as "Number" with 2 decimal places and it giving a result which looks correct with 2 decimal places, but it is actually putting a numeral one (1) in the eleventh decimal place.

In other words, when I perform a test =if(C5<>D5, ...true..., ...false...)
the values do not equal one another because of the addition of the unwanted precision.

4738.92 does in fact not equal 4738.92000000001 on that much Excel and I can agree. But Excel added the 1 way out there using its sum() function. All numbers being used by the sum() function (in fact all in the entire workBOOK are formatted as number and have only 2 decimals. All data is entered manually - none is derrived using a formula or linked in from another worksheet or other data source. None contains an apostrophe, so why is Excel doing this and how do I get it to stop.

This happens on:
Excel 2003 running on XP SP3
Excel 2007 running on XP SP3
Excel 2010 Beta running on Vista Home Premium

But oddly enough, when tested on OpenOffice.org's Calc, the precision is not introduced. It just gives the correct answer every time.

Other Background Info & Odd Behavior (if you care)
There's another couple really strange things happening with the worksheet as well (directly related to this precision). The worksheet consists of 14 groups of numbers (of up to 11 values in each group). The numbers are all listed in a single column (D) and each group is separated by a blank row. Each group can have from one to 11 values in it (this varries from group to group. So the sum() function placed into D5 is as follows: =sum(D7:D174) which will work, even if every group has 11 values in it. This also accounts for the blank cells between each group, but I've even removed them to see if they cause a problem and they did not. I also modified the sum function arguments to include only that range which actually contained data (ending with D95 - and an even lower number than that when all blank cells were removed). The precision was still introduced.

More Oddities
When I remove all of the numbers in a particular group (actually, several groups behave this way), the precision is removed. Example: when all values in Group 3 are removed, the precision goes away. Of course, so do the valid results, because I need to sum the values of Group 3 along with the others. And like I said, this happends with other groups as well, but not all of them.

Really Really Odd
when I added a column (C) and subtotaled each group individually and then summed all subtotals (in C5), the total in C5 is accurate with no precision being added. This is the time when the music from Twighlight Zone should begin to play.

Really Really Really Odd
When I remove only certain values or combinations of values in certain groups, the precision is taken away. So for instance, I can remove the first value (or second or third) in Group 1 and the precision is taken away. There are values like this in each of the groups, which when the group is removed in its entirety do not remove the precision. That is, removing all of Group 1 removes the precision and removing any one of the first three values without removing all of Group 1 does the same thing. But what's stranger than this? Removing the first and second values together does not remove the precision, but removing the first and third or second and third does remove the precision.

Absolutely Out of this World Odd
There are values (three to be exact), which when removed from their respective groups, will remove the precision. All three must be removed for the precision to be gone. None of these values is in a group which removes it when it is removed. And then there is the pattern this relates to.

"Really" Raised to Infinity Odd
Another odd behavior and one in which I accidentally stumbled upon is there seems to be a pattern of odd behavior. When I enter a particular value in one of those three cells mentioned above, the precision is put on (that is to say, the 1 is put in the eleventh decimal place). When I put another value in that same spot, the precision is removed and I get the value I expect (correct to the second decimal). There is a patter among those values which work and those which do not.

For instance, if the first value which does not work properly (adds the precision I do not want) is .01, then the next value which will not work is .02. The next is .05, followed by .06, .10, .14, .18, .22, .26, and .27.

How do I know this? Dumb luck that I stumbled upon it, but trial and error and a lot of sample data. In each case in the worksheet where a value does not work (all three of these freakish values), it follows a pattern where the difference between a value which does not work and the next value which does not work goes like this: .01, .03, .01, .04, .04, .04, .04, .04, and then repeats (.01, .02, ...).

So using the values above, the first number which does not work is .01 and there is a difference between it and the next one which does not work of .01. That means the second number which does not work is .02. The difference between that and the third one which adds the unwanted precision is .03 so the third number is .05 (.02 + .03). Following the pattern we get our to .27 (as mentioned above). The next number which would add the unwanted precision is .30 (we have already begun the repeat of the first .01 between .26 & .27, so now we have to add .03 to the number .27 and we end up with .30). This pattern repeats over and over again. Why? I haven't the foggiest notion and am hoping someone has experienced something similar. If this is some brand new discovery, I'll happily call it the Forsythe Difference Progression (FDP) when I write my book. If you've seen this before, please ignore my tangent and help me figure it out.

Now I know this is a lot of information, but I wanted to be sure to give anyone and everyone interested in helping with this ALL the information I have. I have worked on this until I am blue in the face, but not quite yet a Smurf. I have not yet uttered the words, "let's move this application to OpenOffice where it works." I'm not giving up just yet.

Any ideas?

This being my first post ever, I know, you're hoping my last if I keep writing books like this, I don't know if I can upload my workbook for others to view. I promise to check into this and upload it if I can. It has a better description of these problems.

Jeff

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

forsythe303

New Member
Thank you very much. I don't have time to read the article this AM, but have it pulled up to read tonight. I am disappointed that OpenOffice can do it and no version of Excel seems to be able to, but I do remember number conversion all too well, so I guess I'll have to figure out a work-around. I can use one of the round functions, but was hoping not to have to do so. It will just add to the documentation effort.

Thanks again

Jeff

pgc01

MrExcel MVP
I think that most programs that do calculations use the IEEE specification and I find it strange that you say that OpenOffice is an exception. The exceptions are usually other type of programs like programs for advanced mathematics or theoretical physics, like Mathematica.

I don't use OpenOffice, however, and so I'm sure you are more competent than I about the details on its implementation.

From what I see in the OpenOffice specification (at www.openoffice.org) the type with most precision, the double, follows the IEEE specification like excel:

http://api.openoffice.org/docs/common/ref/com/sun/star/rendering/FloatingPointBitmapFormat.html

Also I did a quick google search and I found this issues with OpenOffice that seem similar to the one you posted:

http://www.openoffice.org/issues/show_bug.cgi?id=69749
http://www.openoffice.org/issues/show_bug.cgi?id=78280

... there were many others

Conclusion: it seems you should have the same type of issues when you run OpenOffice, which are common to most programs that do calculations.
Are you sure you don't have some setting in your OpenOffice using for ex. only 10 or 12 significant digits instead of the maximum 15? This would mean that all calculations would be rounded and you'd get the results you expect.

As I said, I have no experience with OpenOffice and I may be reading this information in a wrong way. I'd appreciate if you would comment.

Replies
17
Views
252
Replies
5
Views
142
Replies
20
Views
363
Replies
4
Views
113
Replies
9
Views
672

1,141,406
Messages
5,706,269
Members
421,437
Latest member
GijoeBlack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back