Rounding to a multiple of 2, 5 or 9

KellyBailey · Jul 17, 2018

Hello,

Is there any way that I can use a formula to convert a number to a multiple of 2, 5 or 9?
I need it to round to the closest ideally.

For example:

252.9 = 252
255.8 = 255
260 = 259

Many thanks in advance

Finalfight40 · Jul 17, 2018

Can you use =MROUND?

Hendy2322 · Jul 17, 2018

If i understand correctly, you don't actually want the number to be converted to a multiple of 2, 5, or 9 because in your example (260 = 259), that is incorrect, the number would stay at 260 since it's a multiple of 2. So I'm assuming you want the last digit to change to either a 2, 5, or 9 depending on which direction is closer to the current number. Below is a formula I developed that will handle that. In the developer tab open up Visual Basic, click Insert->Module, then paste in the below code. Go back into the Excel and type in a cell "=RoundTo_2_5_9('Select Cell'). Let me know if this works for you.

Code:

Public Function RoundTo_2_5_9(ByVal dValue As Double) As Long
    
    Dim dRounded As Double
    Dim dTempValue As Double
    Dim iMinValue As Integer
    
    iMinValue = 10
    
    dRounded = Application.WorksheetFunction.RoundDown(dValue, -1)
    dTempValue = dValue - dRounded
    
    If dTempValue < 0.5 Then
        RoundTo_2_5_9 = dRounded - 1
    ElseIf dTempValue >= 7 Then
        RoundTo_2_5_9 = dRounded + 9
    ElseIf dTempValue < 7 And dTempValue >= 3.5 Then
        RoundTo_2_5_9 = dRounded + 5
    ElseIf dTempValue < 3.5 And dTempValue >= 0.5 Then
        RoundTo_2_5_9 = dRounded + 2
    End If
    
End Function

Hendy2322 · Jul 17, 2018

Sorry the above code works but I forgot to delelte the variable I no longer needed. Below is a shorter version of the code.

Code:

Public Function RoundTo_2_5_9(ByVal dValue As Double) As Long
    
    Dim dRounded As Double
    Dim dTempValue As Double
    
    dRounded = Application.WorksheetFunction.RoundDown(dValue, -1)
    dTempValue = dValue - dRounded
    
    If dTempValue < 0.5 Then
        RoundTo_2_5_9 = dRounded - 1
    ElseIf dTempValue >= 7 Then
        RoundTo_2_5_9 = dRounded + 9
    ElseIf dTempValue < 7 And dTempValue >= 3.5 Then
        RoundTo_2_5_9 = dRounded + 5
    ElseIf dTempValue < 3.5 And dTempValue >= 0.5 Then
        RoundTo_2_5_9 = dRounded + 2
    End If
    
End Function

shg · Jul 17, 2018

[table="width:, class:grid"][tr][td="bgcolor:#c0c0c0"][/td][td="bgcolor:#c0c0c0"]

a

[/td][td="bgcolor:#c0c0c0"]

b

[/td][td="bgcolor:#c0c0c0"]

c

[/td][/tr][tr][td="bgcolor:#c0c0c0"]

1

[/td][td]

250.00

[/td][td="bgcolor:#ccffcc"]

249.0

[/td][td="bgcolor:#ccffcc"]b1: =floor(a1, 10) + lookup(mod(a1, 10), {0,1,3.5,7}, {-1,2,5,9})[/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

2

[/td][td]

250.99

[/td][td="bgcolor:#ccffcc"]

249.0