Formula Fields

Summary:

This article describes the various types of calculations available in OrgPlus, and how to define and use formula fields.

Procedure:

To define a formula field:

Click the Define Fields tool or click the Home tab and select Define Fields from the Fields & Formulas section to display the Define Fields dialog box.

KBFF_01

When you specify a field as a Formula, the Formula dialog box is automatically displayed. You can also edit an existing formula by clicking on the Formula button KBFF_02. The following is an example of the Formula Properties dialog box with the Equals function selected.

KBFF_03

Available Formulas

The following formulas are available in OrgPlus.

Numeric Formulas

The following formulas are for use with number fields.

  • Average: Compute an average of the selected number field for all specified boxes.
  • Count: Counts the number of specified boxes.
  • Non-zero count: Counts the number of specified boxes that do not contain a number field with a zero value.
  • Fraction of Total: Computes the percentage of a total. First sums the field across all specified boxes, and then divides into the field value for each box.
  • Maximum/Minimum: Determines the maximum or minimum value of the selected field across all specified boxes.
  • Product: Multiplies the values of the selected field across the specified boxes.
  • Standard Deviation: Computes the standard deviation for the selected field and boxes.
  • Total: Sums the selected field across the specified boxes.
  • Variance: Computes the variance for the selected field.

Note: With the exception of Count, all other selections are only enabled if a field of type Number exists. For example, if you want to compute an average salary, a salary field (or any Number field) must exist.

General Formulas

  • Equals: Perform a calculation using a field or set of fields. For example, performance change = current performance – last year’s performance.
  • Reports To: Get a value from a manager’s field.

Note: With the exception of Reports To, all other selections are only enabled if a field of type Number exists. For example, if you want to compute an average salary, a salary field (or any Number field) must exist.

Text Formulas

The following formulas are for use with text fields.

  • Upper: Converts a text field to all upper case.
  • Lower: Converts all letters in a text field to lowercase.
  • Trim: Removes all spaces from a text field except for single spaces between words.
  • Len: Returns the number of characters in a text field.
  • Count: Counts the number of specified boxes.

Date Formulas

The following formulas are for use with date fields.

  • Year: Returns the year from the selected date field.
  • Month: Returns the month (1-12) from the selected date field.
  • Day: Returns the day from the selected date field.

Chart Formulas

The following formulas allow you to access information about your chart hierarchy.

  • Level: Returns chart level for a box.
  • Sub-levels: Returns number of subordinate levels for a box.
  • Occurrences: Returns number of times a box appears in the chart based on the duplicate identifier (dotted line) key.
  • Allocation: Returns 1/ Occurrences. For example, if a person appears four times in chart their allocation is 0.25.

Based on the selected type of formula, you must select the boxes that you want to include in this calculation. You can specify boxes as follows:

  • Co-workers: All boxes sharing the same manager, including the manager’s assistants.
  • Level: All boxes on the same level in the chart.
  • Branch and Mgr: All boxes in a branch, including the branch’s manager.
  • Branch Excl. Mgr: All boxes in a branch, excluding the branch’s manager.
  • Dir Sub and Manager: All direct subordinates of a single manager, including the manager.
  • Dir Subordinates: All direct subordinates of a single manager, excluding the manager.
  • Entire Chartv: All boxes in the chart.
  • Current Mgr: A box’s direct manager (one level up).
  • All Mgrs Inclusive: The path of managers from the top of the tree down to that box, including the box.
  • All Managers Exclusive: The path of managers from the top of the tree down to that box, excluding the box.
  • Top of Chart: The number of boxes at the top level of the chart.
  • Self: The current box Click OK to save the formula.

You can modify the format of a format field by clicking in the corresponding format column.

Recalculating Formulas

Click the Home tab and select Calculation Options | Auto Recalculation to turn automatic recalculation of formulas on and off.

Click the Home tab and select Calculation Options | Recalculate Formulas to update all formula fields.

Caution: While Auto Recalculation is OFF, there is no indication that formulas are not up to date.

To exclude fields from calculations:

  • Select the boxes that you want to exclude from a calculation.
  • Click the Format tab and select Layout, then click the Advanced tab. The Box Properties dialog box Advanced tab is displayed.
  • Complete your selections as follows:
    • Exclude from Calculations: Select a set of fields to exclude from calculated functions.
    • Exclude from Counts: Select this option to exclude a box from count functions.
  • Click OK to save your changes and close the dialog box.