Formula for adding taxes & tpt vaue

RAMU

Active Member
Joined
Dec 11, 2009
Messages
298
Dear All,

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

BCDEF
1ITEMEDCSTVATTPT
2A 2%20
3B13.50% 3
4C
5D 2%20
6E4 5
7F13.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

ABCD
1ITEMQTYVALUETOTAL VALUE
2A502000
3B1005000
4B1004050
5C40600
6C40540
7E20300
8B1005000
9A502500

<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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Dear All,

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

BCDEF
1ITEMEDCSTVATTPT
2A 2%20
3B13.50% 3
4C
5D 2%20
6E4 5
7F13.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

ABCD
1ITEMQTYVALUETOTAL VALUE
2A502000
3B1005000
4B1004050
5C40600
6C40540
7E20300
8B1005000
9A502500

<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:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ITEM </td><td style=";">ED </td><td style=";">CST </td><td style=";">VAT </td><td style=";">TPT</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2%</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B </td><td style="text-align: right;;">13.50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">C </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">D </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2%</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">E </td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">F </td><td style="text-align: right;;">13.50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Taxes & TPT</p><br /><br />

You can use this formula:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ITEM </td><td style=";">QTY </td><td style=";">VALUE </td><td style=";">TOTAL VALUE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">103000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">567800</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">4050</td><td style="text-align: right;;">459975</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">600</td><td style="text-align: right;;">24000</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">540</td><td style="text-align: right;;">21600</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">E </td><td style="text-align: right;;">20</td><td style="text-align: right;;">300</td><td style="text-align: right;;">30100</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">567800</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">128500</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Value</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2*C2*(<font color="Blue">1+SUM(<font color="Red">OFFSET(<font color="Green">INDEX(<font color="Purple">'Taxes & TPT'!C:C,MATCH(<font color="Teal">B2,'Taxes & TPT'!B:B,0</font>)</font>),0,0,1,3</font>)</font>)</font>)+C2*INDEX(<font color="Blue">'Taxes & TPT'!F:F,MATCH(<font color="Red">B2,'Taxes & TPT'!B:B,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

RAMU

Active Member
Joined
Dec 11, 2009
Messages
298
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

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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))


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ITEM </td><td style=";">QTY </td><td style=";">VALUE </td><td style=";">TOTAL VALUE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">3040</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5975</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">4050</td><td style="text-align: right;;">4896.75</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">540</td><td style="text-align: right;;">540</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">E </td><td style="text-align: right;;">20</td><td style="text-align: right;;">300</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5975</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">3550</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2*(<font color="Blue">1+SUM(<font color="Red">OFFSET(<font color="Green">INDEX(<font color="Purple">'Taxes & TPT'!C:C,MATCH(<font color="Teal">B2,'Taxes & TPT'!B:B,0</font>)</font>),0,0,1,3</font>)</font>)</font>)+C2*INDEX(<font color="Blue">'Taxes & TPT'!F:F,MATCH(<font color="Red">B2,'Taxes & TPT'!B:B,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

RAMU

Active Member
Joined
Dec 11, 2009
Messages
298

ADVERTISEMENT

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
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Dear friends,

Please help me to solve it.

Thanks
RAMU
Try this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ITEM </td><td style=";">QTY </td><td style=";">VALUE </td><td style=";">TOTAL VALUE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">3040</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5975</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">4050</td><td style="text-align: right;;">4896.75</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C </td><td style="text-align: right;;">40</td><td style="text-align: right;;">540</td><td style="text-align: right;;">540</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">E </td><td style="text-align: right;;">20</td><td style="text-align: right;;">300</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5975</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A </td><td style="text-align: right;;">50</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">3550</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2*(<font color="Blue">1+INDEX(<font color="Red">'Taxes & TPT'!C:C,MATCH(<font color="Green">B2,'Taxes & TPT'!B:B,0</font>)</font>)</font>)*(<font color="Blue">1+INDEX(<font color="Red">'Taxes & TPT'!D:D,MATCH(<font color="Green">B2,'Taxes & TPT'!B:B,0</font>)</font>)</font>)*(<font color="Blue">1+INDEX(<font color="Red">'Taxes & TPT'!E:E,MATCH(<font color="Green">B2,'Taxes & TPT'!B:B,0</font>)</font>)</font>)+C2*INDEX(<font color="Blue">'Taxes & TPT'!F:F,MATCH(<font color="Red">B2,'Taxes & TPT'!B:B,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

Active Member
Joined
Dec 11, 2009
Messages
298
Dear Friend,

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

RAMU
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,746
Members
417,108
Latest member
Thein Than

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
Top