Floor rounding

[Jessica]

In Excel there is a function called Floor which allows me to round down to a
specified mulitple. Is there a way to duplicate this in Access? I have a
bunch of numbers that I want to use the floor function for and the nearest
mulitple is 5. For example anything from 0 to 4 would be returned as 0,
while 5-9 would be returned as 5, etc. Any help would be appreciated.

 

[strive4peace]

Hi Jessica,

try this:

([fieldname]\5) * 5

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Jessica]

Excellent, that works just fine. To make sure I am understanding what it
does, the forward slash takes the quotient part of what I was dividing and
then by mulitplying it by 5 I end up with the right bin for my numbers.
Thank you so much!

Jessica

Hi Jessica,

try this:

([fieldname]\5) * 5

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

In Excel there is a function called Floor which allows me to round down to a
specified mulitple. Is there a way to duplicate this in Access? I have a
bunch of numbers that I want to use the floor function for and the nearest
mulitple is 5. For example anything from 0 to 4 would be returned as 0,
while 5-9 would be returned as 5, etc. Any help would be appreciated.

 

[strive4peace]

Hi Jessica,

you're welcome

using the BACKslash to divide truncates the result to a whole number

21\5 = 4

then, you simply multiply the result by 5 to get 20 <smile>

~~~

if you want to simulate Ceiling, you would do this:

( [fieldname]\5 + 1) * 5

~~~

to remember the difference between forward slash and backslash: forward
slash, or simply slash, is the kind used in dates -- backslash is the
other one <smile> -- it is most often used in file paths

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Excellent, that works just fine. To make sure I am understanding what it
does, the forward slash takes the quotient part of what I was dividing and
then by mulitplying it by 5 I end up with the right bin for my numbers.
Thank you so much!

Jessica

Hi Jessica,

try this:

([fieldname]\5) * 5

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

In Excel there is a function called Floor which allows me to round down to a
specified mulitple. Is there a way to duplicate this in Access? I have a
bunch of numbers that I want to use the floor function for and the nearest
mulitple is 5. For example anything from 0 to 4 would be returned as 0,
while 5-9 would be returned as 5, etc. Any help would be appreciated.

 

[raskew via AccessMonster.com]

The \ is the equivalent of the Int() function.

Bob

Hi Jessica,

you're welcome

using the BACKslash to divide truncates the result to a whole number

21\5 = 4

then, you simply multiply the result by 5 to get 20 <smile>

~~~

if you want to simulate Ceiling, you would do this:

( [fieldname]\5 + 1) * 5

~~~

to remember the difference between forward slash and backslash: forward
slash, or simply slash, is the kind used in dates -- backslash is the
other one <smile> -- it is most often used in file paths

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Excellent, that works just fine. To make sure I am understanding what it
does, the forward slash takes the quotient part of what I was dividing and

[quoted text clipped - 27 lines]

 

[John Spencer]

"The \ is the equivalent of the Int() function." is not completely accurate.
For instance, I get these results in the immediate window.

?Int(51.502) --> 51
?51.502\1 --> 52

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County