Discussion Re: Formula to calculate Canadian taxes in Excel
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562101#M152405
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.Thu, 30 Jun 2022 00:41:39 GMTEagleAD2022-06-30T00:41:39ZFormula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3561522#M152328
<P>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?</P><TABLE width="325"><TBODY><TR><TD width="69">AB</TD><TD width="64">T9K 2C5</TD><TD width="64">Canada</TD><TD width="64">CAD</TD><TD width="64">$119.99</TD></TR><TR><TD>ON</TD><TD>L4M6Z8</TD><TD>Canada</TD><TD>CAD</TD><TD>$49.99</TD></TR><TR><TD>ON</TD><TD>L6M0Z4</TD><TD>Canada</TD><TD>CAD</TD><TD>$119.99</TD></TR><TR><TD>AB</TD><TD>T8R 1B3</TD><TD>Canada</TD><TD>CAD</TD><TD>$59.99</TD></TR><TR><TD>ON</TD><TD>M1N1y6</TD><TD>Canada</TD><TD>CAD</TD><TD>$99.50</TD></TR><TR><TD>NS</TD><TD>B0M 1S0</TD><TD>Canada</TD><TD>CAD</TD><TD>$104.99</TD></TR><TR><TD>AB</TD><TD>T1W 2C3</TD><TD>Canada</TD><TD>CAD</TD><TD>$89.99</TD></TR><TR><TD>ON</TD><TD>L0R1H1</TD><TD>Canada</TD><TD>CAD</TD><TD>$69.99</TD></TR><TR><TD>ON</TD><TD>P3B 1X6</TD><TD>Canada</TD><TD>CAD</TD><TD>$109.99</TD></TR><TR><TD>AB</TD><TD>T5r2h7</TD><TD>Canada</TD><TD>CAD</TD><TD>$49.99</TD></TR><TR><TD>ON</TD><TD>N2A 3G7</TD><TD>Canada</TD><TD>CAD</TD><TD>$69.99</TD></TR></TBODY></TABLE>Wed, 29 Jun 2022 14:54:44 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3561522#M152328EagleAD2022-06-29T14:54:44ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3561552#M152334
<P><LI-USER uid="1440285"></LI-USER> 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.</P><P> </P><P>By the way, I just made up something for NS.</P>Wed, 29 Jun 2022 15:16:10 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3561552#M152334Riny_van_Eekelen2022-06-29T15:16:10ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562101#M152405
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.Thu, 30 Jun 2022 00:41:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562101#M152405EagleAD2022-06-30T00:41:39ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562235#M152412
<P><LI-USER uid="1440285"></LI-USER> 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.</P>Thu, 30 Jun 2022 05:16:01 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562235#M152412Riny_van_Eekelen2022-06-30T05:16:01ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562747#M152506
I have to point out I'm a novice at formulas. Thanks for pointing out the "Quotes" turning them into Texts. I tried your solution with the lookup function but kept getting an error. I was messing something up in the formula which is pretty straightforward. Your help is much appreciated!Thu, 30 Jun 2022 14:57:38 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3562747#M152506EagleAD2022-06-30T14:57:38ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3563035#M152562
<P><LI-USER uid="1440285"></LI-USER> Did you figure it out or do you still need help?</P>Thu, 30 Jun 2022 19:41:26 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3563035#M152562Riny_van_Eekelen2022-06-30T19:41:26ZRe: Formula to calculate Canadian taxes
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3574980#M154778
<P><LI-USER uid="403176"></LI-USER> I Think I'm good now. Your help was very much appreciated.</P>Sun, 17 Jul 2022 19:58:36 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-canadian-taxes/m-p/3574980#M154778EagleAD2022-07-17T19:58:36Z