Skip to main content

If Statement for Taxes


I need some help. I have multiple taxes to account for:

IF({Prov}=BC,(Price+Discount)*0.07),
IF({Prov}=MB(Price+Discount)*0.08),
IF({Prov}=NB(Price+Discount)*0.15),
IF({Prov}=NL (Price+Discount)*0.15),
IF({Prov}=NS (Price+Discount)*0.15),
IF({Prov}=ON (Price+Discount)*0.13),
IF({Prov}=PE (Price+Discount)*0.15),
IF({Prov}=SK (Price+Discount)*0.06),0))))))))

What am I missing to make this work?

2 replies

3 issues I see:
1 - Each province abbreviation is a string, and needs to be denoted as such with quotation marks - "BC", "MB", etc

2 - Each province abbreviation needs to be followed by a comma, like your first one is, to separate the condition for the IF() statement from the output when the condition is met

3 - The closing parenthesis at the end of each line after the tax rate is erroneous and causing an error. Immediately after your tax rate (0.07, 0.08, 0.15, etc) should be a comma, followed by the next IF(.... You already have all of your closing parentheses at the very end of the statement.

There is an easier function for doing this type of work though, called SWITCH():

Your formula converted to a SWITCH() function would look like this:

SWITCH(
   {Prov},
   "BC", (Price + Discount) * 0.07,
   "MB", (Price + Discount) * 0.08,
   "NB", (Price + Discount) * 0.15,
   "NL", (Price + Discount) * 0.15,
   "NS", (Price + Discount) * 0.15,
   "ON", (Price + Discount) * 0.13,
   "PE", (Price + Discount) * 0.15,
   "SK", (Price + Discount) * 0.06,
   0
)

  • Author
  • New Participant
  • 1 reply
  • November 29, 2018
Jeremy_Oglesby wrote:

3 issues I see:
1 - Each province abbreviation is a string, and needs to be denoted as such with quotation marks - "BC", "MB", etc

2 - Each province abbreviation needs to be followed by a comma, like your first one is, to separate the condition for the IF() statement from the output when the condition is met

3 - The closing parenthesis at the end of each line after the tax rate is erroneous and causing an error. Immediately after your tax rate (0.07, 0.08, 0.15, etc) should be a comma, followed by the next IF(.... You already have all of your closing parentheses at the very end of the statement.

There is an easier function for doing this type of work though, called SWITCH():

Your formula converted to a SWITCH() function would look like this:

SWITCH(
   {Prov},
   "BC", (Price + Discount) * 0.07,
   "MB", (Price + Discount) * 0.08,
   "NB", (Price + Discount) * 0.15,
   "NL", (Price + Discount) * 0.15,
   "NS", (Price + Discount) * 0.15,
   "ON", (Price + Discount) * 0.13,
   "PE", (Price + Discount) * 0.15,
   "SK", (Price + Discount) * 0.06,
   0
)

Thank you Jeremy! That was extremely helpful!!


Reply