Rounding to nearest 1000

[Darrell]

I need to round to the nearest 1000. The Round function in Excel will do
this nicely (Round(Value, -3)). However, the Round function in Access
will deal only with the fractional portion of a number, and
correspondingly will accept only positive numbers as an argument.

Is there any way to do this in Access?

Thanks in advance for all help!

Darrell

 

[Ken Snell \(MVP\)]

CInt(YourNumber / 1000) * 1000

 

[Jason Martin]

Depends...

Ken's way will round 500 down to zero. The Round function in Excel to -3
would round 500 up to 1000. Glass half full or half empty?

Optionally, reference the Excel Object Library in VBA then call the Excel
function within Access. Example:

Excel.WorksheetFunction.Round(YourNumber, -3)

 

[Darrell]

CInt(YourNumber / 1000) * 1000

Thank you, Ken. The only catch is that, unless I misunderstand this, I'm
going to get the next LOWER 1000, rather than the NEAREST 1000. If
MyNumber is 3900, dividing by 1000 will give me 3.9. CInt will give me
3, and multiplying by 1000 will give me 3000. What I would like in this
case is 4000.

Darrell

 

[Darrell]

Depends...

Ken's way will round 500 down to zero. The Round function in Excel to -3
would round 500 up to 1000. Glass half full or half empty?

Optionally, reference the Excel Object Library in VBA then call the Excel
function within Access. Example:

Excel.WorksheetFunction.Round(YourNumber, -3)

That's what I thought. Only my understanding was that the CInt would
round even 900 to 0. Maybe I'm wrong?

Anyway, the calling of the Excel function worked perfectly! Thanks! This
was the first time I utilized another application's object model, and I
was wondering if there was a way to make use of that function. Thanks alot!

Darrell

 

[Gary Walter]

PMFBI

typical solution:

Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000

I think your concern is misplaced...

?CInt(3.900)
4
?CInt(2.900)
3

BUT, when the fractional part is exactly 0.5,
CInt and CLng always round to the nearest *even* number.

?CInt(3.500)
4
?CInt(2.500)
2 <---- I don't think what you want!!!

The Fix and Int functions *truncate*
the fractional part of a number.
A typical solution is to truncate
(the division + 0.5):

mynum=3900
?Int(mynum/1000 + 0.5) * 1000
4000

mynum=2500
?Int(mynum/1000 + 0.5) * 1000
3000

mynum=3400
?Int(mynum/1000 + 0.5) * 1000
3000

=================
NEGATIVE NUMBERS
=================

But...will you have any negative numbers?
If so, in what direction do you want to round?

mynum = -3400
?Int(mynum/1000 + 0.5) * 1000
-3000

mynum = -3900
?Int(mynum/1000 + 0.5) * 1000
-4000

mynum = -2500
?Int(mynum/1000 + 0.5) * 1000
-2000

Maybe the result above is okay, but
if you wanted -2500 to round to -3000,
use the "sgn()/sgn()" trick:

mynum = -2500
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
-3000

mynum = -3400
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
-3000

mynum = -2600
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
-3000

plus, positive numbers work unaffected just like above:

mynum=3900
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
4000

mynum=2500
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
3000

mynum=3400
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
3000

============
RANGE
============

Plus, Int always returns a value of the same type as is passed in.

integer -- -32,768 to 32,767
long -- -2,147,483,648 to 2,147,483,647

mynum = 2147483647
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
2147484000

mynum = 2147483500
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
2147484000

mynum = 2147482500
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
2147483000

mynum = 2147483499
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
2147483000

mynum = -2147482500
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
-2147483000

mynum = -2147483499
?Sgn(mynum)*Int(Sgn(mynum)*mynum /1000 + 0.5) * 1000
-2147483000

 

[giorgio rancati]

Hi Darrel

put this code in a bas module
----
Function MyRound(ByVal Number As Variant, Optional Dec As Integer = 2) As
Variant

Dim Result As Variant
Dim Nr As Variant

Nr = CDec(Nz(Number, 0))
Nr = Abs(Nr)

Result = Nr * 10 ^ Dec + 0.5
Result = Fix(Result) / 10 ^ Dec

MyRound = Result * Sgn(Nz(Number, 0))

End Function
----

it works same the excel round function

MyRound(3499,-3) = 3000
MyRound(3500,-3) = 4000
MyRound(-3499,-3) = -3000
MyRound(-3500,-3) = -4000

bye

 

[Ken Snell \(MVP\)]

Try it and you'll see .....

By the way, using another application's library, unless that application in
that version will always be available on the PC where your database is
running, is a problem waiting to happen because of broken references. It's
not usually a good idea to load up your application with such overhead for
such a simple function's use -- program your own or use the built-in ACCESS
VBA functions.

 

[John Spencer]

CInt is not the same as Int.
CInt rounds the number to the nearest whole number value (using bankers
rounding).
Int truncates the number - returns just the integer portion.

One change to Ken Snell's suggestion: I would use CLng since CInt is limited
to handling numbers in the range 32267 (plus or minus).

 

[Gary Walter]

Hi Ken,

Your point about Excel is well made,
but still, your solution would be limited to integer
input numbers or else will get overflow error....

gary

 

[Gary Walter]

Hi John,

Maybe irrelevant to poster,
but both will produce banker's rounding...

gary