Formula 9

Operators/Functions Used

Nested If-Then-Else expressions, NumericText, Subscript, Make Range, Equal to (=), ToNumber, In Range, Parentheses ()

Formula Purpose

A retailer has a customer list that includes both customers living inside the state and customers living in Canada. For customers inside the state, some have 5 digit ZIP codes, others have 9 digit ZIP codes. Canadian customers have Postal Codes beginning with a letter.

The company wants a column on the list that shows the sales tax that needs to be levied on orders from each customer. Local customers (those within the greater metropolitan area) get assigned a 7.5% sales tax (6.5% state and 1% local); customers in the state but outside the metropolitan area are assigned a 6.5% sales tax (state only); and Canadian customers are assigned no sales tax.

Formula

If NumericText({customer.POSTAL CODE}[1 to 5])Then

    If ToNumber({customer.POSTAL CODE}[1 to 5]) in 

    92200 to 92399 Then 

        "7.5" 

    Else 

    "6.5"

Else 

    "0"

Result

ZIP/Postal	Tax	Explanation
91134	6.5	Inside state/outside metro area
92305	7.5	Inside state/inside metro area
92288-5423	7.5	Inside state/inside metro area (ZIP + 4)
96544-2333	6.5	Inside state, outside metro area (ZIP + 4)
T5A 9S2	0.0	Canadian customer

Explanation

• This formula uses nested If-Then-Else operators.
• The first of these If-Then-Else expressions begins with the initial If and ends with the final "0". It says:
• If the first five characters in the {customer.POSTAL CODE} field are all numbers, then compute and print the sales tax using the methodology specified in the second If-Then-Else expression.
• If the first five characters are not all numbers, print "0".
• The second of the If-Then-Else expressions begins immediately after the initial Then and ends immediately before the final words Else "0". It says, now that we have already tested and know that the first five characters of {customer.POSTAL CODE} are numbers:
• If the first five characters of {customer.POSTAL CODE}, converted to numbers, fall in the range of 92200 to 92399, print "7.5" (the amount of state and local sales taxes combined).
• If the first five characters of {customer.POSTAL CODE}, converted to numbers, fall outside that range, print "6.5" (the amount of state sales tax only).
• If NumericText ({customer.POSTAL CODE} [1 to 5]) uses the If-Then-Else operator to test for the condition in parentheses:
• {customer.POSTAL CODE} [1 to 5]) uses the Subscript operator [] and Make Range operator to extract characters 1 to 5 in the {customer.POSTAL CODE} field.
• The NumericText function evaluates the extracted characters to determine if they are all numbers or not.
• If they are all numbers (YES) the first If condition is satisfied and the formula moves to the first Then consequence.
• · If they are not all numbers, the first If condition is not satisfied, and the formula moves to the final Else consequence.
• · Then if ToNumber({customer.POSTAL CODE} [1 to 5]) in 92200 to 92399 Then "7.5" Else "6.5" Else "0" shows what is to take place if the original If conditions have been satisfied.
• · ({customer.POSTAL CODE} [1 to 5]) uses the Subscript operator [ ] and Make Range operator to extract characters 1 to 5 in the {customer.POSTAL CODE} field.
• · ToNumber converts these extracted characters to a number that can then be used in a numeric expression.
• · in 92200 to 92399 checks to see if the {customer.POSTAL CODE} (now converted to a number) falls anywhere within the range 92200 to 92399.
• · If it does, the If condition (inside the parentheses) is satisfied and the Then consequence is performed by printing the text string "7.5".
• · If it does not, the If condition is not satisfied, and the Else consequence is performed by printing the text string "6.5".

Comments

These tax figures, rules, and Postal codes are for illustration only; they are not meant to accurately represent state and local taxing regulations.

Related topics

Formulas In Action