How to Master Notion's IF formula

2 August, 2023

How to Master Notion's IF formula

While the basic concept of the IF formula is straightforward, creating them can quickly become complex.

In this tutorial, I walk you through my technique for creating these formulas, ensuring that by the end you'll possess the skills to understand and create even the most complex IF formulas.

The is also a video tutorial on YouTube:

Template

For this blog, I'm using a basic sales template which you can duplicate here. In the template is a database with daily records for the entire year. The date is stored in the'Date' property, and each day's sales figures are recorded in the 'Sales' property.

Aim

I want to create a new property that will show if I'm hitting my daily sales target and I'll use the IF formula to create it. For this example, my sales target is to have between 5 and 10 sales a day. Anything less than 5 is below target and greater than 10 is above target.

Step 1: Create a new property

• Create a new property and set the type as 'formula'.
• Click on 'Edit', to create the formula

Step 2: Start slowly

Rather than immediately diving into creating the IF formula, it's helpful to build it incrementally. If you start writing the IF formula out in full, straight away it's often difficult to work out where any problems are.

• Begin by writing a formula that'll extract all records that are above the sales target. In this case, we're looking for sales greater than 10.

prop("Sales") > 10

• This adds a checked check box in all records where the sales are above 10, as shown here ⬇

Step 3: Constructing the IF formula

Notion has two options for writing IF statements, I prefer to use this one as I find it more user-friendly and scalable.

To illustrate this, let's expand the formula further:

• Type in the formula below. It sets the value to 'Above Target' for all days with more than 10 sales, all other records should be set to a blank value.

prop("Sales") > 10 ? "Above Target" : ""

Now the days with more than 10 sales have been set to 'Above Target'

Step 4: Finalizing the Formula

The final stage is to complete the formula:

• Type in the below formula. This sets the value to 'Above Target' for all days with more than 10 sales, sets it to 'Below Target'and for days with less than 5 sales, and set all other records to "On target."

prop("Sales") > 10 ? "Above Target" :prop("Sales") < 5 ? "Below Target" : "On Target"

• The database values are then set to this:

Formula illustration

Improving the formula

To enhance the property's readability, I want to now put icons instead of text into the target property's value:

• First, rename the property
• I use the code below to put a green circle to represent 'Above Target', a red circle for 'Below Target' and a yellow circle for all records 'On Target'.

(prop("Sales") > 10) ? "🟢" : ((prop("Sales") < 5) ? "🔴" : "🟡")

tip: to get the icon menu to appear in the formula. For windows hold the Windows logo key + . (period). For Mac Press Control-Command-Space

Conclusion

Congratulations! You've successfully written your first IF statement in Notion. Remember, it's best to construct these formulas in stages, testing each step's functionality, rather than attempting to create the entire formula in one go. This method ensures that any errors can be located and resolved more efficiently.

Part 2 of this blog will show you how to write longer IF formulas...coming soon!