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