Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1790

# VBA: Round up decimal number to 'half' or 'whole'

Hello,

I want to write a function that will round up a number to the nearest 'half' or a 'whole'

e.g.

3.231 becomes 3
3.251 becomes 3.5
3.670 becomes 3.5
3.780 becomes 4
4.123 becomes 4

Any ideas ?

S.S.

0
SpencerSteel
1 Solution

Commented:
This should work:

MsgBox RoundHalfOrWhole(3.231)
MsgBox RoundHalfOrWhole(3.251)
MsgBox RoundHalfOrWhole(3.67)
MsgBox RoundHalfOrWhole(3.78)
MsgBox RoundHalfOrWhole(4.123)

Function RoundHalfOrWhole(Number)

IntPart = Fix(Abs(Number))

diff = Abs(Number) - IntPart

If diff < 0.25 Then
RoundHalfOrWhole = IntPart
ElseIf diff >= 0.25 And diff < 0.75 Then
RoundHalfOrWhole = IntPart + 0.5
Else
RoundHalfOrWhole = IntPart + 1
End If

If Sgn(Number) = -1 Then
RoundHalfOrWhole = RoundHalfOrWhole * -1
End If

End Function
0

RetiredCommented:
Try this function
Function NearestHalf(n As Single) As Single
n = CInt(2 * n)
NearestHalf = n / 2
End Function
0

Senior Software EngineerCommented:
Private Function rn(number)
mynum = FormatNumber(number, 3)
cmp = 1 - (Int(mynum + 1) - mynum)
If cmp >= 0.75 Then
rn = mynum + 1 - cmp
ElseIf cmp < 0.75 And cmp >= 0.25 Then
rn = mynum - cmp + 0.5
Else
rn = mynum - cmp
End If
End Function
0

Senior Software EngineerCommented:
oh, GrahamSkan provided excellent code there!
0

Author Commented:
He did indeed ... it's pretty faultless !

Cem Truk / Reetiseert: I feel bad about that you went to so much hassle ... if it's any concellation, my solution was going to be quite long-winded like yours but I figured someone would have a much 'smarter' solution.

I was right :)
0

RetiredCommented:
Thanks Spencer and Cem Türk -  I'm too lazy to write anything longer.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.