---

MS Excel: How to create a custom round function

This Excel tutorial explains how to create a custom round function (with step-by-step instructions).

Question: In Microsoft Excel, I have an Overtime sheet for the employees. My problem is that while calculating the value, I need the following rules to apply:

If the fraction portion of the number is below 0.5, then I want to round the number down.
If the fraction portion is greater than or equal to 0.5, then the fraction should be 0.5.

For example, a value of 6.7 hours should display as 6.5.

Answer: To accomplish this, you need to create a custom function.

You'll need to open your Excel spreadsheet. Press Alt+F11 and create a new module.

Then paste into the new module the following function:

Function CustomRound(pValue As Double) As Double

   Dim LWhole As Long
   Dim LFraction As Double

   'Retrieve integer part of the number
   LWhole = Int(pValue)

   'Retrieve the fraction part of the number
   LFraction = pValue - LWhole

   If LFraction < 0.5 Then
      CustomRound = LWhole
   Else
      CustomRound = LWhole + 0.5
   End If

End Function

Now, when you want to round your values, you can use the round function as follows:

=CustomRound(6.7)
Result: 6.5

=CustomRound(6.5)
Result: 6.5

=CustomRound(6.49)
Result: 6.0