Excel calculate tax

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
I have two tax type
  1. Slab 1 &
  2. Slab 2
In sheet 1 Column G the tax value should be calculated based on the Column F and the value should be calculated from sheet 2 and please check the desired output of the tax value in column I

tax.xlsx
ABCDEFGHI
1NoNameQTYPriceTotal PriceTax TypeTax ValueFinal PriceDesired Output In Column G
21CTI52001000Slab 11000280
32CTI3195585Slab 1585205.3
43TCI3200600Slab 2600238
54CCC4100400Slab 2400192
65ICN3250750Slab 1750235
Sheet1
Cell Formulas
RangeFormula
H2:H6H2=(E2+G2)
I2:I3,I6I2=((E2*18)/100)+100
I4:I5I4=((E4*23)/100)+100
E2:E6E2=(C2*D2)



tax.xlsx
ABC
1IndexSlab 1Slab 2
2GST0.180.23
3DC10050
Sheet2
Cell Formulas
RangeFormula
B2B2=(18/100)
C2C2=(23/100)
 
Thank you MARK858, still trying
this is what I got now, is true the slab1 and 2 is a condition I didn't considerate, so this is my new try, Please, correct me, a really appreciate your input. Thanks
VBA Code:
Sub varu_v2()
         Dim x As Long
         Dim y As Long
         Dim Cf As Integer
                  For x = 2 To 6
                           For y = 5 To 6
                                       If Cells(x, "f").Value = slab1 Then
                                                Cells(x, "J").Value = Cells(x, "E").Value * 0.18 + 100
                                                Else
                                                Cells(x, "J").Value = Cells(x, "E").Value * 0.23 + 50
                                       End If
                           Next y
                  Next x
End Sub
now this is the results (I am not saying nothing about , right or wrong, I am not sure).
1599145227462.png
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I don't see the need for the y variable and
VBA Code:
.Value = slab1
should be
VBA Code:
.Value = "Slab 1"
So you end up with
VBA Code:
Sub varu_v2()
         Dim x As Long

                  For x = 2 To 6
                                       If Cells(x, "f").Value = "Slab 1" Then
                                                Cells(x, "L").Value = Cells(x, "E").Value * 0.18 + 100
                                                Else
                                                Cells(x, "L").Value = Cells(x, "E").Value * 0.23 + 50
                                       End If
                  Next x
End Sub

Results in Column L.
I think then you will get the correct results ;)
 
Upvote 0
MARK858 Thank you So much for your lesson.
Yes the line for y = 2 to 6 wasn't necessary. [first mistake]
The word slab1 didn't have " "[second mistake}
and
when I write Slab 1, I didn't separate the word from 1 [mistake 3]

Big lesson today from you Mr. Mark858
Thank you for your time.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top