Formulas - Driven Knowledge Base

The Formula tab, Define the formulas that calculate this incentive, is where performance becomes a payout. Formulas are written like a spreadsheet cell. Click a chip to insert a function or a value, or type it.

Formula tab with the default Payout formula, function chips and Live Preview

Ask AI on the editor drafts a formula from a sentence.

The payout formula

Every compensation has one formula named Payout, marked Default. This is the earning. You can edit it. You cannot delete it, and you cannot add a second earning formula.

Display formulas

Add formula creates a display formula, named New formula until you rename it. A display formula calculates a figure people should see, such as attainment or the rate that applied. It does not add a second payment on top of Payout. Each display formula has a Name and a Format: Currency, Percentage or Number. Renaming a formula updates other formulas that refer to it. The trash icon deletes a display formula.

What you can insert

Under the editor, chips are grouped:

Row	What it inserts
Functions	Shortcuts for common functions. These are shortcuts, not the full list.
Compensation	Target, the quota. Shown only when quotas are on.
KPI	The manual KPI, when the KPI is manual.
User variables	Base Salary, Variable Salary and any variable you have created.
Deal	Fields on the deal, such as amount. Row-based only. Totals compensations show Not available for period-based compensations.
Total	Total:Amount of deals, which is how many deals are in the calculation, plus a total for each extra column and each display formula.

A ghost chip on User variables is a variable that does not yet have a value for every person on the plan. Click it to fill the values in before you insert it.

Spreadsheet functions

Any built-in spreadsheet function works, not only the chips. The reference list is the HyperFormula list of available functions. The functions you will use most often: Logic

Function	What it does
`IF`	One test, two results
`IFS`	Several tests, and the result of the first one that is true
`AND` / `OR`	Combine tests
`SWITCH`	Match one value against a list
`IFERROR`	A fallback when a calculation has no result

Math and rounding

Function	What it does
`SUM`, `MIN`, `MAX`, `ABS`	Add, pick the smaller, pick the larger, drop the sign
`ROUND`, `ROUNDDOWN`	Round a number. `ROUNDDOWN` never rounds up.

Attainment functions

ATTAINMENT_PCT and ATTAINMENT_ABS are Driven functions. They are not in the spreadsheet list. Both need quotas to be on. Without a quota, ATTAINMENT_PCT() is zero. ATTAINMENT_PCT() is performance divided by the period quota, as a ratio. 1 is 100%. 0.8 is 80%. 1.2 is 120%. Compare it with 1, not with 100. ATTAINMENT_ABS() is the KPI value itself. In a row-based compensation it is the running KPI up to and including the current deal, which is what you want for an accelerator that changes rate as the person crosses quota mid-period. Do not rebuild attainment by dividing a column total by Target. The attainment functions already handle the quota window for people who joined mid-period.

Another compensation’s attainment

ATTAINMENT_PCT() with nothing inside is this compensation’s own attainment. A formula can use another compensation’s attainment for the same person and the same payout period. Pass that compensation to ATTAINMENT_PCT. In a row-based formula, every deal receives the same number. Deals are not matched one to one with the other compensation. This works when all of the following are true:

The other compensation is published, on this plan, and has quotas on.
Both compensations use the same KPI period and the same payout frequency.
That KPI period and payout frequency are the same as each other. A monthly KPI paid monthly can reference another one like it. A monthly KPI paid quarterly cannot reference attainment this way.

Two compensations can refer to each other. A compensation cannot pass itself. Use the empty ATTAINMENT_PCT() for its own attainment. Example. A bonus compensation, paid on the same schedule as New business, pays 10% of its own quota only when New business attainment is at least 100%. The test is that compensation’s attainment against 1. Below 100% the bonus pays nothing.

Examples

Replace Amount with the deal amount chip. These examples are row-based. For a totals compensation, use Target or a Total chip in place of Amount. Tiered accelerator. 5% until quota, 8% from quota, 12% from 120% of quota. IFS stops at the first test that passes, so write the highest tier first.

=IFS(ATTAINMENT_PCT() >= 1.2, Amount * 0.12, ATTAINMENT_PCT() >= 1, Amount * 0.08, TRUE, Amount * 0.05)

A €50,000 deal closed while attainment is 90% pays €2,500. The same deal closed after attainment has passed 120% pays €6,000. A cap. Never pay more than €5,000 on a deal.

=MIN(Amount * 0.1, 5000)

A €50,000 deal would be €5,000 at 10%, so the cap does not change it. A €80,000 deal would be €8,000, and the payout is €5,000. A floor. Pay at least €500 on a deal that earns commission.

=MAX(Amount * 0.1, 500)

A €2,000 deal at 10% is €200, so the payout is €500.

Extra columns

Extra columns are additional deal fields shown in the calculation table, such as stage or owner. Add them from the Columns menu on the preview. The KPI’s deal property is added for you when you pick it. Each extra column is Default or Cumulative. Default shows the deal’s own value. Cumulative shows a running total down the list. For running quota progress, prefer ATTAINMENT_ABS() over a cumulative column. A Total chip is added for each extra column, named Total: followed by the column name. Renaming the column does not break formulas that use its total.

​The payout formula

​Display formulas

​What you can insert

​Spreadsheet functions

​Attainment functions

​Another compensation’s attainment

​Examples

​Extra columns