2

I have to format some decimal values into specific logic. I would like to round input values always into either to 5 or 9. How can i accomplish that easiest way, is there any function already? Below find examples what i am trying to reach. On the left are some input values and on the right are output i would like to achieve. Is there any easy way to do so?

Input       Formatted
------      ----------
614,46      = 615,00
614,57      = 615,00
615,01      = 619,00
616,57      = 619,00
91,11       = 95,00 
96,11       = 99,00 
89,25       = 95,00
  • [Would this help at all?](http://stackoverflow.com/questions/326476/how-to-round-a-number-in-vba-to-the-nearest-5-or-10-or-x) – Ally Mar 21 '17 at 08:42

2 Answers2

2

There is nothing build in for your case but you can easily implement it by your own:

Public Sub Main()
    Console.WriteLine("{0:N2}", myFlor(614.46))
    Console.WriteLine("{0:N2}", myFlor(614.57))
    Console.WriteLine("{0:N2}", myFlor(615.01))
    Console.WriteLine("{0:N2}", myFlor(616.57))
    Console.WriteLine("{0:N2}", myFlor(91.11 ))
    Console.WriteLine("{0:N2}", myFlor(96.11 ))
    Console.WriteLine("{0:N2}", myFlor(89.25 ))
End Sub

Function myFlor(ByVal value As Double) As Double
    Dim base as Integer
    base =  Math.Truncate(value / 10) *10
    If value - base > 9
        ' Handle 9.01 -9.99 case
        Return base + 15
    ElseIf value - base > 5
        Return base + 9
    Else
        Return base + 5
    End If
End Function
klashar
  • 2,519
  • 2
  • 28
  • 38
  • Hi note that i use decimals and not double, can i just replace all your double types to decimals and it will works? –  Mar 21 '17 at 10:48
  • 1
    @Tony if you have `Option Strict On` it'll throw an error for implicitly converting the values. So changing to `Decimal` is a good idea and won't majorly affect results. See [Numeric Data Types](https://msdn.microsoft.com/en-us/library/ae55hdtk.aspx). – Ally Mar 21 '17 at 10:59
  • @AlexM. ok so i will just take this code and change Double to Decimal. Why there will be no diffrence simply saying its completly diffrent type... –  Mar 21 '17 at 11:14
  • @Tony there are slight variations between the types, but in your case it shouldn't affect you. – Ally Mar 21 '17 at 11:16
  • @AlexM. What about Tezzo code is it same just wrote diffrently or? –  Mar 21 '17 at 11:19
  • @Tony I think that's something you'll have to test for yourself rather than ask me about it ;-) – Ally Mar 21 '17 at 11:20
1

If I understand correctly you need to use Ceiling method to returns the smallest integer greater than or equal to the specified number and then round this integer to the nearest 5 or 9.

I don't think you can obtain this behaviour without writing your own function:

Private Function intRoundTo5Or9(ByVal dblNumber As Double) As Integer

    Dim intLastDigit As Integer = CInt(Math.Ceiling(dblNumber).ToString().Substring(Math.Ceiling(dblNumber).ToString().Length - 1, 1))

    If intLastDigit <= 5 Then
        Return Math.Ceiling(dblNumber) + 5 - intLastDigit
    Else
        Return Math.Ceiling(dblNumber) + 9 - intLastDigit
    End If

End Function
tezzo
  • 10,858
  • 1
  • 25
  • 48