This article describes the various types of calculations available in OrgPlus, and how to define and use formula fields.
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.
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 . The following is an example of the Formula Properties dialog box with the Equals function selected.
The following formulas are available in OrgPlus.
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.
- 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.
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.
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.
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.
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.