How to Use the Formula Builder in Ninety

The Formula Builder lets you create a math formula that automatically generates a KPI's score, using other existing KPIs as variables. It's most useful when a number on your Scorecard is already the result of other numbers you're tracking — for example, gross profit is always sales minus cost of goods sold, so there's no reason to enter it manually every week. Owners, Admins, Managers, and Coaches can add or edit Formula Builder KPIs.

To enable the Formula Builder on an existing KPI, open the KPI's details card and toggle on Formula Builder at the bottom. To create a new Formula Builder KPI from scratch, click New KPI, select Create new KPI, fill in the details, and toggle on Formula Builder before saving.

The Formula Builder supports four operators, including order-of-operations formulas with parentheses:

• Addition
• Subtraction
• Multiplication
• Division

Adding KPIs as variables

The Formula Builder lets you use any existing KPI in your account as a variable in a calculation. The KPIs you reference don't need to be on the same Scorecard — they just need to be actively maintained somewhere in your account (not archived).

Example: Gross Profit. You're already tracking Sales and Cost of Goods Sold on your Scorecard and want Gross Profit to calculate automatically.

1. Click the Data from the left navigation.
2. Click New KPI on the intended KPI group.
3. Click Create new KPI.
4. Name the KPI “Gross Profit.”
5. Toggle on Formula Builder.
6. Click Add KPI.
7. Search for and click your existing "Sales" KPI.
8. Select the subtract operator.
9. Click Add KPI.
10. Search for and click your "Cost of Goods Sold" KPI.
11. Create a Goal.
12. Assign an Owner.
13. Click Save.

Adding constant variables

Constants let you include a fixed number in your formula that stays the same every period.

Example: Daily Average. You track a weekly KPI and want the daily average calculated automatically.

1. Click the Data from the left navigation.
2. Click New KPI on the intended KPI group.
3. Click Create new KPI.
4. Name the KPI "Daily Average."
5. Toggle on Formula Builder.
6. Click Add KPI.
7. Search for and click your existing "Weekly Sales" KPI.
8. Click the division operator.
9. Click Add Number.
10. Enter 7 and click the checkmark.
11. Create a Goal.
12. Assign an Owner.
13. Click Save.

Common formula examples

The following examples cover the Formula Builder patterns users ask about most. For each one, the formula assumes you've already enabled the Formula Builder toggle and are building your expression in the formula field.

Percentage of a total

Use the percentage of a total formula when you want to show what portion one KPI is of another. For example, closed deals out of total leads.

The formula: (Part KPI / Whole KPI) * 100

Here's how to build this example for your Scorecard: Click Add KPI and select your Part KPI, click the division operator, click Add KPI and select your Whole KPI, click the multiplication operator, click Add Number and enter 100, click the checkmark, then click Save.

Percentage change between two KPIs

Use the percentage change between two KPIs formula to compare two values and show the difference as a percentage. For example, this period's revenue vs. last period's.

The formula: (Current KPI - Baseline KPI) / Baseline KPI * 100

Here's how to build this example for your Scorecard: Click Add KPI and select your Current KPI, click the subtract operator, click Add KPI and select your Baseline KPI, click the division operator, click Add KPI and select your Baseline KPI again, click the multiplication operator, click Add Number and enter 100, click the checkmark, then click Save.

Simple difference

Use a simple difference formula to show how much one KPI has grown or changed relative to another. For example, new customers this period minus churned customers.

The formula: KPI A - KPI B

Here's how to build this example for your Scorecard: Click Add KPI and select KPI A, click the subtract operator, click Add KPI and select KPI B, then click Save.

Daily average

Use this to divide a weekly total into a daily average automatically. See the Adding constant variables section above for step-by-step instructions.

The formula: Weekly KPI / 7

Formula behaviors to know before you build

Formulas recalculate retroactively. When you change a Formula Builder KPI's formula, the new formula recalculates all historical periods automatically — not just future ones. There's no way to preserve past calculated values when editing a formula. If you need to change a formula going forward while keeping historical data intact, the cleanest workaround is to archive the existing Formula Builder KPI and create a new one with the updated formula, starting from the current period.

Formula cells are locked by default. When a KPI has an active formula, its cells display a lightning bolt (⚡) icon and are locked for manual editing. The score is calculated automatically each period. If you need to enter a value manually for a specific period, open the KPI's details card and toggle Manual Override on. Note that manually entered values won't be recalculated by the formula unless you turn Manual Override back off.

Clearing formulas

There are two ways to remove elements from your formula:

• Click the Backspace button in the formula window to remove one element at a time, starting from the end of the formula.
• Click X (Clear All) to empty the formula field entirely.

Permissions needed

Owners, Admins, Managers, and Coaches can add or edit KPIs, including Formula Builder KPIs. Team Members can enter data for KPIs they're assigned to, but can't create or edit them.

Frequently asked questions

Do the KPIs I use as variables need to be on the same Scorecard?

No. Formula variables can reference any KPI in your account — including KPIs from other teams' Scorecards. As long as those KPIs are actively maintained somewhere in Ninety (not archived), the Formula Builder can access their data. This makes it possible to build a leadership-level KPI that rolls up values from individual team Scorecards.

What happens if a variable KPI is missing data?

If a KPI used as a variable is missing data for a given period or has been archived, the affected cells will display an "Invalid" error. Once data is entered for the missing period (or the KPI is restored from the archive), the "Invalid" error clears, and the formula recalculates.

Can I use KPIs from one Scorecard timeframe as variables in a formula on another timeframe — for example, weekly data in a monthly formula?

No. Formulas can only reference KPIs within the same timeframe. A weekly KPI can only be used as a variable in another weekly KPI's formula. A monthly formula can only reference monthly KPIs, and so on.

If you want to see weekly data rolled up into a monthly view, use the View by filter on the weekly Scorecard. Setting View by to Month or Quarter aggregates your weekly data without requiring a separate monthly KPI. Note that this view uses the weekly data you've already entered — it doesn't create a separate monthly KPI or allow a formula to read from it.

Can I use a goal value as a formula variable?

No. Formula variables can only reference the actual data values entered for a KPI, not the goal set for it. If you want to calculate something based on a target, create a separate KPI to hold the target value, enter the target as data, and then reference both KPIs in your formula.

Can I reference a previous period's value in a formula — for example, this week vs. last week?

No. The Formula Builder reads each variable KPI's value for the current period only. There's no lookback, lag, or running total function. To compare periods (for example, this week vs. last week), maintain a separate "prior period" KPI that you update each period manually with the previous period's value, then reference both your current KPI and the prior-period KPI in your formula.

For year-to-date or cumulative totals, the Formula Builder can't automatically calculate across periods. A workaround is to maintain a separate KPI as a running YTD tracker and enter the cumulative value manually each period.

Can I change a formula for future entries while keeping historical calculations unchanged?

No. Changing a formula rewrites the calculated values for all historical periods — not just the current one and beyond. To preserve historical data while using a new formula going forward, archive the existing Formula Builder KPI and create a new one with the updated formula, starting from the current period.

Troubleshooting

My formula cell shows "Invalid."

An "Invalid" error means one or more of your variable KPIs is missing data for that period or has been archived.

1. Open each KPI used as a variable and confirm data has been entered for the affected period.
2. Check whether any variable KPIs have been archived. Go to the KPI Manager to view archived KPIs and restore any that should still be active.
3. Once the missing data is entered or the KPI is restored, the "Invalid" error will clear automatically.

If the issue persists, contact support.

My formula saved successfully, but no values are appearing in the cells.

1. Confirm that all KPIs used as variables have data entered for the affected periods. The formula won't calculate for any period where a variable has no data.
2. Try simplifying the formula temporarily to a single variable to identify which KPI may be missing data.
3. Check whether your formula uses more than 25 KPI variables. Exceeding this limit causes the formula to produce incorrect or missing output without an error message. If needed, split the formula across multiple Formula Builder KPIs.

If the issue persists, contact support.

My formula is obviously returning incorrect values — for example, the same incorrect number repeating across all periods.

This is the most common sign that your formula references more than 25 KPI variables. The Formula Builder's 25-variable limit is silent: it doesn't show an error, but results become unreliable once the limit is exceeded.

1. Count the number of KPI variables in your formula.
2. If the count exceeds 25, split the calculation across two or three Formula Builder KPIs (for example, Group A total and Group B total), then combine those results in a fourth KPI.

If the issue persists, contact support.

My Formula Builder cell is locked, and I can't enter data manually.

Formula Builder KPIs locks manual data entry by default. The locked cell shows a lightning bolt (⚡) icon by the KPI's title. To enter data manually for a specific period:

1. Open the KPI's details card.
2. Toggle on Manual Override.
3. Enter the value for the period.

Keep in mind that with Manual Override on, the formula won't recalculate for that period. Turn Manual Override off when you want the formula to resume calculating automatically.

My percentage formula shows the correct value in the weekly view, but the monthly or quarterly rollup shows a very different number.

This usually means the KPI's rollup setting is configured as Total instead of Average. Summing percentages across periods significantly inflates the result.

1. Open the KPI's details card.
2. Find the rollup setting and change it from Total to Average.
3. Save the KPI.

The monthly and quarterly rollup values should now reflect the correct averaged percentage.