Rounding the decimal number only (1 Viewer)

mohamedmatter

Registered User.
Local time
Today, 08:54
Joined
Oct 25, 2015
Messages
112
I want to Rounding the decimal number only as show in column b by condition use round function Attach sample file by request
 

Attachments

  • Rounding the decimal number only.xlsx
    9.7 KB · Views: 207

Gasman

Enthusiastic Amateur
Local time
Today, 15:54
Joined
Sep 21, 2011
Messages
14,044
Write your own function and use that instead of Round()
 

June7

AWF VIP
Local time
Today, 07:54
Joined
Mar 9, 2014
Messages
5,423
I am confused. What is wrong with Round() function? By what 'condition'?
 

mohamedmatter

Registered User.
Local time
Today, 08:54
Joined
Oct 25, 2015
Messages
112
I am confused. What is wrong with Round() function? By what 'condition'?
I want using functions no vba
my attach file
 

Attachments

  • Rounding the decimal number only2.xlsx
    12.3 KB · Views: 117

Minty

AWF VIP
Local time
Today, 15:54
Joined
Jul 26, 2013
Messages
10,354
It's not rounding per ser, it's conditional rounding by the look of it .
e.g. keep the .05 if it's exactly a .05 - I think you need a function.

I might have ten minutes tor knock one up ....
 

mohamedmatter

Registered User.
Local time
Today, 08:54
Joined
Oct 25, 2015
Messages
112
It's not rounding per ser, it's conditional rounding by the look of it .
e.g. keep the .05 if it's exactly a .05 - I think you need a function.

I might have ten minutes tor knock one up ....

yes my request in column B attach file
 

Minty

AWF VIP
Local time
Today, 15:54
Joined
Jul 26, 2013
Messages
10,354
This will do it.
Code:
Public Function fnRoundedWithHalfs(varNumber As Currency) As Currency
 [COLOR="SeaGreen"]   ' Returns either rounded up or down value unless its .05 in which case keep it.
    ' Will guarantee there is a more elegant way of doing this but I have a glass of red in my hand.
    ' ©  Minty @ AWF ;)
 [/COLOR]   
    Dim sNumber As String
    Dim iAddItBack As Currency
    Dim iWorking As Long
     
    sNumber = Int(varNumber * 100)
    'Debug.Print sNumber
    If Right(sNumber, 1) = "5" Then
        iAddItBack = 0.05
    End If
  
   [COLOR="SeaGreen"] ' Now round the original[/COLOR]
    iWorking = (varNumber * 10)
    'Debug.Print iWorking
        
    fnRoundedWithHalfs = (iWorking / 10) + iAddItBack
  

End Function

You can apply the same steps in Excel
 

mohamedmatter

Registered User.
Local time
Today, 08:54
Joined
Oct 25, 2015
Messages
112
This will do it.
Code:
Public Function fnRoundedWithHalfs(varNumber As Currency) As Currency
 [COLOR="SeaGreen"]   ' Returns either rounded up or down value unless its .05 in which case keep it.
    ' Will guarantee there is a more elegant way of doing this but I have a glass of red in my hand.
    ' ©  Minty @ AWF ;)
 [/COLOR]   
    Dim sNumber As String
    Dim iAddItBack As Currency
    Dim iWorking As Long
     
    sNumber = Int(varNumber * 100)
    'Debug.Print sNumber
    If Right(sNumber, 1) = "5" Then
        iAddItBack = 0.05
    End If
  
   [COLOR="SeaGreen"] ' Now round the original[/COLOR]
    iWorking = (varNumber * 10)
    'Debug.Print iWorking
        
    fnRoundedWithHalfs = (iWorking / 10) + iAddItBack
  

End Function

You can apply the same steps in Excel

thank you for your effort . I wanted to do this using formula
 

mohamedmatter

Registered User.
Local time
Today, 08:54
Joined
Oct 25, 2015
Messages
112
request in my image
 

Attachments

  • sshot-1.jpg
    sshot-1.jpg
    98.6 KB · Views: 175

June7

AWF VIP
Local time
Today, 07:54
Joined
Mar 9, 2014
Messages
5,423
Non-VBA:

=IF(RIGHT(TEXT(A2,"0.00"),1)="5",A2,ROUND(A2,1))
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:54
Joined
Jul 26, 2013
Messages
10,354
Then apply each step in the function to a formula in Excel :
 

Attachments

  • Rounding the decimal number only.xlsx
    17.2 KB · Views: 252

Users who are viewing this thread

Top Bottom