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