Rounding Numbers in Increments 1

[Tash]

Does anyone know if there is a way to round a calculated field to an even number? I found a "RoundToNearest" formula on the Microsoft support site, but it doesn't seem to work (I might be putting it in wrong). Any suggestions?

 

[leobaby]

I use this with no problems to round to 5. Of course the number should be a happy number before doing this.


Do Until intRoundedToFive Mod 5 = 0
intRoundedToFive = intRoundedToFive + 1
Loop


as usual, variable names have been changed to protect the inncocent.

 

[MichaelRed]

Review this procedure. It MAY be close to what you want. I'm not sure what you really mean by round to "even", and this is PROBABLY not the entire soloution.

Public Function basRnd2Even()

    Dim tmpRnd As Long

    For x = 1 To 10
        varnum = 1 + (Rnd * 10)
        tmpRnd = CInt(varnum - 0.5) + (CInt(varnum - 0.5) Mod 2)
        Debug.Print x, varnum, tmpRnd
    Next x

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Craig0201]

Try these.

They work identically to the same named functions in Excel

Function Round(Number As Double, Num_Digits As Integer)

'Set up variables
Dim dblNumNum_Digits As Double
Dim intNumNum_Digits As Integer

'Move decimal point such that int will perform at correct level
dblNumNum_Digits = Number * (10 ^ Num_Digits)

'Integerise
intNumNum_Digits = Int(dblNumNum_Digits)

'Compare integer to see if needs rounding up
If dblNumNum_Digits - intNumNum_Digits >= 0.5 Then
'round up
intNumNum_Digits = intNumNum_Digits + 1
Else
'round down
intNumNum_Digits = intNumNum_Digits
End If

'Return result, moving decimal point back
Round = intNumNum_Digits / (10 ^ Num_Digits)

End Function

Function MRound(Number As Double, Multiple As Double)

'Set up variables
Dim dblDivided As Double
Dim intDivided As Integer

'Divide
dblDivided = Number / Multiple
'Integerise
intDivided = Int(dblDivided)

'Round dblDivided to nearest whole number in intDivided
If dblDivided - intDivided >= 0.5 Then
intDivided = intDivided + 1
Else
intDivided = intDivided
End If

'Return result, returning to nearest multiple
MRound = intDivided * Multiple

End Function

 

[Tash]

Thanks for the tips! I'll try it out!

 

[MichaelRed]

Craig0201 - it is worth a star, although you could replace the if block with a round statement. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over