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
How to Separate Whole Number from Decimal
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
How to Separate Whole Number from Decimal
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 12949
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: How to Separate Whole Number from Decimal
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
For the whole number part
=INT(H3)
For the decimal part
=(H3-K3)*100
StuartR
-
- 2StarLounger
- Posts: 176
- Joined: 11 Jun 2012, 20:37
Re: How to Separate Whole Number from Decimal
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:
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.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to Separate Whole Number from Decimal
Here is a VBA solution
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
Re: How to Separate Whole Number from Decimal
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?
Thank you for your attention.
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)
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
Re: How to Separate Whole Number from Decimal
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.
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.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to Separate Whole Number from Decimal
Have you tried the function that I posted? It does split the result into dollars and cents.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
Re: How to Separate Whole Number from Decimal
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.
I tried to include both the MOD and INT functions, but unfortunately, I failed to do so.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to Separate Whole Number from Decimal
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
Re: How to Separate Whole Number from Decimal
Thank you very much, Mr. Hans. Unfortunately, I did not get the desired result.
Here is a new version of the same function
How can these two parts be modified?
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
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
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
Thank you once again for your patience and attention
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to Separate Whole Number from Decimal
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.
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
Hans
-
- Lounger
- Posts: 40
- Joined: 28 May 2022, 00:57
Re: How to Separate Whole Number from Decimal
Thank you very much Hans for your support at all times.
I reviewed the version, and everything is going smoothly.
Best regards
I reviewed the version, and everything is going smoothly.
Best regards