How to Round Number Down?

[HiBoo]

I've searched all the previous Questions regarding 'Rounding' and could not find an answer to my problem.

I have to calculate a number of points based on the difference between two sales totals. The points are calculated as follows...

For each $1000 difference in sales between current year and last year, 100 points are awarded.

So if the difference is...
$900 - Points = 0
$1000 - Points = 100
$1500 - Points = 100
$2200 - Points = 200 etc..

I'm running a report so I have to call a function. The function takes in the difference and returns the points. Any suggestions as to how I can calculate the points correctly? I have to round down the difference to ensure that for example, $999.99 does not round to $1000 and calculate 100 points. This is what I have so far but I'm not able to get it to round down.

Public Function GetPoints(Difference As Double) As Integer
    If Difference > 0 Then
        i = Difference / 1000
        If Not i < 1 Then
            i = FormatNumber(i, 0)
            i = i * 100
            GetPoints = i
        Else
            GetPoints = 0
        End If
    Else
        GetPoints = 0
    End If
End Function

 

[JoeMiller]

Try putting this function in a module:
[tt]
Function RoundCC(X)
RoundCC = Int(X * Factor + 0.5) / Factor
End Function
[/tt]
And put this Const at the top of that same module, right under Option Explicit
[tt]
Set Const Factor = 1
[/tt]
Now take your number and manipulate it using:

Round(MyNumber)

Not sure if it will work for your situation, but it may!
HTH Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Sorry!

RoundCC(MyNumber) Joe Miller
joe.miller@flotech.net

 

[HiBoo]

Thanks for your response Joe, actually I think I may have resolved my issue with...

Public Function GetPoints(Difference As Double) As Integer

    Dim result As Long
    
    result = Int(Difference)
    Difference = result
    

    If Difference > 0 Then
        i = Difference / 1000
        If Not i < 1 Then
            i = FormatNumber(i, 0)
            i = i * 100
            GetPoints = i
        Else
            GetPoints = 0
        End If
    Else
        GetPoints = 0
    End If
End Function

I'm going to try your version though to see if it runs quicker. Thanks a bunch!

 

[JohnYingling]

Integer division

Difference \ 1000 ' NOT /

http://WWW.VBCompare.Com