I want to round Int to quarter
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 |
I want to round Int to quarter
=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 | |
I want to round Int to quarter
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 |
I want to round Int to quarter
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 |
All times are GMT +1. The time now is 03:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com