On 2008-05-12 20:49, Justin wrote:

> We take (List Price * discount Percent) * Number of Pieces = net
> price.

This is wrong. You should do in Excel:
( price * amount ) * discount
As otherwise any small error in representation of price*discount would
be multiplied by usually high amount.

I'd do this way to get it right:
round( round(price * amount, 2) * discount, 2)
This way every sum should match. There could be errors, but in pennies,
not dollars. These errors will be because Excel does not have a decimal
type.

It is a lost case to break Postgres so it will match Excel. Much easier
would be to correct Excel spreadsheet. And this is a right thing to do.

Regards
Tometzky
--
....although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh


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

Tomasz Ostrowski wrote:
> On 2008-05-12 20:49, Justin wrote:
>
>
>> We take (List Price * discount Percent) * Number of Pieces = net
>> price.
>>
>
> This is wrong. You should do in Excel:
> ( price * amount ) * discount
> As otherwise any small error in representation of price*discount would
> be multiplied by usually high amount.
>

Your saying in Excel, Multiplication is not Commutativity??? that
sends shudders down my back
> I'd do this way to get it right:
> round( round(price * amount, 2) * discount, 2)
> This way every sum should match. There could be errors, but in pennies,
> not dollars. These errors will be because Excel does not have a decimal
> type.
>
That makes sense you are keeping the precision the same through the
calculation
> It is a lost case to break Postgres so it will match Excel. Much easier
> would be to correct Excel spreadsheet. And this is a right thing to do.
>
> Regards
> Tometzky
>

2008/5/13 Justin <justin@emproshunts.com>:

> Your saying in Excel, Multiplication is not Commutativity??? that sends
> shudders down my back

The word you want in this case is "associative". Since floating point
math is not exact, sometimes the associativity (and other) properties
of some operations are not preserved. This is true for floating point
in general, not just Excel.

-Doug

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

Thanks to Andy's C code here is the pl/pgSQL function which does the
same thing.

I added a tiny bit so instead of returning a numeric value with 20
trailing zeros it returns a value with the desired precision.


-----------------Begin Code ---------------------

create or replace function roundup(pToRound numeric, pPrecision
integer) returns numeric as
$Body$
declare
factor numeric ;
poweredup numeric;
trunced numeric;
fraction numeric ;
Begin
factor := power(10.0, pPrecision) ;
poweredup := pToRound * factor ;
trunced := trunc(poweredup);
fraction := poweredup - trunced;

if (fraction >= 0.5) then
return trunc(((trunced + 1)/factor), pPrecision);
end if ;
if (fraction <= -0.5) then
return trunc(((trunced - 1)/factor), pPrecision);
end if ;

return trunc((trunced/factor), pPrecision);
END;
$Body$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION roundup(pToRound numeric, pPrecision integer) owner to
postgres ;


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

Tomasz Ostrowski wrote:
> On 2008-05-13 17:29, Justin wrote:
>
>> Tomasz Ostrowski wrote:
>>
>>> On 2008-05-12 20:49, Justin wrote:
>>>
>>>
>>>
>>>> We take (List Price * discount Percent) * Number of Pieces = net
>>>> price.
>>>>
>>>>
>>> This is wrong. You should do in Excel:
>>> ( price * amount ) * discount
>>> As otherwise any small error in representation of price*discount would
>>> be multiplied by usually high amount.
>>>
>>>
>> Your saying in Excel, Multiplication is not Commutativity??? that
>> sends shudders down my back
>>
>
> On floats it is not in any language. Try this C program:
>
> #include <stdio.h>
>
> int main()
> {
> float a = 0.1;
> float b = 10;
> float c = 1000000000;
> float d = a * b;
> printf("%.10f\n", d*c);
> d = b * c;
> printf("%.10f\n", a*d);
> return 0;
> }
>
> On my Intel 32-bit I get:
> 1000000000.0000000000
> 1000000014.9011611938
>
I ran this on Windows using MSVC

The Result are the same when using float . But the Ms compiler throws
warnings, crying truncation and rounding problems
1000000000.0000000000
1000000014.9011612000

I change it to double problem goes away.
1000000000.0000000000
1000000000.0000000000

I have always avoided floating points. In all the work i have done thus
far the problems with floating math would cause scrap parts or make
Quality control worthless.

Accuracy trumps pretty much every concern around here.


> Pozdrawiam
> Tometzky
>

Double holds 15 places which is the highest value of precision it can
maintain before rounding occurs.

Is is limit less no, but what is?

Practically speaking taking a vale 0.000,000,000,000,001 aka
1 trillionth of anything, i view the problem solved for 98% of problems.

Does it hide? Yes, but it hides it very well. :-P

Tomasz Ostrowski wrote:
> On 2008-05-13 19:21, Justin wrote:
>
>> I change it to double problem goes away.
>
> No, it does not. It only hides it deeper. double is also a floating
> point type and has the same problems.
>
> Regards
> Tometzky

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

On Tue, May 13, 2008 at 02:36:18PM -0400, Justin wrote:
> Double holds 15 places which is the highest value of precision it can
> maintain before rounding occurs.
>
> Is is limit less no, but what is?
>
> Practically speaking taking a vale 0.000,000,000,000,001 aka
> 1 trillionth of anything,

But remember that if you add this value onto a large number and then
take off the large number the result will be zero.

(0.000,000,000,01 + 1,000,000) - 1,000,000 ==> 0
0.000,000,000,01 + (1,000,000 - 1,000,000) ==> 0.000,000,000,01

In general, operations on floating point numbers will increase their
errors.

> i view the problem solved for 98% of problems.

Floating point math is good for most problems, hence why most languages
expose the abstraction.


Sam

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

I have forgotten how much i hate C++

Its not doing what you say it would but it did do other odd ball
things. I miss my foxpro :-(.

Plus its not holding 15 precision points

#include <stdio.h>
#include <cmath>

int main()
{
double a = 0.1;
//double b = 1000;
double c = 100000000;
double d ; //= a * b;
for( int i = 1 ; i < 10 ; i++)
{
d = pow(a,i)+ c ;
printf("%.10f\n", d);
d = d-c ;
printf("%.10f\n", d);
}

return 0;
}

Sam Mason wrote:
> On Tue, May 13, 2008 at 02:36:18PM -0400, Justin wrote:
>
>> Double holds 15 places which is the highest value of precision it can
>> maintain before rounding occurs.
>>
>> Is is limit less no, but what is?
>>
>> Practically speaking taking a vale 0.000,000,000,000,001 aka
>> 1 trillionth of anything,
>>
>
> But remember that if you add this value onto a large number and then
> take off the large number the result will be zero.
>
> (0.000,000,000,01 + 1,000,000) - 1,000,000 ==> 0
> 0.000,000,000,01 + (1,000,000 - 1,000,000) ==> 0.000,000,000,01
>
> In general, operations on floating point numbers will increase their
> errors.
>
>
>> i view the problem solved for 98% of problems.
>>
>
> Floating point math is good for most problems, hence why most languages
> expose the abstraction.
>
>
> Sam
>
>

On Wed, May 14, 2008 at 11:47:52AM -0400, Justin wrote:
> I have forgotten how much i hate C++

What we're talking about doesn't have much to do with C++, it's floating
point maths in general.

> Its not doing what you say it would but it did do other odd ball
> things. I miss my foxpro :-(.

What does foxpro use for storing numbers? or is it just that you never
pushed it hard enough for the abstractions to show through.

> Plus its not holding 15 precision points

after changing the output to be:

printf("%.10f %.10f\n", d, d-c);

I get:

100000000.0999999940 0.0999999940
100000000.0100000054 0.0100000054
100000000.0010000020 0.0010000020
100000000.0001000017 0.0001000017
100000000.0000099987 0.0000099987
100000000.0000009984 0.0000009984
100000000.0000001043 0.0000001043
100000000.0000000149 0.0000000149
100000000.0000000000 0.0000000000

Which looks reasonable. Remember that floating point numbers store
their state in base two, not base ten. All of those numbers look good
to 15 decimal digits.


Sam

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

On Wed, May 14, 2008 at 02:08:47PM -0400, Justin wrote:
> Sam Mason wrote:
> >What does foxpro use for storing numbers? or is it just that you never
> >pushed it hard enough for the abstractions to show through.
>
> I know i pushed it. Foxpro for the most has only 4 basic data types
> Numeric (similar to Posgresql numeric), Boolean, Date, Text aka
> (string) The foxpro tables supported far more data types but when every
> it was dumped to variable it acted like one of the 4.

I really meant how much did you check the results, or did you accept
that they were correct?

> Foxpro did not suffer floating point math errors. I normally used 8 to
> 10 points precision. Foxpro was limited to 15 points of precision
> period. No more and no less, once you hit that was it.

15 places seems very similar to what a 64bit IEEE floating point number
will give you, i.e. a double in C/C++.

> My problem is we calculate resistance of parts in a Foxpro app that we
> want to move because we want to bring all the custom apps into one
> framework and single database.
>
> Take this calculation (0.05/30000* 1.0025) which is used to calculate
> parts resistance and Tolerance. (its Ohms Law) The value returned from
> C++ = .0000016708 which is wrong
> it should be .00000167418. We just shrank the tolerance on the part we
> make

Why are you so sure about the FoxPro result? I've just checked a few
calculators and get results consistent with your C++ version.

Justin C: 0.0000016708
J FoxPro: 0.00000167418
My C: 0.000001670833
bc[1]: 0.0000016708333333333333333333333333333332
PG[2]: 0.0000016708333333333333336675
Google[3]: 0.00000167083333 (actually gives 1.67083333e-6)

Both bc and Postgres use their own code (i.e. not the CPU's FPU) to do
the math, and as they all agree I'm thinking FoxPro is incorrect! Next
I tried doing it accurately (in Haskell if it makes any difference) and
get an answer of 401/240000000 out, which would agree with everything
but FoxPro. If I calculate the ratio back out for FoxPro I get
401/239520242 which is a little way out.

> The Documentation from MS says 15 points of precision but the result say
> otherwise.

The docs for what? FoxPro or their C compiler?

If you mean FoxPro, I think this is another case of MS screwing up.

> I'm glad You and others are taking the time to explain to me
> the odd results before i get into redoing that application.

Welcome to the PG community, lots of people to get interested in lots of
things!

> Why oh Why did MS kill Foxpro. :'( I understood it, knew its quirks
> and it worked very well with Postgresql

Are you sure you want to stay with it if its answers are wrong?


Sam

[1] http://www.gnu.org/software/bc/manual/html_mono/bc.html
[2] http://doxygen.postgresql.org/backen...8c-source.html
[3] http://www.google.com/search?q=0.05/30000*1.0025

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