Need a formula

  • Thread starter Thread starter memac19
  • Start date Start date
M

memac19

I am having trouble setting up a formula that will apply differen
taxes, depending on what province the customer is from.
ex.
If have a customer from BC I need it to calculate 7.5%, If from Sask
need 6%
My cells are:
-- BC
-- $6800
thus if it is BC it would be $6800*.075=$510
Problem is how how do I get it to times by 6% if I change the BC cel
to Sask.
Thank
 
I am having trouble setting up a formula that will apply different
taxes, depending on what province the customer is from.
ex.
If have a customer from BC I need it to calculate 7.5%, If from Sask I
need 6%
My cells are:
-- BC
-- $6800
thus if it is BC it would be $6800*.075=$510
Problem is how how do I get it to times by 6% if I change the BC cell
to Sask.
Thanks
Click to expand...

Assuming A2 is the cell containing the codes (BC, Sask etc) and A3 is the
cell containing the amount ...

=LOOKUP(A2,{"BC",0.075;"Sask",0.06})*A3

If you have more then 2 or 3 codes (which is likely when dealing with
taxrates) it would the method above makes for a cumbersome formula, in that
case it would be easier to make a table of codes and rates and use VLOOKUP
or HLOOKUP

For Example:

Upper left Cell for this table is E2, entire table (without the headings) is
E3:F5

Area Rate
BC 7.50%
Sask 6.00%
Quebec 6.25%

With the data in the same cells as the first example, the new formula would
be ...

=VLOOKUP(A2,E3:F5,2)*A3

Hope this helps.

Best,
Bill
 
The formula worked great, but in AB there is no tax. I tried ("AB",0)
but it brought up NA. I need it to bring up 0 because my other formula
that are attached to this cell won't read NA
Thank
 
The formula worked great, but in AB there is no tax. I tried ("AB",0),
but it brought up NA. I need it to bring up 0 because my other
formulas that are attached to this cell won't read NA
Thanks
Click to expand...

It works with AB and a 0 tax rate when I try it ...

=LOOKUP(A2,{"AB",0;"BC",0.075;"Sask",0.06})*A3

Which version of Excel are you using?

Best,
Bill
 
Back
Top