How to Separate Whole Number from Decimal

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

How to Separate Whole Number from Decimal

Post by jakjo »

Greetings to everyone
I have a UDF Function to calculate stamp duty and I am trying to Separate Whole Number from Decimal but couldn't come up with that
How to modify it to Separate Whole Number from Decimal as shown in the attachment .... Thanks in advance
You do not have the required permissions to view the files attached to this post.

User avatar
StuartR
Administrator
Posts: 12949
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: How to Separate Whole Number from Decimal

Post by StuartR »

You can do this by putting the appropriate formula in the destination cells?

For the whole number part
=INT(H3)
For the decimal part
=(H3-K3)*100
StuartR


User avatar
p45cal
2StarLounger
Posts: 176
Joined: 11 Jun 2012, 20:37

Re: How to Separate Whole Number from Decimal

Post by p45cal »

You could incorporate it into the UDF. In Excel 2010 you will probably need to array enter the formula into 2 adjacent cells. See attached cells M3:N4.
UDF:

Code: Select all

Function StampDutyTax(ByVal f As Double, ByVal m As Double) ' As Double
Dim p As Double, x, s As Double, m2 As Double

If f <= 50 Then
  StampDutyTax = 0
Else
  If f > 10000 Then
    m2 = 10000
  Else: m2 = m
  End If
  m2 = m2 - 50
  If f <= 250 Then
    p = 0.006
  ElseIf f <= 500 Then
    p = 0.0065
  ElseIf f <= 1000 Then
    p = 0.007
  ElseIf f <= 5000 Then
    p = 0.0075
  Else
    p = 0.008
  End If
  s = m2 * p
  If f > 10000 Then
    s = s + (m - 10000) * 0.003
  End If
  StampDutyTax = Application.Ceiling(Round(s, 2), 0.05)
End If
x = Int(StampDutyTax)
StampDutyTax = Array(Round(100 * (StampDutyTax - x)), x)
End Function
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Separate Whole Number from Decimal

Post by HansV »

Here is a VBA solution

Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

Re: How to Separate Whole Number from Decimal

Post by jakjo »

Thank you very much everyone, that's not what I mean
I want to deal with the decimal and integer values shown in cells B3 and A3, as these columns contain the tax amount.
As for cells D3 and C3, these columns contain the net tax due after deducting all exemptions.
How can this formula be modified to achieve its intended goal?

Code: Select all

=StampDutyTax(F3;G3)
Thank you for your attention.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Separate Whole Number from Decimal

Post by HansV »

=StampDutyTax(B3+A3/100,D3+C3/100)
Best wishes,
Hans

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

Re: How to Separate Whole Number from Decimal

Post by jakjo »

Thank you very much to everyone
Simply, I want to modify this function to get the expected result by dividing this result into two cells
one for the decimal places and the other for the integer values, as shown in the image below.
Once again, thank you for your patience.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Separate Whole Number from Decimal

Post by HansV »

Have you tried the function that I posted? It does split the result into dollars and cents.
Best wishes,
Hans

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

Re: How to Separate Whole Number from Decimal

Post by jakjo »

Yes, I tried the function that you posted, but it doesn't split the result into dollars and cents.
I tried to include both the MOD and INT functions, but unfortunately, I failed to do so.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Separate Whole Number from Decimal

Post by HansV »

See the attached version.

Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

Re: How to Separate Whole Number from Decimal

Post by jakjo »

Thank you very much, Mr. Hans. Unfortunately, I did not get the desired result.
Here is a new version of the same function

Code: Select all

Private Function CalculateStampDuty(B2 As Double, A2 As Double) As Double
    Dim result As Double
    
    If B2 > 10000 Then
        result = (A2 - 10000) * 0.003 + (10000 - 50) * 0.008
    ElseIf B2 > 5000 Then
        result = (A2 - 50) * 0.008
    ElseIf B2 > 1000 Then
        result = (A2 - 50) * 0.0075
    ElseIf B2 > 500 Then
        result = (A2 - 50) * 0.007
    ElseIf B2 > 250 Then
        result = (A2 - 50) * 0.0065
    ElseIf B2 > 50 Then
        result = (A2 - 50) * 0.006
    Else
        result = 0
    End If
    
    CalculateStampDuty = Application.Ceiling(Application.Round(result, 2), 0.05)
End Function

Function StampDutyCents(B2 As Double, A2 As Double) As Double
    Dim total As Double
    total = CalculateStampDuty(B2, A2)
    StampDutyCents = (total - Int(total)) * 100 ' Extract the cents portion
End Function

Function StampDutyDollars(B2 As Double, A2 As Double) As Double
    Dim total As Double
    total = CalculateStampDuty(B2, A2)
    StampDutyDollars = Int(total) ' Extract the dollar portion
End Function

How can these two parts be modified?

Code: Select all

Function StampDutyCents(B2 As Double, A2 As Double) As Double
    Dim total As Double
    total = CalculateStampDuty(B2, A2)
    StampDutyCents = (total - Int(total)) * 100 ' Extract the cents portion
End Function

Function StampDutyDollars(B2 As Double, A2 As Double) As Double
    Dim total As Double
    total = CalculateStampDuty(B2, A2)
    StampDutyDollars = Int(total) ' Extract the dollar portion
End Function

Please find a solution to get the decimal places in one cell and the whole numbers in an adjacent cell.
Thank you once again for your patience and attention
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Separate Whole Number from Decimal

Post by HansV »

The workbook that I attached in my previous reply demonstrated how to do what you want: it returns the dollars to one cell and the cents to the cell next to it, without needing two extra functions
If you look at the formula, it also shows how you can use your extra functions - see the version attached here.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jakjo
Lounger
Posts: 40
Joined: 28 May 2022, 00:57

Re: How to Separate Whole Number from Decimal

Post by jakjo »

Thank you very much Hans for your support at all times.
I reviewed the version, and everything is going smoothly.
Best regards