Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
I would really like some help,
round(3348.86*.1,2) = 334.89 round(3348.86*.03,2) = 100.47 when added together 334.89+100.47 = 435.36 yet round(3348.86*.13,2) = 435.35 I beleive it is because 100.4658 is rounding to 100.47, when it should be 100.46. I really need these numbers to add up. If this is possible it would be great. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
Hi!
Actually, there is not a problem and Excel is calculating these properly. The difference is the result of the ROUND function. Not rounded: (to 15 decimal places) 3348.86*0.1=334.886000000000000 3348.86*0.03=100.465800000000000 Added together = 435.351800000000000 Biff "Bud" wrote in message ... I would really like some help, round(3348.86*.1,2) = 334.89 round(3348.86*.03,2) = 100.47 when added together 334.89+100.47 = 435.36 yet round(3348.86*.13,2) = 435.35 I beleive it is because 100.4658 is rounding to 100.47, when it should be 100.46. I really need these numbers to add up. If this is possible it would be great. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
Hi Bud I agree with Biff; Excel is correct in rounding 100.4658 up to 100.47 if using ROUND. If you specifically want to round down to 100.46 use ROUNDDOWN instead of ROUND. Stu -- stuarthow ------------------------------------------------------------------------ stuarthow's Profile: http://www.excelforum.com/member.php...o&userid=25417 View this thread: http://www.excelforum.com/showthread...hreadid=487849 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
Bud wrote:
round(3348.86*.1,2) = 334.89 round(3348.86*.03,2) = 100.47 when added together 334.89+100.47 = 435.36 yet round(3348.86*.13,2) = 435.35 This is a normal anomaly of rounding. It is one reason why the IRS, for example, suggests rounding only the result of a computation, not intermediate results. You do not need Excel to understand what is happening. Consider adding 1.4 + 2.4, round to an integer manually. If you round 1.4 and 2.4 first, your sum is 1+2 = 3. If you round only the result, your sum is 1.4 + 2.4 = 3.8, which rounds to 4. I beleive it is because 100.4658 is rounding to 100.47 Yes. Alternatively it is because 334.886 is rounded to 334.89. when it should be 100.46. Why do you believe that? No matter: your expectation is simply incorrect. I really need these numbers to add up. If this is possible it would be great. There is no solution where, in all cases, f(A+B) = f(A)+f(B), where "f" is any of the operations like INT, CEILING, ROUND, ROUNDUP or ROUNDDOWN. You need to accept that as a mathematical fact. To determine the right solution for you, you need to decide what your goals are. If you want an accurate final result, it might be best to round only the final computation. If you want the Excel computation to match what you would do manually based on intermediate values displayed in cells, you might want to round each intermediate result, accepting a small error in the final computation. (There is also an option to force this behavior for all spreadsheet compuations. See Tools Options Calculation Precision As Displayed.) Moreover, you might use ROUNDUP or ROUNDDOWN to maximize or minimize the error in the final computation in a particular direction. The choice is a tricky judgment call that requires a detailed understanding of the purpose of your computations. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unwanted rounding of large number | Excel Worksheet Functions | |||
Rounding numbers to the nearest 5 or 0 | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) | |||
Banker's Rounding - need help! | Excel Discussion (Misc queries) | |||
How do I make Excel stop rounding off my numbers that are 16 digi. | Excel Discussion (Misc queries) |