Re: rounding problems - Mailing list pgsql-general

From Justin
Subject Re: rounding problems
Date
Msg-id 4828914C.2000304@emproshunts.com
Whole thread Raw
In response to Re: rounding problems  (Andy Anderson <aanderson@amherst.edu>)
Responses Re: rounding problems
List pgsql-general

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)?

Yes i'm talking 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.

We take (List Price * discount Percent) * Number of Pieces = net
price.    List Prices is stored as numeric (16,4) discount is stored as
numeric(10,4)
the result is numeric (16,4).  On the UI its rounded to 2 and displays
correctly and agrees with my TI-89

The problem is the price book which is used to quotes is almost always
0.01 to 0.015 pennies higher.  Net result the invoices are almost always
lower than Quoted price.  (yet customers still through a fit.)


>
> -- Andy
>
> On May 12, 2008, at 1:48 PM, Justin wrote:
>
>> I have very annoying problem that i would like to get a work around
>> in place so the data entry people stop trying to kill me.
>>
>> Normally people give quotes out of the price book which was done in
>> Excel like 15 years ago and just has been updated over the years.
>> the problem is excel is rounding differently than postgres 8.3.1 (Yes
>> i know Excel rounds incorrectly) which results in normally being
>> pennies off but on large qty its usually under a few bucks on the
>> postgresql side.   We internally don't  care but those annoying
>> customers scream bloody murder if the quote don't agree to the penny
>> on the invoice  Even when its to their benefit .
>> Has anyone every got  Postgresql and Excel to agree on rounding.
>> I have checked excel up to Office XP and its still wrong.  (open
>> office was looked out and the people  screamed really loudly NO )
>>
>> Another annoying thing is the calculators on everyones desk get it
>> wrong to if the rounding is turned to 2 places.
>> Although my TI-89, and TI-36X calculators agree perfectly with
>> postgresql .
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>



pgsql-general by date:

Previous
From: "Pavel Stehule"
Date:
Subject: Re: Returning RECORD from PGSQL without custom type?
Next
From: Justin
Date:
Subject: Re: rounding problems