Incorrect formula result/ decimal places

[Guest]

I have a query in which I calculate the difference between two measurements,
Left and Right.

My formula reads Diff:

-

This should be simple enough, but I am getting strange results. For example:
0.3 - 0.2 = 0.100000008940697

My operators who enter the measurements have maximum of three digits past
the decimal place, so I do not know why I am getting this answer.

I would like the result of the formula to have three digits to the right of
the decimal and have formatted the column for this
(Properties-General-Decimal Places-3), but am getting the same results. I
even tried formatting the Left and Right columns the same way.

Does anyone have any suggestions on how I can correct this problem?​

 

[Tom Ellison]

Dear Quantum:

What problem? These are measurements. How accurate are they? When a
measurement is 0.3, just how accurate is it? Could it be 0.3002? Now, I
don't know what measuring tools you have, but I do know that measurements
are always subject to some small error.

So are floating point numbers in the computer. The computer's use of these
numbers is far more accurate than any measurements with which I'm familiar.
The computer will not distort your calculation anywhere nearly as great as
the errors introduced by your measurements.

Now, this is not to say you do not have a point. You need the answer
rounded for convenience to some reasonable degree, which you will want to
select yourself.

Use the Format function to produce rounded results to the number of decimal
places you want to see. For example:

Format(0.3 - 0.2, "####0.###")

This shows 0.1 for an answer. This is a string, and no longer numeric data.
But it is appropriate for display on the computer screen or a report.

Tom Ellison

 

[Guest]

Thank you for your help in formatting these answers. I am still confused as
to why additional numbers are in the answers, though.

Dear Quantum:

What problem? These are measurements. How accurate are they? When a
measurement is 0.3, just how accurate is it? Could it be 0.3002? Now, I
don't know what measuring tools you have, but I do know that measurements
are always subject to some small error.

So are floating point numbers in the computer. The computer's use of these
numbers is far more accurate than any measurements with which I'm familiar.
The computer will not distort your calculation anywhere nearly as great as
the errors introduced by your measurements.

Now, this is not to say you do not have a point. You need the answer
rounded for convenience to some reasonable degree, which you will want to
select yourself.

Use the Format function to produce rounded results to the number of decimal
places you want to see. For example:

Format(0.3 - 0.2, "####0.###")

This shows 0.1 for an answer. This is a string, and no longer numeric data.
But it is appropriate for display on the computer screen or a report.

Tom Ellison

I have a query in which I calculate the difference between two
measurements,
Left and Right.

My formula reads Diff:

-

This should be simple enough, but I am getting strange results. For
example:
0.3 - 0.2 = 0.100000008940697

My operators who enter the measurements have maximum of three digits past
the decimal place, so I do not know why I am getting this answer.

I would like the result of the formula to have three digits to the right
of
the decimal and have formatted the column for this
(Properties-General-Decimal Places-3), but am getting the same results. I
even tried formatting the Left and Right columns the same way.

Does anyone have any suggestions on how I can correct this problem?​

​

​

 

[Guest]

This is a huge problem for me. I have a fraction (Pct) where the denominator
has too many decimals. Then, I have subsequent multiplication calculations
off the fraction. But, because the fraction has too many decimals, ie
0.0000000732, subsequent calculation turns the decimal into a whole number,
ie 732. As a result, it does 9 * 732 instead of 9 * 0.0000000732.

Query1
Step1 - Total: [Jan]+[Feb]...[Dec]
[Total] is the sum of 12 months that gives several results in decimal places
instead of zero, such as 1.00 - 1.00 = 0.0000000555. This is not accurate and
is wrong.

Query1
Step2 - JulPct: IIf([Total]=0,0,[Jul]/[Total])
Because [Total] = 0.0000000555, this is where JulPct = 0.0000000732.

Query2
Step3 - JulEstimate: [Grand Total] * JulPct
Because [JulPct] = 0.0000000732, step 3 converts 0.0000000732 to 732. As a
result, I get [JulEstimate] = [Grand Total]*732 instead of [Grand Total]*0 or
even [Grand Total]*0.0000000732. This is an astronomical number in my
situation.

Round or Format does not work in each query. Int gives me an inaccurate
number.

Any other suggestions? - thanks

Dear Quantum:

What problem? These are measurements. How accurate are they? When a
measurement is 0.3, just how accurate is it? Could it be 0.3002? Now, I
don't know what measuring tools you have, but I do know that measurements
are always subject to some small error.

So are floating point numbers in the computer. The computer's use of these
numbers is far more accurate than any measurements with which I'm familiar.
The computer will not distort your calculation anywhere nearly as great as
the errors introduced by your measurements.

Now, this is not to say you do not have a point. You need the answer
rounded for convenience to some reasonable degree, which you will want to
select yourself.

Use the Format function to produce rounded results to the number of decimal
places you want to see. For example:

Format(0.3 - 0.2, "####0.###")

This shows 0.1 for an answer. This is a string, and no longer numeric data.
But it is appropriate for display on the computer screen or a report.

Tom Ellison

I have a query in which I calculate the difference between two
measurements,
Left and Right.

My formula reads Diff:

-

This should be simple enough, but I am getting strange results. For
example:
0.3 - 0.2 = 0.100000008940697

My operators who enter the measurements have maximum of three digits past
the decimal place, so I do not know why I am getting this answer.

I would like the result of the formula to have three digits to the right
of
the decimal and have formatted the column for this
(Properties-General-Decimal Places-3), but am getting the same results. I
even tried formatting the Left and Right columns the same way.

Does anyone have any suggestions on how I can correct this problem?​

​

​

 

[Jamie Collins]

This is a huge problem for me.

Which platform? I am using Jet 4.0 and am getting different results
from the OP:

SELECT 0.3 - 0.2 AS data_value,
TYPENAME(0.3 - 0.2) AS data_type
FROM Blah;

I get 0.1 and 'Decimal' respectively. Jet's DECIMAL data type is fixed
point, not floating point.

Rather than display, perhaps this is a storage issue e.g. the OP is
storing the result in a column of type FLOAT (Double) and should change
it to DECIMAL(n, 1).

Jamie.

--