Formulas in Comp Cycles: Complete Function Reference Guide

Pequity’s formula engine in Comp Cycles allows you to automate compensation calculations, create dynamic fields, and streamline every stage of planning. If you’re familiar with Excel, you’ll find the experience intuitive—supporting arithmetic operations, conditional logic, date functions, aggregations, string functions, and more.

This guide provides a complete reference to every formula function supported in Comp Cycles, with examples and best-practice recommendations.

What You Can Do With Formulas

Formulas enable you to:

Calculate salary, bonus, and equity recommendations
Apply conditional logic based on performance, tenure, geography, or compensation rules
Build multi-factor compensation matrices
Prorate awards based on start date
Aggregate data across all employees in a cycle
Format text, convert types, and work with dates

💡 Tip: Use the Pequity AI Assistant to generate formulas automatically. Describe what you need and the AI will write and explain it for you.

1. Formula Basics

Referencing Columns

Use the @ symbol to reference any column in your cycle:

=@current_salary * 1.05
=@base_salary + @bonus
=@first_name & " " & @last_name

Important: Column names are case-sensitive.

Using @all for Aggregations

To reference values across the entire cycle, use @all.column_name inside aggregate functions:

=SUM(@all.current_salary)
=AVERAGE(@all.performance_rating)
=@current_salary / SUM(@all.current_salary)

Rules:

@all must be inside an aggregate function
Only one @all reference per aggregate function
Any change recalculates formulas for all employees

2. Operators

Type	Operator	Description
Math	`+ - * / ^`	Standard arithmetic
Comparison	`= <> < > <= >=`	Compare values
Text	`&`	Concatenate

Precedence: () → ^ → * / → + - → comparisons.

3. Math & Rounding Functions

Includes:

ROUND
ROUNDUP
ROUNDDOWN
MROUND
MROUNDUP
MROUNDDOWN
MOD
PRODUCT

These help create clean salary numbers, ensure consistent proration, and support multi-factor calculations.

4. Logical Functions

IF
IFS
AND / OR / NOT
ISBLANK
IFERROR

These are essential for tiered logic and eligibility rules.

5. Text Functions

Includes:

CONCATENATE
LEFT, RIGHT, MID
LEN, TRIM
UPPER, LOWER
SUBSTITUTE
FIND, CONTAINS
STRING

Great for naming conventions, IDs, and cleaning messy data.

6. Date Functions

Includes:

DATE
YEAR, MONTH, DAY
DATEDIF
TODAY, NOW
NETWORKDAYS

Commonly used for tenure, proration, eligibility windows, cliff calculations, and cycle end dates.

7. Aggregate Functions

Two modes:

Aggregate mode (with @all)

Values reflect entire cycle:

=SUM(@all.salary)
=AVERAGE(@all.rating)

Row-level mode (no @all)

Values apply per employee:

=SUM(@base, @bonus)

Functions: SUM, AVERAGE, COUNT, MIN, MAX.

8. Conditional Aggregates

Filter data before aggregating:

SUMIF
COUNTIF
AVERAGEIF
SUMIFS, COUNTIFS, AVERAGEIFS
MINIFS, MAXIFS
MEDIANIF, PERCENTILEIF

9. Statistical Functions

Useful for pay equity and EU transparency:

MEDIAN
PERCENTILE
QUARTILE
STDEV
VAR

10. Lookup Functions

RANGELOOKUP(dept, role, geo, level, attr)

Retrieves market data or configured salary ranges.

Example:

=RANGELOOKUP(@department, @role, @geography, @level, "midpoint")

11. Type Conversion

INT
VALUE
TEXT
COUNTA

12. Real-World Examples

Performance-Based Increase

=IFS(
    @performance_rating >= 4.5, @current_salary * 1.10,
    @performance_rating >= 3.5, @current_salary * 1.05,
    @performance_rating >= 2.5, @current_salary * 1.02,
    TRUE, @current_salary
)

Prorated Bonus

=@annual_bonus * (DATEDIF(@start_date, @cycle_end_date, "D") / 365)

Percent of Budget

=ROUND(@current_salary / SUM(@all.current_salary) * 100, 2)

Range Positioning

=(@current_salary - RANGELOOKUP(...,"min"))
 /(RANGELOOKUP(...,"max") - RANGELOOKUP(...,"min"))

13. Best Practices

Build formulas in simple increments
Use parentheses to avoid ambiguity
Handle blanks and errors gracefully
Always round money ($) values
Minimize unnecessary @all usage

14. Common Pitfalls

Pitfall	Solution
Division by zero	Wrap in `IFERROR`
Invalid date	Validate DATE() inputs
Typo in column	Double-check column labels
Incorrect @all usage	Only inside aggregation functions

15. Error Handling

Common issues:

Error	Meaning	Fix
`#DIV/0!`	Division by zero	Add IFERROR
`#NUM!`	Invalid date or DATEDIF	Validate dates
Column not found	Column mismatch	Correct @column

Pequity preserves last valid value if a formula errors, preventing data loss.

16. Useful Patterns

Cap at max

=MIN(@value, @max)

Floor at min

=MAX(@value, @min)

Keep within range

=MAX(@min, MIN(@value, @max))

Proration

=DATEDIF(@start_date, @cycle_end_date, "D") / 365

Need Help?

You can:

Ask the Pequity AI Assistant
Reference this guide
Test your formula step-by-step
Contact support@getpequity.com for assistance