Something more efficient then Nested IF statements

[canadian86]

I have the provinces and subtotal listed in two columns:

<table style="width: 239px; height: 380px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="75"> <col style="width: 56pt;" width="74"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 56pt;" height="20" width="75">Province
</td> <td style="width: 56pt;" width="74">Subtotal</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ON</td> <td class="xl320"> $ 2,819.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">QC</td> <td class="xl320"> $ 3,928.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">NS</td> <td class="xl320"> $ 482.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">NB</td> <td class="xl320"> $ 33.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MB</td> <td class="xl320"> $ 3,242.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BC</td> <td class="xl320"> $ 5,323.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PI</td> <td class="xl320"> $ 5,532.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">SK</td> <td class="xl320"> $ 67.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">AB</td> <td class="xl320"> $ 766.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">NL</td> <td class="xl320"> $ 5,454.00 </td> </tr> </tbody></table>

I know the sales tax for each province in my head (i.e. Ontario = 13%)

I want a formula that will automatically multiply the sub-total by the correct tax rate based on the province code shown above.

For example, in cell C2, since the Ontario tax rate is 13%, the formula will multiply 2819 x 13% and give this answer: 366.47

I believe nested IF statements are limited to 7 only, so I don't think I can use that.

What's the most efficient way to achieve this?

Thanks!

______________
I use Excel 2007

 

[CWatts]

There are a few ways, but here is one:

In column E, list your province abbreviations.
In column F, list tax rate that applies to that province

In cell C2, use this formula:

=B2*INDEX($F$2:$F$11,MATCH(A2,$E$2:$E$11,0))

The MATCH formula, matches the province from A2 and finds out what position in column E it's located. it then finds the number in the same spot as column F and multiplies it by the dollar amount.

You can hide the columns, or move it to a separate sheet.

That's one option. If you can provide more detail how you plan to interact with this sheet, another version may be better suited.

 

[canadian86]

Works great.. thanks a lot!!

 

[Ganjin]

nevermind xD