Formula 11

Operators/Functions Used

If-Then-Else, Subscript [], Not equal to (<>), Maximum([array]), Multiply (*), Parentheses ().

Formula Purpose

As part of a charity fund raiser, a company agrees to donate 1% of the invoice amount (or $1.00, whichever is larger) for each invoice it cut during the previous quarter. The only invoices that it has exempted are Credit Invoices (identified with the letter "C" as the 6th character in the invoice number) and invoices for non-profit agencies (identified with the letter "N" as the 6th character in the invoice number). Management wants a Contribution column to appear on the quarterly detail sales report.

Formula

If {file.INV#}[6]<> "C" and {file.INV#}[6]<>"N" Then

    $(Maximum([.01 * {file.AMT}), 1])

Else

    0

Result

Invoice #	Amount	Amt. * 1%	To Charity	Reason
21523R	143.27	1.43	$1.43
21524C	223.46	N/A	0	«credit»
21538R	47.15	.47	$1.00
21575N	1312.49	N/A	0	«nonprofit»

Explanation

• The If part of the expression tests for two conditions: that the invoice is not a credit invoice and that it is not a non-profit invoice. Both conditions must be true to trigger the Then consequence.
• Condition A: {file.INV#} [6] <> "C" uses the Subscript operator [] to extract the 6th character of the value stored in {file.INV#}. It then compares that character to "C". If the character is not C (not a credit invoice), Condition A is satisfied.
• Condition B: {file.INV#} [6] <> "N" uses the Subscript operator [] to extract the 6th character of the value stored in {file.INV#}. It then compares that character to "N". If the character is not N (not a non-profit invoice), Condition B is satisfied.
• The Boolean operator and indicates that both Condition A and Condition B must be TRUE in order to satisfy the If part of the expression.
• If Condition A and Condition B are both TRUE, the If part of the expression is satisfied, thus triggering the Then consequence.
• .01 * {file.AMT} uses the Multiply operator (*) to multiply the amount of the invoice by 1% (.01).
• Maximum([.01 * {file.AMT}, 1]) returns the highest contribution for a given invoice: either 1% of the invoice amount or $1.00.
• The To Dollar ($) operator converts the amount to a dollar value.
• If either Condition A or Condition B is FALSE, or both are FALSE, the If part of the expression is not satisfied, thus triggering the Else consequences.
• If the If part of the expression is not satisfied, the formula returns the value 0.

Comments

An alternative rendering follows:

If {file.INV#}[6] in ["C", "N"] Then

    0

Else

    Maximum([.01*{file.AMT}, 1])

Related topics

Formulas In Action