--- title: "Formulas in Comp Cycles: Complete Function Reference Guide" description: "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.\n\nThis guide provides a complete reference to every formula function supported in Comp Cycles, with examples and best-practice recommendations." --- [Skip to content](https://helpcenter.pequity.com/support/formulas-in-comp-cycles-complete-function-reference-guide#main-content) English Show submenu for translations [More support](https://helpcenter.pequity.com/support/kb-tickets/new?hsLang=en) ![Logo (1)](https://helpcenter.pequity.com/hs-fs/hubfs/Logo%20(1).png?width=202&height=50&name=Logo%20(1).png) - [Pequity](https://pequity.com/) - [Platform](https://pequity.com/solutions) - [Blog](https://blog.pequity.com/) - [Login](https://pequity.app/login) - [Get a demo](https://pequity.com/contact) Open main navigation Close main navigation - [Pequity](https://pequity.com/) - [Platform](https://pequity.com/solutions) - [Blog](https://blog.pequity.com/) - [Login](https://pequity.app/login) - [Get a demo](https://pequity.com/contact) - English Show submenu for translations - [More support](https://helpcenter.pequity.com/support/kb-tickets/new) - Contact us Contact us How can we help you? - There are no suggestions because the search field is empty. 1. [Support Center](https://helpcenter.pequity.com/support?hsLang=en) 2. [Pequity Features & Workflows](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en) 3. [Pequity Comp Cycles](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#pequity-comp-cycles) # 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 - Create a support ticket for assistance - [Getting Started with Pequity](https://helpcenter.pequity.com/support/getting-started-with-pequity?hsLang=en#main-content) - [Pequity Setup & Company Admin Onboarding](https://helpcenter.pequity.com/support/getting-started-with-pequity?hsLang=en#pequity-setup-company-admin-onboarding) - [Personal Settings](https://helpcenter.pequity.com/support/getting-started-with-pequity?hsLang=en#personal-settings) - [Integrations & Data Management](https://helpcenter.pequity.com/support/getting-started-with-pequity?hsLang=en#integrations-data-management) - [ADP Lyric HCM Integrated Pequity](https://helpcenter.pequity.com/support/adp-lyric-hcm-integrated-pequity?hsLang=en) - [Managing Your Team in Pequity](https://helpcenter.pequity.com/support/managing-your-team-in-pequity?hsLang=en#main-content) - [General User Access](https://helpcenter.pequity.com/support/managing-your-team-in-pequity?hsLang=en#general-user-access) - [Single Sign-On (SSO) Integration](https://helpcenter.pequity.com/support/managing-your-team-in-pequity?hsLang=en#single-sign-on-sso-integration) - [People Manager Access](https://helpcenter.pequity.com/support/managing-your-team-in-pequity?hsLang=en#people-manager-access) - [Pequity Features & Workflows](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#main-content) - [Pequity Comp Cycles](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#pequity-comp-cycles) - [Pequity Ranges Tool](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#pequity-ranges-tool) - [Pequity People Tool](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#pequity-people-tool) - [Pequity Approvals Tool](https://helpcenter.pequity.com/support/pequity-features-workflows?hsLang=en#pequity-approvals-tool) - [Pequity Best Practices](https://helpcenter.pequity.com/support/pequity-best-practices?hsLang=en) - [Security & Compliance in Pequity](https://helpcenter.pequity.com/support/security-compliance-in-pequity?hsLang=en) - [Proactive Support & Troubleshooting](https://helpcenter.pequity.com/support/proactive-support-troubleshooting?hsLang=en) - [Pequity Updates & Insights](https://helpcenter.pequity.com/support/pequity-updates-insights?hsLang=en) - [Pequity University](https://helpcenter.pequity.com/support/pequity-university?hsLang=en#main-content) - [Comp 101](https://helpcenter.pequity.com/support/pequity-university?hsLang=en#comp-101) - [Reward Letters](https://helpcenter.pequity.com/support/reward-letters?hsLang=en) [![Pequity](https://helpcenter.pequity.com/hubfs/Pequity%20an%20adp%20company%20final%5B96%5D%20(1).svg "Pequity")](http://pequity.com) 1 ADP Boulevard Roseland, NJ 07068 Copyright © 2026 Pequity, an ADP Company