deviation in small numbers

[thread]

Hi all,
i'm uploading data from excel to access via excel object
i would like to know if there might be some deviation in the
calculations when the number is too small(for example 0.00012) so when
it multiply the amount by this figure it will round the small number

 

[Ron2006]

There is no problem in the maintaining and entering etc of small
numbers. BUT there is a significant difference in the rounding that
you will encounter between excel and access.

Do research on "Bankers Rounding" in this and other access related
groups and you will find a ton of information about it.

Ron

 

[thread]

i really think that there is some problem in the number when i'm
trying to multiply very small numer(0.0012) from the excel by some
amount before uploading it to access via object
can you please confirm that we are talking about the same thing?

Ron2006 כתב:

 

[thread]

i really think that there is some problem in the number when i'm
trying to multiply very small numer(0.0012) from the excel by some
amount before uploading it to access via object
can you please confirm that we are talking about the same thing?

Ron2006 כתב:

 

[thread]

i really think that there is some problem in the number when i'm
trying to multiply very small numer(0.0012) from the excel by some
amount before uploading it to access via object
can you please confirm that we are talking about the same thing?

Ron2006 כתב:

 

[Ron2006]

I am not sure where you are saying you have the problem.

If Access is doing the multiplication and rounding then the bankers
rounding is involved.



If you are doing the multiplication in excel and have a question about
the answer there then you had better ask the question in an Excel
group.

The only other thing I can think of is if you doing the multiplication
in excell and then importing the answer, is the definition of the
field in Access of the proper type to handle the answer. Will it have
the proper degree of accuracy. (for example don't use an integer to
try to hold a decimal value.)

Ron

 

[Jamie Collins]

If Access is doing the multiplication and rounding then the bankers
rounding is involved.

Universally, always?

Consider this quick example:

CREATE TABLE CurrencyVsDecimal (
cur_col CURRENCY,
dec_col DECIMAL(19,4)
)
;
INSERT INTO CurrencyVsDecimal (cur_col, dec_col)
VALUES (0.123456, 0.123456)
;
SELECT cur_col, dec_col
FROM CurrencyVsDecimal
;

returns (0.1235, 0.1234).

The value is rounded using banker's rounding for the CURRENCY column
but rounded using symmetric truncation** for the DECMIAL column.
Perfectly reasonable, predicable and expected, unless you hold the
false premise, Access = banker's rounding.

** You may consider truncation not to be a form of rounding but the
vendor does e.g.

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652
How To Implement Custom Rounding Procedures

"The simplest form of rounding is truncation"

Jamie.

--

 

[Ron2006]

I mis-phrased my answer relative to using an integer.

I was trying to point out that if you were dealing with money, for
instance, to make sure that all of the fields involved could handle a
decimal answer (probably). Don't expect the final value to have
dollars and cents but have it defined as an integer - or at least
don't be surprized if the cents are always 00.

Ron

 

[Jamie Collins]

I mis-phrased my answer relative to using an integer.

I was trying to point out that if you were dealing with money, for
instance, to make sure that all of the fields involved could handle a
decimal answer (probably). Don't expect the final value to have
dollars and cents but have it defined as an integer - or at least
don't be surprized if the cents are always 00.

For money data, your should additionally ensure you use an exact
numeric data type, rather than an approximate data type such as DOUBLE
FLOATING POINT. Ironically, in light of your reply, both Jet's exact
'decimal' numeric types, CURRENCY and DECIMAL, are persisted as scaled
integers <g>.

 

[thread]

Hi Ron,
thanks for your relay,
indeed im doing the multiplying via excel and then im uploading the
data to access,so its probbly the rounding banker that i will try to
see how i can overcome this problem if you can send me a specific link
regarding this matter i will be very thankfull

Ron2006 כתב:

 

[Ron2006]

Here are some references on "Banker's Rounding"



MS products' rounding explained:
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652


==================================
Have you tried the Round function in Access?
Round([Net] * [TaxRate] / 100 + .0001 , 2)
The additional .0001 compensates for the round to even functionality
included in the Access Round function. The round to even
functionality
causes Access to round .135 to .14 and .145 to .14 instead of the .15
that
you might expect.

============================================================

Try
= Int((NumberToRound * 100) + 0.5) / 100

Adding the 0.5 will round up any results where the 3rd decimal place
is 5 or
larger. This is the way sales tax is usually rounded and calculated.


--
Wayne Morgan
MS Access MVP

===========================================================

Further comments:

The built-in Round() function does "banker's" or "scientific"
rounding. It
will take .5 and round it to the nearest even number. This reduces
the
rounding error in calculations. 1 number doesn't move (.0), 4 round
down
(.1 - .4), 4 round up (.6 - .9), and one goes up half the time and
down half
the time (.5). However, this isn't the way most folks round and isn't
the
way sales tax is usually calculated. Instead, these are usually
calculated
by rounding .5 up all of the time. Adding 0.0001 won't fix this
problem,
0.0149 should round down and adding 0.0001 to it will change it to
0.015
which then will round up or down depending on where the even number
is, in
this case it would round to 0.02. Using the other equation, you get
01.49 +
0.5 for 01.99. Taking the Int() you truncate the 1.99 to get 1 then
divide
by 100 to get 0.01. This has rounded down as it should have.

--
Wayne Morgan
MS Access MVP

=========================================

And someone had this comment but I forgot to list who it was.

Changing the data type to currency may introduce other more subtle
rounding issues. The CURRENCY type rounds values using a banker's
rounding algorithm, which may not be appropriate to fabric
quantities.
The DECIMAL type truncates and therefore may be the way to go if the
aim truly is to eliminate rounding.

========================================

Ron

 

[Jamie Collins]

And someone had this comment but I forgot to list who it was.

Changing the data type to currency may introduce other more subtle
rounding issues. The CURRENCY type rounds values using a banker's
rounding algorithm, which may not be appropriate to fabric
quantities.
The DECIMAL type truncates and therefore may be the way to go if the
aim truly is to eliminate rounding.

That was me!

http://groups.google.co.uk/group/microsoft.public.access/msg/b055b671b938e247?hl=en&

Jamie.

--

 

[Ron2006]

That was me!

http://groups.google.co.uk/group/microsoft.public.access/msg/b055b671...

Jamie.

--

Jamie,

Thank you. I just updated my notes. (so I know who to blame... )

Ron