I need to calculate the VAT % based on lots of criteria. I've written it as a nested IF function but it's way too large. I want to use CASE but I'm struggling.
The criteria I'm trying to evaluate is:
If the account is a member of the EU (using the EU member state tickbox)
If the product code is one of two codes
If the account country is one of five countries
If the account country is Canada and the province is one of 13 areas
Each of these criteria will return a different VAT value.
This is the IF function I've written that is too large:
IF(
ProductCode__c ="E-MTU-E"||ProductCode__c ="E-MTU-B",0,
IF(
Opportunity.Account.EU_member_state__c =TRUE,0,
IF(
Opportunity.Account.BillingCountry ="United Kingdom",0.2,
IF(
Opportunity.Account.BillingCountry ="Ireland",0.23,
IF(
Opportunity.Account.BillingCountry ="South Africa",0.14,
IF(
Opportunity.Account.BillingCountry ="Korea, North",0.1,
IF(
Opportunity.Account.BillingCountry ="Korea, South",0.1,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Alberta"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="British Columbia"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Manitoba"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="New Brunswick"),0.13,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Newfoundland and Labrador"),0.13,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Northwest Territories"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Nova Scotia"),0.15,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Nunavut"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Ontario"),0.13,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Prince Edward Island"),0.14,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Quebec"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Saskatchewan"),0.05,
IF(AND( Opportunity.Account.BillingCountry ="Canada", Opportunity.Account.BillingState ="Yukon"),0.05,
0))))))))))))))))))))
I've not used Case before so I have only got as far as working out the first five countries like this:
CASE((Opportunity.Account.BillingCountry),
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
0)
This bit works but can anyone help me join the rest up? How do I say IF Country = Canada and State = x?
Help much appreciated.
Hi Sara, Please try the below
IF(ProductCode__c ="E-MTU-E"||ProductCode__c ="E-MTU-B",0,
IF(Opportunity.Account.EU_member_state__c,0,
CASE(Opportunity.Account.BillingCountry,
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
"Canada",
CASE(Opportunity.Account.BillingState,
"Alberta",0.05,
"British Columbia",0.05,
"Manitoba",0.05,
"New Brunswick",0.13,
"Newfoundland and Labrador",0.13,
"Northwest Territories",0.05,
"Nova Scotia",0.15,
"Nunavut",0.05,
"Ontario",0.13,
"Prince Edward Island",0.14,
"Quebec",0.05,
"Saskatchewan",0.05,
"Yukon",0.05,0
),0
)
)
)
