Forum Discussion

EagleAD's avatar
EagleAD
Copper Contributor
Jun 29, 2022

Formula to calculate Canadian taxes

I'm struggling to figure out a formula for Canadian Taxes.  We now have to include taxes in our product prices on Etsy.  So I need the formula to calculate what I have to remit to the gov. Here's an example of the info I get from Etsy.  What I'm trying to do is that the first column says "AB" to calculate the tax rate of 5% and return that amount in another column on the same row.  If it reads "ON" return the $49.99 x 13% and so on for each row for each appropriate tax rate.  Is there a way of doing this?

ABT9K 2C5CanadaCAD$119.99
ONL4M6Z8CanadaCAD$49.99
ONL6M0Z4CanadaCAD$119.99
ABT8R 1B3CanadaCAD$59.99
ONM1N1y6CanadaCAD$99.50
NSB0M 1S0CanadaCAD$104.99
ABT1W 2C3CanadaCAD$89.99
ONL0R1H1CanadaCAD$69.99
ONP3B 1X6CanadaCAD$109.99
ABT5r2h7CanadaCAD$49.99
ONN2A 3G7CanadaCAD$69.99
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 29, 2022

    EagleAD Best to create a lookup table with all the tax rates per state and then use VLOOKUP or XLOOKUP is your Excel version supports it. But you could also use LOOKUP . See attached. Several possible solutions in it.

     

    By the way, I just made up something for NS.

    • EagleAD's avatar
      EagleAD
      Copper Contributor
      Jun 30, 2022
      Thanks for the help! With your guidance and a bit of google, I ended up using the IFS formula: =IFS(M4="ON",".13",M4="AB",".05",M4="BC",".12",M4="MB",".12",M4="SK",".11",M4="QC",".14975",M4="NS",".15",M4="NB",".15",M4="NL",".15",M4="PE",".15",M4="NT",".05",M4="NU",".05",M4="YT",".05",TRUE,"0") Too many variable for the =IF command. This covered every province and excluded anything not in Canada. I then take the result and multiply it by the cost of the product column. Thanks again for your help.
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jun 30, 2022

        EagleAD Your choice to do it that way, but I would use a look up table. And, by the way, why did you put the tax rates between quotes? That turns them into texts. Although Excel will interpret them as a number as soon as you do some arithmetic with them, better to use numbers from the start.

Resources