Solved

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

Posted on 2006-05-02
1,772 Views
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
Question by:SpencerSteel

LVL 13

Expert Comment

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

LVL 76

Accepted Solution

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

LVL 17

Expert Comment

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

LVL 17

Expert Comment

oh, GrahamSkan provided excellent code there!
0

Author Comment

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

LVL 76

Expert Comment

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

Featured Post

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…