Rounding to a multiple of 2, 5 or 9

[KellyBailey]

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]

Can you use =MROUND?

 

[Hendy2322]

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.

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]

Sorry the above code works but I forgot to delelte the variable I no longer needed. Below is a shorter version of the 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]

	a​	b​	c​
1​	250.00​	249.0​	b1: =floor(a1, 10) + lookup(mod(a1, 10), {0,1,3.5,7}, {-1,2,5,9})
2​	250.99​	249.0​
3​	251.00​	252.0​
4​	253.49​	252.0​
5​	253.50​	255.0​
6​	256.99​	255.0​
7​	257.00​	259.0​
8​	260.00​	259.0​

 

[KellyBailey]

Hello,

Thanks both. Unfortunately I couldnt get the VBA one to work, although my experience of this is very low, so I am sure it was probably that I was doing something wrong.

The formula works a treat - thank you.

Best wishes

Kelly