#mround

In this video, we round time to the nearest 15 minutes in Excel using a formula. In previous videos, we have grouped times using … source

New Post has been published on http://access-excel.tips/excel-access-mround-custom/

Excel Access MRound custom Function round to nearest multiple

This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple

Excel MRound Function

Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. For example, $11040 is rounded to $11000.

Access MRound Function

As of Access 2013, there is no Access MRound Function available. Even worse, Access Round Function uses round to even logic which is different from Excel Round Function. Therefore there is a need to create a custom Access MRound Function.

VBA Code – Excel Access MRound custom Function

Function wMRound(pValue As Double, multiple) As Double     Dim negNumber As Boolean     If pValue < 0 Then         pValue = 0 - pValue         negNumber = True     End If     If multiple < 0 Then         multiple = 0 - multiple     End If          Select Case pValue / multiple - Int(pValue / multiple)         Case Is < 0.5             result = Int(pValue / multiple) * multiple         Case Is >= 0.5             result = Int(pValue / multiple) * (multiple) + multiple     End Select              If negNumber = True Then         wMRound = 0 - result     Else         wMRound = result     End If End Function

Algorithm – Excel Access MRound custom Function

This custom MRound Function basically looks at the decimal part of remainder after dividing pValue by multiple. If the remainder is >=0.5, we need to round down the pValue to maximum multiple less than pValue, and then add a multiple to the number.

Unlike Excel MRound Function, this custom MRound Function allow parameters to input negative pValue but positive Multiple (which I think make more sense than using negative Multiple)

Syntax – Excel Access MRound custom Function

wMRound(pValue, multiple)

pValue The value to round Multiple Multiple to use in round

Although this Function can be used for Access and Excel, it is considered as mainly used for Access, because you can simply call the followings in Excel VBA

Application.WorksheetFunction.MRound

Example – Excel Access MRound custom Function

Formula Result Explanation =wmround(-1.1,1) -1 Allow negative number but positive multiple =wmround(-1.2,-1) -1 Allow negative multiple, which will be regarded as positive =wmround(-1.3,0.5) -1.5 Allow decimal multiple =wmround(-1.5,0.5) -1.5 Return same value if remainder is 0

Outbound References

New Post has been published on http://access-excel.tips/excel-mround-function-multiple/

Excel MRound Function round to nearest multiple

This tutorial explains how to use Excel MRound Function round to nearest multiple

Excel MRound Function round to nearest multiple

Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we don’t want the bonus amount looks better. For example, $11040 is rounded to $11000.

If you fail to use Excel MRound Function, navigate to Developer > Add-Ins > enable Analysis TooPak

Syntax of Excel MRound Function – round to nearest multiple

MROUND(number, multiple)

Number The value to round Multiple Multiple to use in round, must be of the same sign as Number, otherise #NUM! will return

Note that the logic is same for positive and negative number. For example, Mround(0.5,1) = 1, MROUND(-0.5,-1) = -1

Example of Excel MRound Function – round to nearest multiple

Number =Round(cell,0) =Mround(cell,1) Explanation 0.1 0 0 0.2 0 0 0.3 0 0 0.4 0 0 0.5 1 1 Mround also round half up 0.6 1 1 0.7 1 1 0.8 1 1 0.9 1 1 1 1 1 -1.1 -1 #NUM! Multiple is positive but number is negative -1.2 -1 #NUM! -1.3 -1 #NUM! -1.4 -1 #NUM! -1.5 -2 #NUM! -1.6 -2 #NUM!

Workaround of Excel MRound Function – #NUM! due to different sign

To avoid error due to sign, use the Sign Function to synchronize the sign of Multiple with Number. Sign Function is to return a positive value as 1, negative value as -1.

Number =Mround(cell,1) =Mround(cell,sign(cell)*1) 0.1 0 0 0.2 0 0 0.3 0 0 0.4 0 0 0.5 1 1 0.6 1 1 0.7 1 1 0.8 1 1 0.9 1 1 1 1 1 -1.1 #NUM! -1 -1.2 #NUM! -1 -1.3 #NUM! -1 -1.4 #NUM! -1 -1.5 #NUM! -2 -1.6 #NUM! -2

Outbound References