Writing Formulas in Compensation Cycles: Function Reference Guide

Pequity’s formula engine allows you to build dynamic logic directly into your compensation cycle columns. Whether you’re calculating bonuses, validating ranges, or formatting text, this guide will help you understand the full set of functions available.

🛠 Custom Pequity Functions

These functions are unique to Pequity and designed specifically for compensation workflows.

`STRING(value)`

Purpose: Converts any value into a text string
Example:
STRING(@employee_id)
Use Case: Formatting employee IDs or combining data into readable text fields

`CONTAINS(string, substring)`

Purpose: Checks if a text string includes a particular substring
Example:
CONTAINS(@job_family, "Engineer")
Use Case: Apply logic based on job title, department, or location keywords

`RANGELOOKUP(co_dept, co_role, co_geo, co_level, attribute)`

Purpose: Pulls in a compensation range attribute (like low, mid, or high) using role-level attributes
Example:
RANGELOOKUP(@job_family_group, @job_family, @geo, @level, "co_salary_low")
Use Case: Validate proposed pay against set salary bands or ranges

📊 Standard Spreadsheet Functions

You can use many familiar spreadsheet functions when writing formulas.

🔍 Logical Functions

ISBLANK(value) — Check if a field is empty
IF(condition, value_if_true, value_if_false) — Add conditional logic
IFS(condition1, result1, condition2, result2, ...) — Multiple conditional checks
AND(condition1, condition2, ...) — All conditions must be true
OR(condition1, condition2, ...) — At least one condition must be true
NOT(condition) — Reverse a condition

➗ Math Functions

MOD(number, divisor) — Returns the remainder
ROUND(number, num_digits) — Round to nearest digit
MROUND(number, multiple) — Round to nearest multiple
MROUNDUP(number, multiple) — Round up to nearest multiple
MROUNDDOWN(number, multiple) — Round down to nearest multiple
AVERAGE(number1, number2, ...) — Calculate the mean of a set

🔤 Text Functions

LOWER(text) — Convert text to lowercase
UPPER(text) — Convert text to uppercase

📅 Date Functions

TODAY() — Returns the current date
DAY(date), MONTH(date), YEAR(date) — Extract parts of a date
DATE(year, month, day) — Combine numbers into a date
NETWORKDAYS(start_date, end_date) — Count working days between two dates (excludes weekends)

⚠️ Error Handling

IFERROR(value, fallback) — Use a fallback value if the formula produces an error

🧠 Example Use Cases

1. Merit Increase Calculation

=IF(
  @performance_rating = "Exceptional",
  @salary_increase * 1.2,
  @salary_increase
)

Use case: Cap the salary increase for top performers to avoid exceeding budget.

2. Range Validation

=AND(
  @new_salary >= RANGELOOKUP(@job_family_group, @job_family, @geo, @level, "co_salary_low"),
  @new_salary <= RANGELOOKUP(@job_family_group, @job_family, @geo, @level, "co_salary_high")
)

Use case: Ensure pay recommendations fall within approved salary ranges.

3. Prorated Bonus Calculation

=@target_bonus * (NETWORKDAYS(@start_date, TODAY()) / 260)

Use case: Automatically adjust bonuses based on employee start date.

💡 Pro Tips for Writing Formulas

✅ Validate inputs: Use ISBLANK or IFERROR to avoid surprises.
🔄 Handle edge cases: Always account for unexpected values.
👁 Keep it readable: Break complex logic into smaller columns if needed. Simpler formulas are easier to troubleshoot.

Need help writing a specific formula? Reach out to support or use our AI Assistant in the formula builder to get started faster.