Formula fields overview
Insperity Organizational Planning can be configured to add custom formula fields that work with your data to create powerful insight into your company’s charts. The most common formula fields include headcount, which calculate based on the number of employees in the chart, or salary totals, which calculates the sum of all the salaries within a determined range.
Adding a formula field
To add a formula field, go to Administration, then to Fields. Select Add Formula then complete the new field properties. When finished, click Save.
Popular formula fields
Below are a few of the most popular formula fields. These formulas are examples and your data source may differ in field availability and field names.
Headcount
Purpose: Calculates the number of direct and indirect reports for a manager.
Calculation:
- Type: Formula
- Categories: Lookup and Reference
- Functions: Count
- Entity: Person
- Range: Branch Exclusive
Span of control
Purpose: Calculates the number of direct reports only for a manager.
Calculation:
- Categories: Choose the “Lookup and Reference”
- Functions: Count
- Range: Direct Subordinates Exclusive
Total Salary (of Direct Subordinates & Manager)
Purpose: Calculates the salaries of all the direct reports of a manager, including the salary of that manager.
Calculation:
- Field Type: Formula
- Categories: Number
- Functions: Total
- Field: Salary
- Range: Direct Subordinates Inclusive
Average Salary (of direct subordinates)
Purpose: This formula calculates the average salary of a manager’s direct reports.
Calculation:
- Field Type: Formula
- Categories: Number
- Functions: Average
- Fields: Salary
- Range: Direct Subordinates
Average Age
Purpose: Calculates the average age across a range of direct and indirect reports.
- Calculation:
- Field Type: Formula
- Category: Number
- Function: Average
- Field: Age
- Range: Direct Subordinates Exclusive
Tenure
Purpose: Calculates the amount of time for an employee in years from hire until “today”.
Calculation: This calculation has multiple steps
Step 1: Tenure Days
- Categories: Formula
- Functions: Date/Number of Days
- Field 1: Today
- Field 2: “hire date”
- Range: Self
Step 2: Tenure Years
- Categories: Formula
- Functions: Number/Calculate
- Operand 1: Tenure Days
- Operand 2: Operator “/”
- Field: 365
- Range: Self
Gender Diversity
Purpose: Calculates the percentage of males and percentage of females within a branch.
Calculation: This calculation has multiple steps
Step 1: Calculate headcount (see Headcount formula)
Step 2: Total Males
- Field Type: Formula
- Category: Count
- Field: Gender = M
Step 3: Total Females
- Field Type: Formula
- Category: Count
- Field: Gender = F
Step 4: % Males
- Field Type: Formula
- Total males / headcount
Step 5: % Females
- Field Type: Formula
- Total females / headcount
Ethnicity Diversity
Purpose: Calculates the percentage of each ethnic category within a branch.
Calculation: This calculation has multiple steps. Follow the steps for Gender Diversity above, replacing Male or Female with the ethnic category.
Step 1: Calculate headcount (see Headcount formula)
Step 2: Calculate totals for each category as a separate step
- Total Asian— formula, conditional count when Ethnicity field = Asian
- Total Black— formula, conditional count when Ethnicity field = Black
- Total White—formula, conditional count when Ethnicity field = White
- Total Hispanic— formula, conditional count when Ethnicity field = Hispanic
Step 3: Calculate the percentage (count over headcount) for each category as a separate step
- % Asian—formula, percent, Total Asian / headcount
- % Black—formula, percent, Total Black / headcount
- % White—formula, percent, Total White / headcount
- % Hispanic—formula, percent, Total Hispanic / headcount