Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
ROUND(1000.2555-999,3)=1.255
the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
No, when rounding a value the next number in the figure is considered,
in this case, the 4th decimal (5). If that number is less than 5, then the result is rounded down, if it's 5 or more, then it's rounded up. So 1.2554 becomes 1.255 but 1.2555 becomes 1.256. If you want to keep the value without the rounding, use TRUNC instead: TRUNC(1000.2555-999,3) returns 1.255 Cheers. liaosheng wrote: ROUND(1000.2555-999,3)=1.255 the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
Oops, sorry. misunderstood your post. You are of course correct, it
should return 1.256. No idea why it doesn't. Cheers liaosheng wrote: ROUND(1000.2555-999,3)=1.255 the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
The value being rounded is:
1.255499999999980 Biff "liaosheng" wrote in message ... ROUND(1000.2555-999,3)=1.255 the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
=ROUNDUP(1000.2555-999,3)
mama no teeth "liaosheng" wrote: ROUND(1000.2555-999,3)=1.255 the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel error:ROUND(1000.2555-999,3)=1.255
Excel is performing exactly as it should here.
Excel and almost all other computer software does binary math. Most terminating decimal fractions (including .2555) are non-terminating binary fractions that can only be approximated (just as 1/3 can only be approximated as a decimal fraction). The precision of Excel's approximation is defined by the IEEE 754 standard, where the decimal representation of the best binary approximation to 1000.2555 is 1000.255499999999983629095368087291717529296875. It displays as 1000.2555 because (as documented in Help) Excel will display no more than 15 digits, but when you subtract 999, you correctly get a value that is visibly less than 1.2555, and which therefore rounds down. Since you know the operations that produced your 1.25549999999998, you know that ROUND(1000.2555-999,4) will do no violence to your calculations while cleaning up the result of the original binary approximations. That will produce a value that will round as you expected. Jerry "liaosheng" wrote: ROUND(1000.2555-999,3)=1.255 the correct result is 1.256,why? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |