Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
The spreadsheet was created from an Access query where the column is defined
as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
See http://www.cpearson.com/excel/rounding.htm for an excellent article on
the subject... "Chuck" wrote: The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
Thank You Sheeloo. I read the article but am not sure it applies to this issue.
When I expand the decimal places on the column to 30, there are only 0's after the second decimal place for each cell in the sum. But the sum has values other than 0 starting in decimal position 13 thru 24, then all 0s to position 30. I would thing there would need to be some value besides 0 in position 25 thru 30? "Sheeloo" wrote: See http://www.cpearson.com/excel/rounding.htm for an excellent article on the subject... "Chuck" wrote: The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
Chip's explanation applies, Chuck. You're adding in decimal, but computers
add in binary. Conversions from decimal to binary are imperfect, especially in decimal positions 13 to 24. If you want the sum to be exactly zero, you need to round your numbers. Regards, Fred. "Chuck" wrote in message ... Thank You Sheeloo. I read the article but am not sure it applies to this issue. When I expand the decimal places on the column to 30, there are only 0's after the second decimal place for each cell in the sum. But the sum has values other than 0 starting in decimal position 13 thru 24, then all 0s to position 30. I would thing there would need to be some value besides 0 in position 25 thru 30? "Sheeloo" wrote: See http://www.cpearson.com/excel/rounding.htm for an excellent article on the subject... "Chuck" wrote: The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
Often, programs do not test for perfect equality due to the
limitations of floating point representations. Instead, a difference less than some very small value is considered equal. E.g., Const MIN_NUM As Double = 0.00000000000001 Dim A As Double Dim B As Double ' A = some calculation ' B = some other calculation If A = B Then ' might have floating point problems End If ' instead use If Abs(A - B) < MIN_NUM Then ' close enough to be considered equal End If Some languages (e.g, C/C++) have the MIN_NUM value defined as part of the standard package for just this sort of thing. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 12 Dec 2008 16:19:47 -0600, "Fred Smith" wrote: Chip's explanation applies, Chuck. You're adding in decimal, but computers add in binary. Conversions from decimal to binary are imperfect, especially in decimal positions 13 to 24. If you want the sum to be exactly zero, you need to round your numbers. Regards, Fred. "Chuck" wrote in message ... Thank You Sheeloo. I read the article but am not sure it applies to this issue. When I expand the decimal places on the column to 30, there are only 0's after the second decimal place for each cell in the sum. But the sum has values other than 0 starting in decimal position 13 thru 24, then all 0s to position 30. I would thing there would need to be some value besides 0 in position 25 thru 30? "Sheeloo" wrote: See http://www.cpearson.com/excel/rounding.htm for an excellent article on the subject... "Chuck" wrote: The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
On Dec 12, 2:11 pm, Chuck wrote:
I read the article but am not sure it applies to this issue. Well, it does. But I can see how its relevance might be lost in its overwhelming detail. Try this pointer: http://support.microsoft.com/kb/214118 .. I think the first paragraph directly addresses your problem. When I expand the decimal places on the column to 30, [....] I would thing there would need to be some value besides 0 in position 25 thru 30? Well, there are. But Excel formats only the first 15 "significant digits" (i.e. not including leading zeros). I don't know exactly what your value is; but as an example, if I enter the 24-digit decimal fraction that you posted into an Excel cell, the exact internal representation is exactly -0.00000000000250110429875499,985154767000935027774 881358464398317664745263755321502685546875. (Note: I use the comma to denote 15 significant digits to the left.) The bottom line is: even for simple financial computations, it is prudent to make liberal use of the ROUND function. That will not eliminate the numerical "error" that is inherent in the representation of most decimal fractions in the standard binary computer format. But it will probably be sufficient for your purposes. Alternatively, you might use the Precision As Displayed calculation option. But I do not think that is a good approach. ----- original posting ----- On Dec 12, 2:11*pm, Chuck wrote: Thank You Sheeloo. I read the article but am not sure it applies to this issue. When I expand the decimal places on the column to 30, there are only 0's after the second decimal place for each cell in the sum. But the sum has values other than 0 starting in decimal position 13 thru 24, then all 0s to position 30. I would thing there would need to be some value besides 0 in position 25 thru 30? "Sheeloo" wrote: Seehttp://www.cpearson.com/excel/rounding.htmfor an excellent article on the subject... "Chuck" wrote: The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755.
You are the dictator of 250 000 people. Each one of them (happily) pay you this amount of dollars every minute, day and night, all year through. After X time you can buy a hot dog with your tax money. X is ? Question / problem is ? Best wishes Harald "Chuck" wrote in message ... The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
in 10 years you'll have $3.29 (rounded, of course)
unless my Excel is wrong... :) "Harald Staff" wrote: Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. You are the dictator of 250 000 people. Each one of them (happily) pay you this amount of dollars every minute, day and night, all year through. After X time you can buy a hot dog with your tax money. X is ? Question / problem is ? Best wishes Harald "Chuck" wrote in message ... The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum show difference of -0.000000000002501104298755
Wow, and who said computer guys were geeks, this could be auditions for the
comedy channel. FYI, the query in Access that creates the spreadsheet has Round(x,2) for the column. Then I tried =Round(E2,2) on the spreadsheet for all the rows and still get the hot dog in 3 years, which of course I'll share with my 250,000 subjects. "dlw" wrote: in 10 years you'll have $3.29 (rounded, of course) unless my Excel is wrong... :) "Harald Staff" wrote: Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. You are the dictator of 250 000 people. Each one of them (happily) pay you this amount of dollars every minute, day and night, all year through. After X time you can buy a hot dog with your tax money. X is ? Question / problem is ? Best wishes Harald "Chuck" wrote in message ... The spreadsheet was created from an Access query where the column is defined as double with 2 decimil places. In Excel I expand the column to 30 decimal places and none of the values have anything but 0 past the second decimal place. Yet when I =SUM(E1:E763) I get the total of -0.000000000002501104298755. I have tried changing the format in Excel to currency, general, accounting, no change? Any ideas would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show Difference as a Percentage | Excel Worksheet Functions | |||
Show difference between tow numbers as a + or - value. | Excel Discussion (Misc queries) | |||
show time difference | New Users to Excel | |||
Show % Difference between two numbers | Excel Discussion (Misc queries) | |||
what is the formula to show the difference between two dates | Excel Discussion (Misc queries) |