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
