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

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.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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
>
>



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.
>
>
>
>
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

thats how i loaded the price list to start with. The problems with
sales orders are entered and the automatic pricing kicks in ( the
discounts are calculated * the number or pieces ordered) it goes to
down the tubes.

I could just rewrite the pricing stored procedures to call a rounding
procedure that would make the results agree with stupid excel :-\

Not the preferred way but it would make data entry people leave me
alone. Thanks for the idea.


Christophe wrote:
> Rather than try to recreate Excel's rounding algorithm, perhaps use
> Excel to create a table of input values and results, and load that
> into the database? It might be easier than trying to back-engineer
> Excel's broken math.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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)?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Justin wrote:

> 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.

`double' in C++ refers to double precision floating point. `double' is
subject to all the usual fun with rational decimals being irrational
binary floats (and vice versa).

One of the reasons I chose Java for my current work is that it has a
built-in decimal type (like `numeric') called BigDecimal . This makes
working with exact quantities a lot easier as there's no conversion and
rounding occurring each time data goes to/from the database.

Are there any particular decimal/numeric libraries people here like to
use with C++ ? Or do you just use double precision floats and a good
deal of caution?

I'd expect that using double would be OK so long as the scale of your
numeric values never approaches the floating point precision limit of
the double type. I'm far from sure about that, though, and it'd be handy
to hear from people who're doing it. Personally I like to stick to
numeric/decimal types.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Craig Ringer wrote:
> Justin wrote:
>
>> 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.
>
> `double' in C++ refers to double precision floating point. `double' is
> subject to all the usual fun with rational decimals being irrational
> binary floats (and vice versa).
>
> One of the reasons I chose Java for my current work is that it has a
> built-in decimal type (like `numeric') called BigDecimal . This makes
> working with exact quantities a lot easier as there's no conversion
> and rounding occurring each time data goes to/from the database.
Not according to MS specific if i'm reading it correctly

*Microsoft Specific >*

The double type contains 64 bits: 1 for sign, 11 for the exponent, and
52 for the mantissa. Its range is +/--1.7E308 with at least 15 digits of
precision
>
> Are there any particular decimal/numeric libraries people here like to
> use with C++ ? Or do you just use double precision floats and a good
> deal of caution?
>
> I'd expect that using double would be OK so long as the scale of your
> numeric values never approaches the floating point precision limit of
> the double type. I'm far from sure about that, though, and it'd be
> handy to hear from people who're doing it. Personally I like to stick
> to numeric/decimal types.
>
> --
> Craig Ringer

As i'm playing around with rounding and the numeric field precision ran
into a odd set of results i don't understand

here is the sql i wrote the first four inserts are calculations we run
everyday and they make sense but if division is used the results are
not right or am i missing something

create table test_num (
num1 numeric(20,1),
num2 numeric(20,2),
num3 numeric(20,3),
num4 numeric(20,4),
num5 numeric(20,5),
num6 numeric(20,6),
num7 numeric(20,7),
num8 numeric(20,8),
num9 numeric(20,9));

delete from test_num;

insert into test_num values ( (0.70 *1.05), (0.70 *1.05), (0.70 *1.05),
(0.70 *1.05), (0.70 *1.05), (0.70 *1.05),
(0.70 *1.05), (0.70 *1.05), (0.70 *1.05));

insert into test_num values ( (0.709 *1.05), (0.709 *1.05), (0.709 *1.05),
(0.709 *1.05), (0.709 *1.05), (0.709 *1.05),
(0.709 *1.05), (0.709 *1.05), (0.709 *1.05));

insert into test_num values( (.5/.03), (.5/.3), (.5/3),
(.5/30), (.5/300), (.5/3000),
(.5/30000), (.5/30000), (.5/30000));


insert into test_num values( (.5/.03)*.9975, (.5/.3)*.9975, (.5/3)*.9975,
(.5/30)*.9975, (.5/300)*.9975, (.5/3000)*.9975,
(.5/30000)*.9975, (.5/30000)*.9975, (.5/30000)*.9975);

insert into test_num values( (9*.1),
(9*.01),
(9*.001),
(9*.0001),
(9*.00001),
(9*.000001),
(9*.0000001),
(9*.00000001),
(9*.000000001));

insert into test_num values ( (9/10),
(9/100),
(9/1000),
(9/10000),
(9/100000),
(9/1000000),
(9/10000000),
(9/100000000),
(9/1000000000));

insert into test_num values( (1*.1),
(1*.01),
(1*.001),
(1*.0001),
(1*.00001),
(1*.000001),
(1*.0000001),
(1*.00000001),
(1*.000000001));

insert into test_num values ( (1/10),
(1/100),
(1/1000),
(1/10000),
(1/100000),
(1/1000000),
(1/10000000),
(1/100000000),
(1/1000000000));

select * from test_num ;



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general