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
  • Last Modified:

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
Asked:
SpencerSteel
1 Solution
 
rettiseertCommented:
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
 
GrahamSkanRetiredCommented:
Try this function
Function NearestHalf(n As Single) As Single
    n = CInt(2 * n)
    NearestHalf = n / 2
End Function
0
 
Cem TürkSenior 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Cem TürkSenior Software EngineerCommented:
oh, GrahamSkan provided excellent code there!
0
 
SpencerSteelAuthor 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
 
GrahamSkanRetiredCommented:
Thanks Spencer and Cem Türk -  I'm too lazy to write anything longer.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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