Access doesn't round decimals correctly

[Ionel]

Hi. I was wondering if anyone knows why Access doesn't
round decimals correctly, for example 4.52 rounds it down
to 4.50; 7.56 rounds it up to 7.60. But when it comes
to numbers like 8.55, then it also rounds it down to 8.50,
instead of rounding it up.
Is this a bug in Access (2000 and 2003) ? ... because
Excel works fine, but there seems to be a problem in
Access, and there is no documentation in the knowledge
base whatsoever about this.

Thank you.

 

[Bob Richardson]

It seems that a number with 2 decimal places should round to one with less
than 2 places; i.e. 7.56 rounds UP to 7.6, not 7.60, otherwise there is no
rounding in the typical sense. If you're rounding to the nearest dime,
that's different.

If you really want to have your numbers rounded to 2 decimal places, I'd
look at the currency type.

Hi. I was wondering if anyone knows why Access doesn't
round decimals correctly, for example 4.52 rounds it down
to 4.50;

Shouldn't 4.52 round UP to 4.6

7.56 rounds it up to 7.60. But when it comes

 

[Roger Carlson]

Sure there is. I searched on "rounding" and the third article from the top
was this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210423&Product=acc2000

 

[Mike Painter]

Hi. I was wondering if anyone knows why Access doesn't
round decimals correctly, for example 4.52 rounds it down
to 4.50; 7.56 rounds it up to 7.60. But when it comes
to numbers like 8.55, then it also rounds it down to 8.50,
instead of rounding it up.
Is this a bug in Access (2000 and 2003) ? ... because
Excel works fine, but there seems to be a problem in
Access, and there is no documentation in the knowledge
base whatsoever about this.

Access uses what is called Bankers rounding. 8.65 would round to 8.70.

This can *never* be less precise and almost always be more precise than
always rounding 5 up (or down).
It is assumed and banks have historically found this to be correct that
about half the time the deciding digit will be even and odd the other half.

 

[Ionel]

What I meant was rounding from 7.52 down to 7.5 and from
7.58 up to 7.6. This works fine.
What doesn't work is rounding from .55 numbers (i.e.
7.55) This gets rounded DOWN to 7.5 instead of up to 7.6,
like it should.
Why?

 

[Mike Painter]

What I meant was rounding from 7.52 down to 7.5 and from
7.58 up to 7.6. This works fine.
What doesn't work is rounding from .55 numbers (i.e.
7.55) This gets rounded DOWN to 7.5 instead of up to 7.6,
like it should.
Why?

Because that how banking instutites have been doing it for hundreds of
years.
It is called bankers rounding.
7.x5 rounds to x if x is odd and to x+1 if x is even.

It is never less precise and usually more precise than always rounding "5"
the same way.

 

[Guest]

Yes, but what if you do want .5 to always round up? We give our exhibitors a
free badge allotment based on their square footage, and then have a
two-tiered fee system where they can get half of their allotment for one
price and half for a higher price once they've exhausted their free badges.
If their original allotment is odd (e.g. 5), we have always rounded the first
half up, so if thier original allotment is 5, they would get 3 more at the
lower price and 2 more at the higher price. In any case, we would need to
treat all of them the same, so "banker's rounding" isn't going to cut it.
Any ideas?

 

[John Vinson]

On Wed, 29 Sep 2004 08:35:29 -0700, "Amy Baggott" <Amy

Yes, but what if you do want .5 to always round up? We give our exhibitors a
free badge allotment based on their square footage, and then have a
two-tiered fee system where they can get half of their allotment for one
price and half for a higher price once they've exhausted their free badges.
If their original allotment is odd (e.g. 5), we have always rounded the first
half up, so if thier original allotment is 5, they would get 3 more at the
lower price and 2 more at the higher price. In any case, we would need to
treat all of them the same, so "banker's rounding" isn't going to cut it.
Any ideas?

Add 0.000001 before rounding.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi. I was wondering if anyone knows why Access doesn't
round decimals correctly, for example 4.52 rounds it down
to 4.50; 7.56 rounds it up to 7.60. But when it comes
to numbers like 8.55, then it also rounds it down to 8.50,
instead of rounding it up.
Is this a bug in Access (2000 and 2003) ? ... because
Excel works fine, but there seems to be a problem in
Access, and there is no documentation in the knowledge
base whatsoever about this.

Thank you.

 

[Guest]

I've had the same problem, I tried the Round function, but 33/8 still gives
4.12 instead of 4.13 that it should. Did you ever find a solution, I'm new to
the discussion group and I'm not sure how to find answers.
JAT

 

[Guest]

So what is the answer.
JAT

On Wed, 29 Sep 2004 08:35:29 -0700, "Amy Baggott" <Amy


Add 0.000001 before rounding.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I've had same problem as you, did you ever find out a solution?
JAT

 

[Joerg Ackermann]

So what is the answer.
JAT

Function fctRound(Optional varNr, Optional varPl As Integer = 2) As Double

If IsMissing(varNr) Or Not IsNumeric(varNr) Then Exit Function
fctRound = Fix("" & varNr * (10 ^ varPl) + Sgn(varNr) * 0.5) / (10 ^
varPl)
End Function



If you like to use Excel-functions add a Reference to Excel olb and:

Function RundZahl(a,b)
RundZahl=Excel.Application.Round(a, b)
End Function

(from the German Access-FAQ www.donkarl.com)

Joerg

 

[John Vinson]

I've had the same problem, I tried the Round function, but 33/8 still gives
4.12 instead of 4.13 that it should. Did you ever find a solution, I'm new to
the discussion group and I'm not sure how to find answers.
JAT

Access uses "Banker's Rounding".

You are apparently using the *CONVENTION* - and it's just a
convention, not a rule - that a 5 after the rounding position should
always round UP. That is, 4.12499 should round to 4.12, 4.12501 should
round to 4.13, and - by this convention - 4.125 should also round to
4.13.

There is a different (and some would argue) better convention: round
to the nearest *even* digit if there is a 5 after the rounding
position. That is, 4.125 -> 4.12; 4.115 also -> 4.12. The advantage of
this is that, on balance, the average of the rounded numbers will be
closer to the average of the unrounded values; your preferred
convention will make the average *larger*.

Asserting that "banker's rounding is wrong" is a rather narrow view of
the subject in other words!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

what if you do want .5 to always round up?

I can't reproduce:

CREATE TABLE ATest (
MyDecCol DECIMAL(15,1) NOT NULL
)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.52)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.58)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.55)
;
SELECT MyDecCol FROM ATest;

I get 7.5 for all three rows i.e. it always rounds down.

Jamie.

--

 

[Mike Painter]

I can't reproduce:

CREATE TABLE ATest (
MyDecCol DECIMAL(15,1) NOT NULL
)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.52)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.58)
;
INSERT INTO ATest
(MyDecCol) VALUES (7.55)
;
SELECT MyDecCol FROM ATest;

I get 7.5 for all three rows i.e. it always rounds down.

Jamie.

Access uses Bankers rounding. If you always round up (or down) on 5 then
your sums over a number of rows will be less precise than if you use the
method Access does.
These numbers appear as x.xx but are stored as x.mnxyzz. If the "x" is a
five, the "y" is used to determine if the n is rounded up or down.
The assumption is that the "y" value will be 0-4 half the time and 5-9 the
other half.
In the worst case the answer will *never* be less precise than rounding one
way and usually will be much closer to the correct answer.

Banks have had no problem with this for a few hundred years.

 

[Jamie Collins]

Access uses Bankers rounding.

I'm using a Jet 4.0 .mdb with the OLE DB Provider for Jet 4.0. I'm not
using MS Access.

If you always round up (or down) on 5 then
your sums over a number of rows will be less precise than if you use the
method Access does.

I'm not doing any rounding. Jet is truncating my values to one decimal
place.

These numbers appear as x.xx but are stored as x.mnxyzz. If the "x" is a
five, the "y" is used to determine if the n is rounded up or down.
The assumption is that the "y" value will be 0-4 half the time and 5-9 the
other half.

I'm not sure that is correct. I assume Jet is storing the values as
x.m. Did you try my code? Did you find that bankers rounding was
yielding a different result?

Jamie.

--

 

[Mike Painter]

Jamie Collins wrote:

I'm not using MS Access.

Sorry, I missed this. I assumed fron the subject line and the fact that this
is an Access forum you were asking about about Access.

 

[Jamie Collins]

Sorry, I missed this. I assumed fron the subject line and the fact that this
is an Access forum you were asking about about Access.

I assumed from the subject line and the fact that this is an MS Access
forum that we were discussing Jet's DECIMAL data type.

I'm using Jet via ADO and OLE DB. You're using Jet via the MS Access
UI. I say potato... You were involved in that 'Jet or MS Access'
thread last week

http://groups.google.com/[email protected]

so don't bail on me now <g>.

Are you telling me that 'decimal' means something else in the MS
Access UI? Which bears the question, is Jet's DECIMAL data type known
as something else in the MS Access UI?

Please clarify.

Many thanks,
Jamie.

--