I have a column of numbers: 4.29, 5.79, 6.34, 7.89 and so on

I got a CustomRound function but it doesn't work right. I want to be
able to round the above numbers to the nearest quarter. I suspect that
either I need to use a case statement or more If Then statements. Can
anyone point me in the right direction.

Thank you, Mark

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 franction part of the number
LFraction = pValue - LWhole

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


End Function

=MROUND(A1,0.25)

If you get a #NAME error; ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"QUESTION-MARK" wrote in message ps.com...
|I have a column of numbers: 4.29, 5.79, 6.34, 7.89 and so on
|
| I got a CustomRound function but it doesn't work right. I want to be
| able to round the above numbers to the nearest quarter. I suspect that
| either I need to use a case statement or more If Then statements. Can
| anyone point me in the right direction.
|
| Thank you, Mark
|
| 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 franction part of the number
| LFraction = pValue - LWhole
|
| If LFraction < 0.5 Then
| CustomRound = LWhole
| Else
| CustomRound = LWhole + 0.5
| End If
|
|
| End Function
|

Mark,

No need for a custom User-Defined-Function.

For a column of numbers starting in cell A2, use a formula like this in another column

=ROUND(A2*4,0)/4

and copy down to match your data. You can replace your original data with the value by using the
pastespecial values feature.

HTH,
Bernie
MS Excel MVP


"QUESTION-MARK" wrote in message
ps.com...
I have a column of numbers: 4.29, 5.79, 6.34, 7.89 and so on

I got a CustomRound function but it doesn't work right. I want to be
able to round the above numbers to the nearest quarter. I suspect that
either I need to use a case statement or more If Then statements. Can
anyone point me in the right direction.

Thank you, Mark

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 franction part of the number
LFraction = pValue - LWhole

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


End Function

Thank you to everyone who replied!!! Both work great!!

Mark


Bernie Deitrick wrote:
Mark,

No need for a custom User-Defined-Function.

For a column of numbers starting in cell A2, use a formula like this in another column

=ROUND(A2*4,0)/4

and copy down to match your data. You can replace your original data with the value by using the
pastespecial values feature.

HTH,
Bernie
MS Excel MVP


"QUESTION-MARK" wrote in message
ps.com...
I have a column of numbers: 4.29, 5.79, 6.34, 7.89 and so on

I got a CustomRound function but it doesn't work right. I want to be
able to round the above numbers to the nearest quarter. I suspect that
either I need to use a case statement or more If Then statements. Can
anyone point me in the right direction.

Thank you, Mark

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 franction part of the number
LFraction = pValue - LWhole

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


End Function