Excel calculate tax

[varunwalla]

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
	A	B	C	D	E	F	G	H	I
1	No	Name	QTY	Price	Total Price	Tax Type	Tax Value	Final Price	Desired Output In Column G
2	1	CTI	5	200	1000	Slab 1		1000	280
3	2	CTI	3	195	585	Slab 1		585	205.3
4	3	TCI	3	200	600	Slab 2		600	238
5	4	CCC	4	100	400	Slab 2		400	192
6	5	ICN	3	250	750	Slab 1		750	235
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=(E2+G2)
I2:I3,I6	I2	=((E2*18)/100)+100
I4:I5	I4	=((E4*23)/100)+100
E2:E6	E2	=(C2*D2)

tax.xlsx
	A	B	C
1	Index	Slab 1	Slab 2
2	GST	0.18	0.23
3	DC	100	50
Sheet2

Cell Formulas
Range	Formula
B2	B2	=(18/100)
C2	C2	=(23/100)

 

[rondeondo]

Hi there
you can use HLookup, or Xlookup in excel 365, to return your tax rate from sheet 2 then use that in your calculation,
So G2 will be something like =E2*HLOOKUP(F2,Sheet2!$A$1:$C$2,2,FALSE)+100

 

[MARK858]

Why are I4 and I5 238 and 192 and not 188 and 142 respectively as they come under Slab2?

 

[varunwalla]

Hi there
you can use HLookup, or Xlookup in excel 365, to return your tax rate from sheet 2 then use that in your calculation,
So G2 will be something like =E2*HLOOKUP(F2,Sheet2!$A$1:$C$2,2,FALSE)+100

thanks man it works

Why are I4 and I5 238 and 192 and not 188 and 142 respectively as they come under Slab2?

wow you found mistake

 

[MARK858]

Formula in Column G or Column H

Book1
	A	B	C	D	E	F	G	H	I	J
1	No	Name	QTY	Price	Total Price	Tax Type	Tax Value2	Tax Value	Final Price	Desired Output In Column G
2	1	CTI	5	200	1000	Slab 1	280	280	1280	280
3	2	CTI	3	195	585	Slab 1	205.3	205.3	790.3	205.3
4	3	TCI	3	200	600	Slab 2	188	188	788	238
5	4	CCC	4	100	400	Slab 2	142	142	542	192
6	5	ICN	3	250	750	Slab 1	235	235	985	235
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=E2*HLOOKUP(F2,Sheet2!$B$1:$C$3,2,0)+HLOOKUP(F2,Sheet2!$B$1:$C$3,3,0)
H2:H6	H2	=E2*INDEX(Sheet2!$B$2:$C$2,MATCH(F2,Sheet2!$B$1:$C$1,0))+INDEX(Sheet2!$B$3:C$3,MATCH(F2,Sheet2!$B$1:$C$1,0))
I2:I6	I2	=(E2+G2)
J2:J3,J6	J2	=((E2*18)/100)+100
J4:J5	J4	=((E4*23)/100)+100
E2:E6	E2	=(C2*D2)

 

[montecarlo2012]

Hello, Your column E is exactly the same as column H, so are you looking for a code, so you just click and everything is done?

 

[MARK858]

Hello, Your column E is exactly the same as column H, so are you looking for a code, so you just click and everything is done?

Column H in the original post has a formula in it and so it changes when you put numbers in column G.

 

[montecarlo2012]

ok

 

[montecarlo2012]

I am trying JUST to help. I am NOT an expert.
if I am in that sheet with that info, this is what I do,
and please, CORRECT ME.

Sub varunwalla()
         Dim x As Long
         Dim y As Long
                  For x = 2 To 6
                           For y = 5 To 5
                                          Cells(x, "J").Value = Cells(x, "E").Value * (18 / 100) + 100
                           Next y
                  Next x
End Sub

This is what I got.

 

[MARK858]

First of all I wasn't chastising you, I was just pointing out that column H had a formula in it which is why it looks the same as column E as there was no data in column G in the first post.

Anyway I think that you will need to amend your code as the it is not always +100 the reply below

wow you found mistake

is because Slab2 is cell C2 (the tax rate being multiplied) + C3 (50, the 100 (B3) only applies to Slab1).
The result should appear in column G and should be

Book1
	G
2	280
3	205.3
4	188
5	142
6	235
Sheet1

Based on the OP's original figures