round up and round down

[aspgk]

In Excel, there is a nifty roundup and rounddown function... i need to do
the same in Access... how can this be done.. i've tried the ..... +.5 stuff
and it doesn't work..

Please advise..

Paul

 

[Ofer Cohen]

The function Round will round up all numbers above 5, and down if number
under 5

Round(5.6) will get 6
Round(5.4) will get 5

Round(5.66,1) will get 5.7
Round(5.64,1) will get 5.6

Function Int will always round down

Int(8.9) will return 8

Function Format will do what Round will

Format(8.9,"#") will return 9

 

[Ed Metcalfe]

Ofer,

I thought the Access round function used banker's rounding, so that isn't
necessarily the case?

One simple way to use the Excel functions is to add a reference to the Excel
object library and then the Excel.Worksheetfunction.NameOfFunction in the
VBA window. This does come with a certain amount of overhead, but it's quick
and easy...

Ed Metcalfe.

 

[John W. Vinson]

I thought the Access round function used banker's rounding, so that isn't
necessarily the case?

It is. Banker's rounding applies only when the position being rounded is
exactly 5.

5.6 rounds to 6 (it's above .5)
5.4 rounds to 5 (it's below .5)
5.5 rounds to 6, but 4.5 rounds to 4 (.5 rounds to the nearest EVEN value, up
or down).

John W. Vinson [MVP]

 

[Ed Metcalfe]

John,

Of course. I can never remember exactly how banker's rounding works. I've
never reallly understood why MS went for that method, without also providing
a built in natural rounding function.

Ed Metcalfe.

 

[Tom Ellison]

Dear Ed:

LOL! Banker's rounding is a natural rounding function, for bankers!

Tom Ellison
Microsoft Access MVP

 

[John W. Vinson]

Of course. I can never remember exactly how banker's rounding works. I've
never reallly understood why MS went for that method, without also providing
a built in natural rounding function.

Banker's rounding is - in at least some sense - more rational than "always
round .5 down". The rationale is that with "always round down", the total or
average of an ensemble of numbers will *decrease* upon rounding. With banker's
rounding, it's unbiased - on average, the total (average) will remain
unchanged after rounding.

Just because it's what we both learned in fourth grade (or whenever) doesn't
make "always round down" any more "natural" than any other arbitrary
convention!

John W. Vinson [MVP]

 

[Ed Metcalfe]

Banker's rounding is - in at least some sense - more rational than "always
round .5 down". The rationale is that with "always round down", the total
or
average of an ensemble of numbers will *decrease* upon rounding. With
banker's
rounding, it's unbiased - on average, the total (average) will remain
unchanged after rounding.

Just because it's what we both learned in fourth grade (or whenever)
doesn't
make "always round down" any more "natural" than any other arbitrary
convention!

John W. Vinson [MVP]

John,

I understand the rationale behind the use of banker's rounding. It was more
the fact that bankers rounding is included in Access *without* also
providing the other method of rounding (always round 0.5 up) that most
people are familiar with.

Ed Metcalfe.