How to Build a Sales Tracker With Nested Notion Formulas

You need a sales tracker that calculates commission, profit margin, and deal stage values automatically. Standard Notion formulas handle one function at a time, but nested formulas let you combine multiple operations inside a single property. This article shows you how to build a complete sales tracker using nested if, prop, round, format, dateBetween, and slice formulas. By the end, you will have a working database that computes key sales metrics without manual data entry.

Key Takeaways: Build a Sales Tracker with Nested Formulas

Database setup with at least 8 properties: Deal Name, Close Date, Deal Value, Stage, Commission Rate, Commission, Profit Margin, Days to Close, Stage Value.
Nested if formula for Stage Value: Maps each stage name to a numeric score using multiple conditions inside one formula property.
Commission and Profit Margin formulas: Multiply deal value by a percentage, then format the result as currency using format and round.
dateBetween for Days to Close: Calculates the number of days between the close date and today, nested inside a condition that only shows a value when the deal is closed.

What Nested Formulas Do in a Sales Tracker

A nested formula places one function inside another function. For example, you can put an if statement inside a round function, or wrap a prop reference inside format. This technique lets you create complex logic in a single property without needing multiple helper columns.

Before building the tracker, create a new Notion database with the following properties. All property types are listed next to each name:

Deal Name (Title): The name of the sales opportunity.
Close Date (Date): The expected or actual close date.
Deal Value (Number): The total dollar amount of the deal.
Stage (Select): Options: Prospecting, Qualification, Proposal, Negotiation, Closed Won, Closed Lost.
Commission Rate (Number): The percentage rate as a decimal, for example 0.10 for 10%.
Commission (Formula): Will compute the commission amount.
Profit Margin (Formula): Will compute the margin after a fixed cost percentage.
Days to Close (Formula): Will compute days from today to close date.
Stage Value (Formula): Will assign a numeric score to each stage.

All formula properties use the Formula property type. You will write each formula in the formula editor by clicking the property and selecting Edit formula.

Steps to Build Each Nested Formula

Step 1: Create the Stage Value Formula

Open the formula editor for Stage Value
Click the Stage Value property header, then choose Edit formula. The formula editor opens.
Enter the nested if formula
Paste or type this formula:
```
if(prop("Stage") == "Prospecting", 1, if(prop("Stage") == "Qualification", 2, if(prop("Stage") == "Proposal", 3, if(prop("Stage") == "Negotiation", 4, if(prop("Stage") == "Closed Won", 5, if(prop("Stage") == "Closed Lost", 0, 0))))))
```
This formula checks the Stage property and returns a number. Prospecting equals 1, Qualification equals 2, and so on. Closed Lost returns 0. The outer if wraps the next if until all stages are covered.
Close the editor
Click Done. Each row now shows a numeric score for its stage.

Step 2: Build the Commission Formula

Open the formula editor for Commission
Click the Commission property header and select Edit formula.
Enter the nested formula with round and format
Type this formula:
```
if(prop("Stage") == "Closed Won", format(round(prop("Deal Value")  prop("Commission Rate"), 2)), "")
```
The outer if checks if the deal is Closed Won. If true, it multiplies Deal Value by Commission Rate, rounds to two decimals, and formats the number as text. If false, it returns an empty string.
Add a dollar sign manually (optional)
Notion does not add currency symbols automatically. To show a dollar sign, wrap the entire result in concat:
```
if(prop("Stage") == "Closed Won", concat("$", format(round(prop("Deal Value")  prop("Commission Rate"), 2))), "")
```
Click Done.

Step 3: Build the Profit Margin Formula

Open the formula editor for Profit Margin
Click the Profit Margin property and select Edit formula.
Enter the nested formula with a fixed cost assumption
Assume a fixed cost of 60% of the deal value. Type:
```
if(prop("Stage") == "Closed Won", concat(format(round((prop("Deal Value") - (prop("Deal Value")  0.60)) / prop("Deal Value")  100, 1)), "%"), "")
```
This formula subtracts 60% cost from Deal Value, divides by Deal Value, multiplies by 100 to get a percentage, rounds to one decimal, and appends a percent sign. The outer if ensures it only calculates for Closed Won deals.
Close the editor
Click Done. Closed Won rows now show a profit margin percentage.

Step 4: Build the Days to Close Formula

Open the formula editor for Days to Close
Click the Days to Close property and select Edit formula.
Enter the nested formula with dateBetween
Type:
```
if(prop("Stage") == "Closed Won", dateBetween(prop("Close Date"), now(), "days"), "")
```
dateBetween calculates the number of days between the Close Date and the current date and time. The outer if shows the result only for Closed Won deals. For all other stages, the cell stays empty.
Close the editor
Click Done. Closed Won rows now show the number of days to close.

Common Issues When Building Nested Formulas

Formula returns “Invalid syntax” error

Notion requires exact punctuation. Check that every opening parenthesis has a matching closing parenthesis. Verify that double equals == is used for comparisons, not a single equals sign. Also ensure property names inside prop("") match the exact property name in your database, including capitalization and spaces.

Formula shows a number instead of formatted text

Formula properties can return numbers or text, not both. If you use round without format, the result stays as a number. To display a dollar sign or percent sign, you must wrap the number inside format() and then use concat() to add the symbol. Without format, the concat function will fail.

Stage Value shows 0 for every row

The most common cause is a typo in the stage name inside the formula. Open the Stage property and copy the exact text of each option, including capitalization. Paste that text into the formula between the double quotes. For example, if your stage is called “Prospecting” with a capital P, the formula must use “Prospecting” exactly.

Days to Close shows a negative number

A negative number means the Close Date is in the past. This is expected for deals that closed earlier. If you want to show only positive numbers, add an if condition that checks whether the date is before today. Use dateBetween(now(), prop("Close Date"), "days") to swap the order and get a positive number for past dates.

Notion Formula Functions Used in This Sales Tracker

Function	Purpose	Example Usage
`if`	Returns one value if a condition is true, another if false	`if(prop("Stage") == "Closed Won", 5, 0)`
`prop`	References another property in the same row	`prop("Deal Value")`
`round`	Rounds a number to a specified number of decimal places	`round(123.456, 2)` returns 123.46
`format`	Converts a number to a text string	`format(123)` returns “123”
`concat`	Joins multiple text strings together	`concat("$", "123")` returns “$123”
`dateBetween`	Calculates the difference between two dates in a specified unit	`dateBetween(prop("Close Date"), now(), "days")`
`now`	Returns the current date and time	`now()`

You now have a sales tracker with five working formula properties. The Stage Value property helps you sort deals by progress. The Commission and Profit Margin properties automate financial calculations. The Days to Close property tracks deal velocity. To extend the tracker, add a Rollup property that sums Commission across all Closed Won deals, or add a formula that calculates the weighted pipeline value by multiplying Stage Value by Deal Value.

← Back to WiseChecker Home More in Windows & PC