Formula for adding taxes & tpt vaue

[RAMU]

Dear All,

I have two workbooks, one is containing taxes & TPT (transportation) cost of 60 items like mentioned below:
workbook1

	B	C	D	E	F
1	ITEM	ED	CST	VAT	TPT
2	A			2%	20
3	B	13.50%			3
4	C
5	D			2%	20
6	E	4			5
7	F	13.50%			3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

And the other workbook is containing a value like the following:
workbook2

	A	B	C	D
1	ITEM	QTY	VALUE	TOTAL VALUE
2	A	50	2000
3	B	100	5000
4	B	100	4050
5	C	40	600
6	C	40	540
7	E	20	300
8	B	100	5000
9	A	50	2500

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 89px"></colgroup><tbody>
</tbody>

Now I want a formula in column D of workbook2 that will add the taxes automatically with value ( sequence would be same as mentioned in the column of workbook1), then TPT cost will be added on after calculating its total cost. In workbook1 TPT is mentioned per unit basis. Ultimately TOTAL VALUE will show the result.

Please help.

Thanks in advance
RAMU

 

[Sal Paradise]

Dear All,

I have two workbooks, one is containing taxes & TPT (transportation) cost of 60 items like mentioned below:
workbook1

	B	C	D	E	F
1	ITEM	ED	CST	VAT	TPT
2	A			2%	20
3	B	13.50%			3
4	C
5	D			2%	20
6	E	4			5
7	F	13.50%			3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

And the other workbook is containing a value like the following:
workbook2

	A	B	C	D
1	ITEM	QTY	VALUE	TOTAL VALUE
2	A	50	2000
3	B	100	5000
4	B	100	4050
5	C	40	600
6	C	40	540
7	E	20	300
8	B	100	5000
9	A	50	2500

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 89px"></colgroup><tbody>
</tbody>

Now I want a formula in column D of workbook2 that will add the taxes automatically with value ( sequence would be same as mentioned in the column of workbook1), then TPT cost will be added on after calculating its total cost. In workbook1 TPT is mentioned per unit basis. Ultimately TOTAL VALUE will show the result.

Please help.

Thanks in advance
RAMU

Given your data:

Excel 2003
	B	C	D	E	F
1	ITEM	ED	CST	VAT	TPT
2	A			2%	20
3	B	13.50%			3
4	C
5	D			2%	20
6	E	4			5
7	F	13.50%			3
Taxes & TPT

You can use this formula:

Excel 2003
	B	C	D	E
1	ITEM	QTY	VALUE	TOTAL VALUE
2	A	50	2000	103000
3	B	100	5000	567800
4	B	100	4050	459975
5	C	40	600	24000
6	C	40	540	21600
7	E	20	300	30100
8	B	100	5000	567800
9	A	50	2500	128500
Value

Cell Formulas
Range	Formula
E2	=D2*C2*(1+SUM(OFFSET(INDEX('Taxes & TPT'!C:C,MATCH(B2,'Taxes & TPT'!B:B,0)),0,0,1,3)))+C2*INDEX('Taxes & TPT'!F:F,MATCH(B2,'Taxes & TPT'!B:B,0))

 

[RAMU]

Lil misunderstanding. In E2 value will be 3040. See its value is 2000. therefore taxes will be added 2% only. 2% of 2000 is 40. And TPT is 20 per unit. so tpt cost will be 50*20 = 1000. so total taxes & TPT amount is 1040. Now 1040 will be added with 2000. So Total value will be 3040.

Regards
RAMU

 

[Sal Paradise]

Lil misunderstanding. In E2 value will be 3040. See its value is 2000. therefore taxes will be added 2% only. 2% of 2000 is 40. And TPT is 20 per unit. so tpt cost will be 50*20 = 1000. so total taxes & TPT amount is 1040. Now 1040 will be added with 2000. So Total value will be 3040.

Regards
RAMU

Try this:

=D2*(1+SUM(OFFSET(INDEX('Taxes & TPT'!C:C,MATCH(B2,'Taxes & TPT'!B:B,0)),0,0,1,3)))+C2*INDEX('Taxes & TPT'!F:F,MATCH(B2,'Taxes & TPT'!B:B,0))

Excel 2003
	B	C	D	E
1	ITEM	QTY	VALUE	TOTAL VALUE
2	A	50	2000	3040
3	B	100	5000	5975
4	B	100	4050	4896.75
5	C	40	600	600
6	C	40	540	540
7	E	20	300	1600
8	B	100	5000	5975
9	A	50	2500	3550
Sheet2

Cell Formulas
Range	Formula
E2	=D2*(1+SUM(OFFSET(INDEX('Taxes & TPT'!C:C,MATCH(B2,'Taxes & TPT'!B:B,0)),0,0,1,3)))+C2*INDEX('Taxes & TPT'!F:F,MATCH(B2,'Taxes & TPT'!B:B,0))

 

[RAMU]

Dear Friend,

I was wrong. Your formula is perfect based on my statement. Actually If a product has multiple taxes like ED & CST then First it will add the value & ED value first, then CST or VAT will be calculated on that value.

Suppose product G has 13.5% ED, 2% CST & TPT 50, then first it will calculate the ED value like X*13.5% ( X = value ) = Y, then CST will be calculated like Y*2% =Z, then TPT : qty*50 = V. Ultimately Total value will come (X + Y + Z + V).

Think Now I am clear.
Regards
RAMU

 

[RAMU]

Dear friends,

Please help me to solve it.

Thanks
RAMU

 

[Sal Paradise]

Dear friends,

Please help me to solve it.

Thanks
RAMU

Try this:

Excel 2003
	B	C	D	E
1	ITEM	QTY	VALUE	TOTAL VALUE
2	A	50	2000	3040
3	B	100	5000	5975
4	B	100	4050	4896.75
5	C	40	600	600
6	C	40	540	540
7	E	20	300	1600
8	B	100	5000	5975
9	A	50	2500	3550
Sheet2

Cell Formulas
Range	Formula
E2	=D2*(1+INDEX('Taxes & TPT'!C:C,MATCH(B2,'Taxes & TPT'!B:B,0)))*(1+INDEX('Taxes & TPT'!D:D,MATCH(B2,'Taxes & TPT'!B:B,0)))*(1+INDEX('Taxes & TPT'!E:E,MATCH(B2,'Taxes & TPT'!B:B,0)))+C2*INDEX('Taxes & TPT'!F:F,MATCH(B2,'Taxes & TPT'!B:B,0))

And just for future reference, when there are ambiguous calculations in the sheet (like whether taxes are taxed, or if they are all added together and taxed on the value alone), you may want to include samples for those cases, and give us explanations of what you expect the answer to be (it will save us trouble).

 

[RAMU]

Dear Friend,

Shall try to remember your suggestion. Your formula works perfect. thanx

RAMU