#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bud
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
stuarthow
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unwanted rounding of large number Candyman Excel Worksheet Functions 5 August 18th 05 12:32 AM
Rounding numbers to the nearest 5 or 0 Fieldmedic Excel Worksheet Functions 3 July 17th 05 06:51 AM
Rounding LaraHubbs Excel Discussion (Misc queries) 2 June 21st 05 09:42 PM
Banker's Rounding - need help! Somecallmejosh Excel Discussion (Misc queries) 3 January 20th 05 10:53 PM
How do I make Excel stop rounding off my numbers that are 16 digi. Aida Excel Discussion (Misc queries) 1 December 6th 04 05:34 PM


All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"