Search Postgresql Archives

Re: rounding problems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





Lincoln Yeoh wrote:
At 01:48 AM 5/13/2008, 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 .

Bad news, the Excel thing is probably doing math very wrong.

Also, my guess is you're treating one penny as 0.01, which is also wrong.
The fields are numeric(12,4) and numeric(10,2) . I'm in process of extending the precision out on the acounting side because its causing problems with inventory costing, as we have raw material priced in $50 to $100 a pound but only consume .000235 lbs per part. so we can getting some funky results. I did not layout the database. The person who laid out the database knows even less math than i do, we have numeric fields (20,10) to (10,4) and everything in between. it creates some funky results due to truncating and rounding in the different fields. You have raw material priced as high as thing are today it starts adding up to some major issues. Multiply that by thousands of transactions it just way wrong.

I learned long ago make sure every field in the database have the same precision and deal with the rounding at the UI side. I learned this because of my work in low resistance measurements taken at the ppm scale.

When you do financial calculations you should avoid floating point where possible. Floating point is really tricky to get right. There are scary books on it.

I know this and experienced it before. Again someone did not know what they where doing and i got left picking up the pieces. Not to say my first time through i did not make all kind of mistakes but i fixed my.

To add further murky the water for the users our last ERP packaged used round to next highest number which trashed cost accounting as it used more raw material than it should have.


I'm no expert in financial calculations and floating point stuff, my _guess_ is a good start is probably treating one penny as 1, instead of 0.01. But better wait for the experts to chime in.

That said, if you're going to insist on using the wrong numbers from the Excel Invoice, can't you work some way of getting them into Postgresql and stored "as is", rather than having Postgresql calculate them differently ( I suspect you're using floating point in postgresql and so it'll be wrong too, just maybe a bit less wrong than Excel ;) ).

No floating point is being used every variable is declared as numeric on the Postgresql side and in the C++ which is the UI side everything is double.

Regards,
Link.







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux