## How formulas work in Pave

Formulas let you create calculated columns that derive values from other data in the cycle. You write them using a templating language called Handlebars. If you've written spreadsheet formulas, the concepts are the same: reference a cell (here, a column), apply a function, get a result.

Every formula goes inside double curly braces: `{{ }}`. Inside the braces, you reference employee data with `columns.fieldName` and call built-in functions (called helpers) by name.

### Your first formula

To output an employee's base pay, just reference it:

```
{{columns.basePay}}
```

To multiply base pay by 5%:

```
{{multiply columns.basePay 0.05}}
```

To nest one function inside another, wrap the inner one in parentheses:

```
{{round (divide columns.annualBonusCash columns.basePay) 2}}
```

This divides bonus by base pay, then rounds to 2 decimal places.

That's the entire syntax. The rest of this guide covers what helpers are available and how to combine them.

## If/else: choosing between values

This is the most commonly used pattern. Use `#if` to branch based on a condition.

**Simple if/else:**

```
{{#if columns.isPromotion}}
  {{multiply columns.newBase 0.10}}
{{else}}
  0
{{/if}}
```

**Multiple branches (if / else if):**

```
{{#if (eq columns.level "P1")}}0.10
{{else if (eq columns.level "P2")}}0.15
{{else if (eq columns.level "P3")}}0.20
{{else}}0
{{/if}}
```

**Inline if (one-line, inside another expression):**

```
{{add columns.salary (inlineIf columns.eligible columns.bonus 0)}}
```

**Unless (opposite of if):**

```
{{#unless columns.isExcluded}}
  {{columns.salaryRaise}}
{{/unless}}
```

Every `{{#if}}` must end with `{{/if}}`. Every `{{#unless}}` must end with `{{/unless}}`.

## Comparison helpers

Use these inside `#if` to compare values. They return true or false.

| Helper | Meaning | Example |
|----|----|----|
| `eq` | Equals | `(eq columns.country "Japan")` |
| `ne` | Not equals | `(ne columns.variablePay 0)` |
| `gt` | Greater than | `(gt columns.tenureDays 1095)` |
| `gte` | Greater than or equal | `(gte columns.daysInRole 365)` |
| `lt` | Less than | `(lt columns.startDate '2025-01-01')` |
| `lte` | Less than or equal | `(lte columns.compaRatio 1.0)` |

## Logical helpers

Combine multiple conditions.

| Helper | Meaning | Example |
|----|----|----|
| `and` | True if all are true | `(and (eq columns.dept "Sales") (gt columns.tenure 1))` |
| `or` | True if any are true | `(or (eq columns.level "P1") (eq columns.level "P2"))` |
| `not` | Flips true to false | `(not columns.isPromotion)` |
| `includes` | True if text/list contains a value | `(includes columns.tags "Sales")` |
| `isNullOrUndefined` | True if the field is empty | `(isNullOrUndefined columns.bonusOverride)` |

## Math helpers

| Helper | What it does | Example |
|----|----|----|
| `add` | Adds numbers | `{{add columns.basePay columns.variablePay}}` |
| `subtract` | Subtracts | `{{subtract columns.newBase columns.basePay}}` |
| `multiply` | Multiplies | `{{multiply columns.basePay 0.05}}` |
| `divide` | Divides | `{{divide columns.basePay 12}}` |
| `round` | Rounds to N decimals | `{{round columns.value 2}}` |
| `ceil` | Rounds up | `{{ceil (divide columns.shares 100)}}` |
| `floor` | Rounds down | `{{floor (divide columns.shares 100)}}` |
| `max` | Largest of the values | `{{max columns.a columns.b}}` |
| `min` | Smallest of the values | `{{min columns.a columns.b}}` |
| `abs` | Absolute value | `{{abs (subtract columns.a columns.b)}}` |
| `mod` | Remainder after division | `{{mod columns.x 2}}` |
| `power` | Raises to a power | `{{power columns.x 2}}` |

### Rounding to specific increments

The `round` helper rounds to decimal places. To round to the nearest $100 or $1,000, combine `round` with `multiply` and `divide`:

```
Nearest $100:    {{multiply (round (divide columns.newBase 100)) 100}}
Nearest $1,000:  {{multiply (round (divide columns.newBase 1000)) 1000}}
Nearest cent:    {{divide (round (multiply columns.newBase 100)) 100}}
```

## Date helpers

| Helper | What it does | Example |
|----|----|----|
| `dayDiff` | Days between two dates | `{{dayDiff '2025-01-01' columns.startDate}}` |
| `monthDiff` | Full months between two dates | `{{monthDiff columns.startDate '2026-02-01'}}` |
| `duration` | Human-readable tenure | `{{duration columns.startDate '2026-02-01'}}` |
| `dateAdd` | Add days to a date | `{{dateAdd columns.startDate 90 'days'}}` |
| `dateFormat` | Format a date for display | `{{dateFormat columns.startDate 'MM/dd/yyyy'}}` |

## Text helpers

| Helper | What it does | Example |
|----|----|----|
| `concat` | Joins strings | `{{concat columns.firstName " " columns.lastName}}` |
| `split` | Splits text; optional index returns one piece | `{{split columns.fullName ' ' 0}}` (first name) |
| `substring` | Extracts part of a string | `{{substring columns.level 0 1}}` (first character) |
| `replace` | Replaces text | `{{replace columns.salary ',' ''}}` (strip commas) |
| `formatCurrency` | Formats a number as currency | `{{formatCurrency columns.basePay 'USD'}}` |
| `toNumber` | Converts text to a number | `{{toNumber columns.salaryFromUpload}}` |
| `toString` | Converts a number to text | `{{toString columns.level}}` |
| `toUpper` | Uppercases text | `{{toUpper columns.country}}` |
| `toLower` | Lowercases text | `{{toLower columns.email}}` |
| `titleCase` | Capitalizes each word | `{{titleCase columns.name}}` |

## Handling empty values

| Helper | What it does | Example |
|----|----|----|
| `coalesce` | Returns the first non-empty value | `{{coalesce columns.override columns.default}}` |
| `isNullOrUndefined` | True if a field is empty (use inside `#if`) | `{{#if (not (isNullOrUndefined columns.bonus))}}...{{/if}}` |

## Saving intermediate values with setVar

When your formula needs the same value in multiple places, or when a calculation has several steps that are hard to follow as a single nested expression, 

```javascript
setVar
```

 lets you break it into named pieces. You assign an intermediate result to a name, then reference that name later in the formula as if it were a column. This is especially useful for multi-step calculations like proration (compute daily pay, compute days worked, then multiply), formulas that reuse the same sub-expression (compute a base amount once, then use it in both a percentage and a dollar calculation), and lookup-driven logic where you store a converted value (like an FX-adjusted salary) before applying further math to it. Without 

```javascript
setVar
```

, these formulas would require deeply nested expressions that are difficult to read and debug.

For long calculations, save partial results into a named variable to keep things readable.

```
{{setVar 'dailyPay' (divide columns.basePay 365)}}
{{setVar 'daysWorked' (dayDiff columns.startDate '2025-12-31')}}
{{multiply dailyPay daysWorked}}
```

## Working with lookup tables (setMapVar / getMapVal)

Some fields contain structured data (like band values with min/target/max). Use `getMapVal` to pull one value out.

```
{{getMapVal columns.equityBandRawValues 'target'}}
```

You can also create your own lookup tables inline. This is commonly used for FX rates or level-to-tier mappings:

```
{{setMapVar 'fxRates' '{"USD":1,"EUR":1.08,"GBP":1.27}'}}
{{multiply columns.basePay (getMapVal fxRates columns.currency)}}
```

## Controlling whitespace

Formulas that span multiple lines can produce unexpected whitespace in the output. Add `~` inside the braces to strip whitespace from that side:

```
{{#if columns.isPromotion}}
  {{~columns.newBase~}}
{{else}}
  {{~columns.basePay~}}
{{/if}}
```

The `~` strips whitespace from whichever side it's on. `{{~value~}}` strips both sides. `{{value~}}` strips only the right side.

## Common recipes

These are the most-used patterns across Pave customers.

**On-target earnings:**

```
{{add columns.basePay columns.variablePay}}
```

**Percent change between current and new total cash:**

```
{{divide (subtract columns.newTotalAnnualCash columns.currentTotalAnnualCash) columns.currentTotalAnnualCash}}
```

**Pro-rated bonus by start date:**

```
{{multiply columns.annualBonusCash (divide (dayDiff columns.startDate '2025-12-31') 365)}}
```

**Cap a raise at zero (no negative raises):**

```
{{max (subtract columns.newBase columns.basePay) 0}}
```

**Compa-ratio:**

```
{{divide columns.basePay (getMapVal columns.salaryImpact 'bandMid')}}
```

**Monthly salary from annual:**

```
{{divide columns.basePay 12}}
```

**Hourly rate (2,080 working hours/year):**

```
{{divide columns.basePay 2080}}
```

**First name (for reward letters):**

```
{{split columns.fullName ' ' 0}}
```

**Multi-step calculation:**

```
{{setVar 'increase' (multiply columns.basePay 0.025)}}
{{setVar 'days' (dayDiff columns.startDate columns.effectiveDate)}}
{{divide (multiply increase days) 365}}
```

## Tips

* Use the **live preview** in the column editor to test your formula against real employee data before saving.
* Strings need quotes (`"Sales"` or `'Sales'`). Numbers don't.
* Whitespace and line breaks inside `{{ }}` don't affect the result. Use them to keep complex formulas readable.
* Combine operations by nesting with parentheses: `{{multiply (add a b) c}}`.




Writing formulas with Handlebars

Configurations overview

Cycle access permissions

Overview

Employee eligibility

Approval chains

Planning windows

Budgets

Recommendation logic

Adding non-integration data to your cycle

Reward letters

Cycle Setup

Columns

OTE planning

Hourly and monthly pay

Columns and Formulas

Using the planner worksheet

Using the employee detail view

Cycle Progress dashboard

Budgets dashboard

Cycle Outcomes dashboard

Approval Statuses 

Worksheet Experience

Overview of smart features in compensation planning

Peer Comparison

Smart Flags

Priority Sort

Compensation Outliers

Manager Compression Flag

Vesting Drop Flag

Flags and Insights

Compensation Planning

Connect ADP

Connect BambooHR

Connect Dayforce

Connect Deel

Connect Gusto

Connect HiBob

Connect Workday

Connect Namely

Connect Paylocity

Connect Personio

Connect Rippling

Connect UKG Pro

Connect CharlieHR

Connect Humaans

Connect Justworks

Connect Sapling

Connect SAP SuccessFactors

Connect TriNet

Connect Zenefits

HRIS Integrations

Connect Carta

Connect ComputerShare

Connect Shareworks

Connect E*Trade

Connect Fidelity

Connect Global Shares

Connect Pulley

Connect Schwab

Connect Ledgy

EMS integrations

Connect Culture Amp

Connect Workday Performance

Performance integrations

Connect Ashby

Connect Greenhouse

Connect Lever

Connect Workday ATS

ATS integrations

Manage your connections

Upload custom reports

Pave API

Integrations

Team View Overview

Team View Column Configuration

Team View Customizations

Re-ordering Columns

Deleting Columns

Adding Columns

Team View Settings

Team View

How does Pave Work?

Intro to Pave

Contact Us

Pave 101

Getting Started

Grades

Setting up Company Job Architecture

Company Job Architecture

Job Architecture 101

Pay Zone Manager

Step 1: Build Foundation

Generate Bands from Market Data

Editing Bands

Calculated Columns

Bands Set Audit Log

Bulk & Manually Associate Market Data to Bands

Employee Distribution in Bands

How to duplicate band sets

Managing & Reordering Calculated Columns

Unmatched Employees

View changes

Pay Types: Calculated TDC, Annual Equity, and Refresh Equity

Manually Create New Band Sets

Band Sets

Upload/Download Bands

Step 3: Update Ranges

Advanced Filters

Compare Bands

Generate Bands for New Jobs in Your Job Architecture

Relative Permissions

Ongoing Management & Analysis

Edit and apply benchmarks

Set weights and logic

Map data sources to jobs

Supported survey providers

Manage Pave data sources

Manage survey data sources

Benchmarks overview

Survey aging

Step 2: Benchmarking

Market Pricing

Create an account and log in

Set up single sign-on (SSO)

Account setup

Configure role permissions

Create and manage roles

Roles and permissions

Impersonate a user

Manage notifications

Complete your company profile

Manage company settings

Platform settings

Account & Permissions

Create and manage Peer Groups in Market Data

Create and manage Market Data reports

Equity data benchmarking

Explore data composition

Explore the Pave job catalog

Filter Market Data benchmarks

Get started with Market Data

Share Market Data externally

Market Data Benchmarks (Features & Workflows)

Understand Calculated Benchmarks

Understand Consistency Labels

Understand job matching

Understand Market Data methodology

Understand job levels

Market Data Methodology 

Explore Global Location Insights

Explore Equity Practices

Other Pave Market Data Products 

Market Data FAQ

FAQ's

Upgrade to Market Data Pro

Upgrades & Pricing 

Market Data

Understanding Equity in Total Rewards

Display Original Grant Value

Compensation Simulator Configurations

Compensation Simulator Overview

Learning Hub Configuration

Learning Hub Overview

Reward Letters

Accessing your Total Rewards portal

Total Rewards 101

Visual Offer Letters with Workday

Visual Offer Letters with Greenhouse

Visual Offer Letters with Lever

Visual Offer Letter as a Candidate

Previewing Visual Offer Letters

Visual Offer Letters with Ashby

Creating and sending Visual Offer Letters in Pave

Visual Offer Letter 101

Why is my equity different in the equity tab on the left hand side compared to the equity valuation on the graphs?

Why can't I view my own Total Rewards portal?

Total Rewards and Visual Offer Letter FAQ

How can I complete our Greenhouse connection if "API credential management" is not in my Greenhouse menu?