Re: ROUNDING RESULT OF CALCULATION UP OR DOWN

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I had an issue once in a Life insurance calculation. In one of the calculation rules we had to round to a whole year. Each year
(of duration) had its own cost factor.
It turned out there were problems because one algorithm established the unrounded duration as 5.49999999999999999 years and the
other one as 5.5.
A whole year of difference after rounding, with a large impact on customer's revenues.

Just an example.

I keep repeating we need (optional) decimal arithmetic in Excel, like scaled integers in VB. There is no way the misinterpretation
I explained above could have come up in decimal arithmetic. Because we're all used to it and are more familiar with the pitfalls,
like repeating fractions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"James Silverton" <jim.silverton@xxxxxxxxxxx> wrote in message news:%23J99EdrXIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
| joeu2004 wrote on Thu, 24 Jan 2008 10:11:40 -0800 (PST):
|
| j> On Jan 24, 5:28 am, "James Silverton"
| j> <jim.silver...@xxxxxxxxxxx> wrote:
| ??>> Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:
| JWL>>> For instance, =ROUND(10^14+0.5,0)
| JWL>>> incorrectly returns the value of 100000000000000
| ??>>
| ??>> This is all very interesting but ultimately what does it
| ??>> matter if ROUND sometimes goes in the wromg direction at
| ??>> the limits of precision? It is hard for me to imagine a
| ??>> real case where so many figures are necessary but I am
| ??>> ready to be instructed.
|
| j> It isn't so much that anyone needs this much precision, but
| j> that these flaws cause problems in formulas that involve
| j> large numbers. The explanation is more precise than the
| j> solution requires.
|
| j> I presented this anomaly when I was having a problem with
| j> INT(). Actually, the problem is with MOD(x,y), which is
| j> well-know to an error with large numbers. I wanted to use x
| j> - y*INT(x/y) work around the problem. When that was
| j> misbehaving, I did a binary search and discovered the 10^14
| j> boundary and speculated the relationship to the number of
| j> significant digits that Excel will display.
|
| Again without wishing to start a fight, can you give me an
| example of a real calculation where the rounding error makes a
| difference to a conclusion? I probably wouldn't use Excel if
| there were such a case since it's not that hard to program in
| double precision or the like.
|
| James Silverton
| Potomac, Maryland
|
| E-mail, with obvious alterations:
| not.jim.silverton.at.verizon.not
|


.

Relevant Pages

  • RE: subtraction returning incorrect calue
    ... Then perhaps the Precision As Displayed option is the right thing for you. ... Then you might need ROUND only in formulas where you compare expression, ... make a backup copy of your Excel file first. ... But the point to note is: most decimal fractions cannot be represented ...
    (microsoft.public.excel.worksheet.functions)
  • =?ISO-8859-1?Q?Re=3A_I_don=B4t_view_the_value_right_after_six_decimal_?= =?ISO-8859-
    ... That still might have the same effect as COBOL decimal arithmetic. ... They are not identical in Excel 2003. ... You might consider setting the option Tools> Options> Calculation> ... Precision As Displayed. ...
    (microsoft.public.excel.misc)
  • RE: Why do I get rounding errors in Excel 2007 on empty cells?
    ... Excel and most other software stores numbers in binary format, ... Excel follows the IEEE standard for double precision ... You do not give an example calculation, so I will supply my own. ... When I increase the precision it seems i get rounding errors at ...
    (microsoft.public.excel.crashesgpfs)
  • Re: mod equation
    ... the MOD function in excel will not fail in any situation? ... calculation percentage increase etc. ... and you can determine the x ordinate to great precision. ...
    (microsoft.public.excel)
  • Re: calculated field problem
    ... if i have used the same formula in Excel i wouldnt use the round ... it comes to calculation of decimals like Discount * UnitPrice * ...
    (microsoft.public.access.formscoding)