forsythe303
New Member
- Joined
- Jan 24, 2010
- Messages
- 2
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.
Thanks for your indulgence and your assistance.
Jeff
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.
Thanks for your indulgence and your assistance.
Jeff
