Re: rounding problems

> Andy Anderson wrote:
>> Can you be more explicit about the rounding that's wrong in Excel?
>> Are you talking about the ....n5 round-up to n+1 that Excel uses
>> vs. ....n5 round-to-even n (sometimes called Banker's Rounding)?

On May 12, 2008, at 2:38 PM, Justin wrote:
> Yes i'm taking about difference between bankers rounding verse
> Excels crappy math. I have dealt with excels crappy math skills
> in scientific measurements dumped from AD cards the simply solution
> was increase the decimal range to 1 more than i needed. But in
> this case it won't work sense this published material will disagree
> with how postgresql rounds.

Well, I won't call it crappy, just different; it depends on your
purpose. I learned round-even in grade school, but I've seen many
college students in the last two decades who learned round-up.
Microsoft actually explains these two and several other ways to
implement rounding on this page:

http://support.microsoft.com/kb/196652

(But they don't justify their choice for Excel, very odd given its
extensive financial use.)

Anyway, I would imagine you could implement a custom function to
replace Postgres' round(n, i) along the lines of:

function roundup(n, i)
{
factor = power(10.0, i);
nd = n * factor;
ni = trunc(nd);
fraction = nd - ni;
if (fraction >= 0.5)
return (ni + 1)/factor;
if (fraction <= -0.5)
return (ni - 1)/factor;
return ni/factor;
}

Apologies for using C and warnings that I haven't thoroughly tested
this.

P.S. You could also write a round-even function for Excel and get
them to use it on their next printout! :-)

-- Andy