Formula 6

Operators/Functions Used

If-Then-Else, Greater than (>), Percentage (%), Greater than or equal (>=), Boolean Operator (And), Boolean Operator (Or), Parentheses ()

Formula Purpose

The manager of a minor league baseball team wants a column on the statistics report that flags all batting averages of .300 or better and all averages below .200. The manager does not want the batting average flagged for any player who has batted fewer than 100 times because the manager does not feel that such a batting average is statistically significant.

Formula

If ({file.ATBAT}>=100) And 

(({file.HITS} % {file.ATBAT}>=30) Or 

({file.HITS} % {file.ATBAT}<20)) Then 

    "####" 

Else 

    ""

Result

Hits	AtBat	Average	Flag	Explanation
31	98	.316		AtBat <100 (not significant)
31	101	.307	####	Average > 30 (.300)
43	216	.199	####	Average < 20 (.200)
19	99	.192		AtBat < 100 (not significant)

Explanation

• The formula uses the If-Then-Else operator to test for specific conditions.
• Condition A ({file.ATBAT} >= 100) uses the Greater than or equal operator (>=) to make certain that the batter has batted at least one hundred times. If he has batted 100 or more times, this condition is true, otherwise it is false.
• Condition B ({file.HITS} % {file.ATBAT} >30) uses the Percentage operator (%) to calculate {file.HITS} as a percentage of {file.ATBAT}. It then uses the Greater than or equal operator (>=) to test the resulting percentage to see if it is 30% (.300 batting average) or more. If the percentage is greater than or equal to 30, this condition is true, otherwise it is false.
• Condition C ({file.HITS} % {file.ATBAT} < 20) again uses the Percentage operator (%) to calculate {file.HITS} as a percentage of {file.ATBAT}. It then uses the Less than operator (<) to test the resulting percentage to see if it is less than 20% (.200 batting average). If the percentage is less than 20, this condition is true, otherwise it is false.
• The formula uses the Boolean operators And, and Or to evaluate conditions A, B, and C. The logical relationship required is Condition A and either Condition B or Condition C. Thus, the If part of the formula is satisfied if Condition A is TRUE, and either Condition B or Condition C is TRUE.
• If the If part of the formula is satisfied (Then), it flags the batting average by printing four pound signs "####".
• If those conditions are not true (Else), it prints nothing (as indicated by the empty text string "").

Related topics

Formulas In Action