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.
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'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 ;) ).
Regards,
Link.
