Formulas In Action

Formulas in Action have been developed to demonstrate the use of multiple functions and operators in coordination with one another. The formulas have been created to illustrate concepts; they do not represent the only way, or necessarily the best way, to achieve the desired effects.

The following is a listing of complex formulas created to illustrate the use of various operators and functions. Each formula topic includes:

• a report scenario that describes a real-world reporting need
• a formula using Crystal syntax that fills the need described in the scenario
• a dissection of the formula so you can understand the role of each function and operator.

The names of the operators and functions used in the formula follow each numbered formula topic.

Click the formula of interest from the following list for further information:

Formula 1 (sales management, determining representatives closest to hitting quota)

Abs(x), Subtract (-), Divide (/), Multiply (*), ToText, Concatenate (+)

Formula 2 (form letter/extracting first purchase date from customer number, use date to calculate # of years as customer, and use result to customize letter)

If-Then-Else, Subscript [], Less Than (<), Concatenate (+), Make range (to), ToNumber, Subtract (-), ToText, Parentheses

Formula 3 (inventory analysis based on extracting inventory data from codes imbedded in item numbers)

Concatenate (+), ToText, ToNumber, Multiply(*), Make Range, Subscript [], Parentheses ()

Formula 4 (sales compensation, calculating commissions, flag commissions that exceed certain amount)

Nested formulas, If-Then-Else, Subtract (-), Multiply (*), Greater than (>), Greater than or equal (>=), Sum, Parentheses ()

Formula 5 (form letter, soliciting orders against available credit line)

Nested formulas, If-Then-Else, Subtract (-), Not equal to (<>), Less than (<), Concatenate (+), ToText, To dollar ($), Negate ( -() ), Parentheses ()

Formula 6 (calculating one value as percent of another, flag percentages outside range, disregard statistically insignificant percentages)

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

Formula 7 (sales compensation, selecting fixed bonus or calculated commission, whichever is higher)

If-Then-Else, Subtract (-), Greater than (>), Maximum, Multiply (*), Parentheses ()

Formula 8 (purchasing, determining quantity to order based on average sales during rolling quarter)

If-Then-Else, Less than (<), Negate (-), Add (+), Round, Average([array]), Parentheses ()

Formula 9 (retail, calculating mail order sales tax based on customer ZIP or Postal code)

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

Formula 10 (staff scheduling, flagging weekend incoming calls)

If-Then-Else, Not, DayOfWeek, Make range (to), In range (in), Parentheses ()

Formula 11 (calculating a contribution based on face value of invoice, and then selecting calculated value or agreed upon minimum)

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

Formula 12 (determining monthly compensation based on percent of dollars saved, and comparing result to negotiated maximum)

If-Then-Else, Average([array]), Subtract (-), Greater than (>), Minimum([array]), Multiply (*), Parentheses ()

Formula 13 (converting one unit of measure to another)

ToText, Truncate, Division (/), Concatenate (+), Remainder

Formula 14 (customer service, determining and identifying warranty plan based on length of product serial number)

If-Then-Else, Length, TrimLeft, Less than or equal (<=), Parentheses ()

Formula 15 (form letter, personalizing salutation based on degree and sex of recipient)

Nested If-Then-Else operators, Not equal (<>), Boolean operator (And), Equal (=), Concatenate (+), Parentheses ()

Formula 16 (shipping, calculating discounted value of shipment and adding calculated freight charge to orders that do not meet "free freight" criteria)

If-Then-Else, Add (+), Less than (<), Multiply (*)

Formula 17 (form letter, splitting a mailing list in half and sending a different offer to each half of the list)

If-Then-Else, Remainder, ToNumber, Equal, Parentheses ()