Rounding Down

Hey... is there a function that will round down? Here's my situation.

I have an variable nIndex which is an Integer. I have another called CurrentRow which is also an integer (this program uses excel, but this isn't an excel related question, so I'm keeping it here.

Now then, when CurrentRow is between 1-20, nIndex should = 0, for 21-40, it should equal 1, and so on.

The code I've used to do this is:

nIndex = CurrentRow / 20

However, when CurrentRow = 13, nIndex = 1. So it's rounding the 0.65 up to 1. Is there any way to get it to round down?

As far as I am aware, the round function is clever enough to do this for you:


Round Function


Description

Returns a number rounded to a specified number of decimal places.

Syntax

Round(expression [,numdecimalplaces])

The Round function syntax has these parts:

Part Description
expression Required.Numeric expression being rounded.
numdecimalplaces Optional. Number indicating how many places to the right of the decimal are included in the rounding. If omitted, integers are returned by the Round function.

You could force integer math:

nIndex = Currentrow \ 20

Sounds like you want Int() or Fix() (equivalent for positive numbers)
Either of these will just chop off digits after the decimal point, so
0.999 -> 0
1.000 -> 1

Diurnal: That works perfectly. Thanks! :)

correct me if im wrong but if Currentrow was to equal 20 then the result would be 1 using "nIndex = Currentrow \ 20". you need to minus 1 from the Currentrow first like this

nIndex = (Currentrow - 1) \ 20

correct me if im wrong but if Currentrow was to equal 20 then the result would be 1 using "nIndex = Currentrow \ 20". you need to minus 1 from the Currentrow first like this

nIndex = (Currentrow - 1) \ 20

Quite right. With

nIndex = Currentrow \ 20

nIndex would equal 0 for 0-19, 1 for 20-39, etc.

Try:nIndex = CurrentRow Mod 20

unclebill, using Mod will only work on two numbers, 20 and 21. any other number will be wrong.

You're right. I had tested a couple of things and was looking at the results of one and thought it was Mod. Blew that one bad!unclebill, using Mod will only work on two numbers, 20 and 21. any other number will be wrong.

Mod returns the remainder of a division.

0 mod 20 = 0
1 mod 20 = 1
...
19 mod 20 = 19
20 mod 20 = 0
21 mod 20 = 1

This is great for a "wrap-around" effect, but you will certainly run into problems if you replace integer division with modulus.

Mod returns the remainder of a division.

0 mod 20 = 0
1 mod 20 = 1
...
19 mod 20 = 19
20 mod 20 = 0
21 mod 20 = 1

This is great for a "wrap-around" effect, but you will certainly run into problems if you replace integer division with modulus.

Mod and Div are complementary in the sense that if you have one, you can construct the other - so, if you are in trapped in Bizarro World and all you HAVE is mod, you could always use


(x - (x mod 20)) / 20


to achieve integer division... :)

To force a number to round down:
Round(number - 0.5, 0)

To force a number to round up:
Round(number + 0.5, 0)

Hope this helps!

Int() also works for rounding down.

using Round() wont always work on whole numbers as it uses bankers rounding.

Int(number) 'down
-Int(-number) 'up

though none of these are any good for the original question :p