A guide to create Notion formulas based on Select fields

A guide to create Notion formulas based on Select fields

Published
April 20, 2023

← All Articles

image

Do you want to learn how to create a rating in Notion based on select fields? I will show you an example of how to create a database to prioritize ideas based on the PIE framework. Additionally, I have summarized 3 simple prioritization methods.

In this article, you will learn how to create the formula yourself. You can also download the template and customize it for yourself. However, I recommend going through the instructions so that you can learn it right away and use the knowledge for other use cases.

image

Step-by-step guide on how to evaluate select fields with a formula.

image

Step 1: Create a database with the properties

First, create a database with the name "Ideas".

This example is created based on the PIE framework. Add the properties we need for it.

These can be expanded arbitrarily, for example, to include the status or a short description. For now, however, we focus on the most important fields for this calculation.

Create the following properties in your database:

  1. Potential Type = Select
  2. Importance Type = Select
  3. Ease Type = Select
  4. ★Bewertung Type = Formula
  5. #Bewertung Type = Formula

It will look like this:

image

Step 3: Define your options and evaluation criteria.

Please specify which options you want to use and which numerical value they are assigned to. In this example, we use "Very low" to "Very high" to make it easier.

image

Example:

Potential: Very high = 10

Potential: Very low = 0.1

If you are sure, you can create these options in the select fields. So in the fields Potential, Importance, Ease.

image
icon
Important: If you rename an option in a select field, you must adjust this in the formula. The same applies if you rename the database property.

Step 4: Formula for numerical value

The numerical values we defined above should now be stored in a formula. This assignment is made in a formula that defines the property #Bewertung

image

Here you can adapt the formula to your definition. Tip: Use it as given first before adapting it.

round(
	(
		if(prop("Potential") == "Sehr hoch", 10, 
		if(prop("Potential") == "Hoch", 7.5, 
		if(prop("Potential") == "Mittel", 5, 
		if(prop("Potential") == "Tief", 2.5, 
		if(prop("Potential") == "Sehr Tief", 0.1,
		 0))))) +
		if(prop("Importance") == "Sehr hoch", 10, 
		if(prop("Importance") == "Hoch", 7.5, 
		if(prop("Importance") == "Mittel", 5, 
		if(prop("Importance") == "Tief", 2.5, 
		if(prop("Importance") == "Sehr Tief", 0.1,
		 0))))) +
		if(prop("Ease") == "Sehr tief", 10, 
		if(prop("Ease") == "Tief", 7.5, 
		if(prop("Ease") == "Mittel", 5, 
		if(prop("Ease") == "Hoch", 2.5, 
		if(prop("Ease") == "Sehr hoch", 0.1,
		 0)))))
	) / 3 * 10
) / 10

prop(“Potential”) refers to the property in the database, in this example "Potential".

== "Sehr hoch", 10 defines the value of the option. In this example, Very high receives a 10.

icon
Important: You cannot directly paste this formula into the formula property. You have to do this with a small workaround, as the spacing between them is interpreted as an error by Notion.

How to:

  1. Copy this formula
  2. Paste it into the address bar of your browser
  3. From there, copy it again and paste it into the formula field #Bewertung.

It will look like this. But since this is hard to read, let alone edit, I use the above-mentioned workaround.

round((if(prop("Potential") == "Sehr hoch", 10, if(prop("Potential") == "Hoch", 7.5, if(prop("Potential") == "Mittel", 5, if(prop("Potential") == "Tief", 2.5, if(prop("Potential") == "Sehr Tief", 0.1, 0))))) + if(prop("Importance") == "Sehr hoch", 10, if(prop("Importance") == "Hoch", 7.5, if(prop("Importance") == "Mittel", 5, if(prop("Importance") == "Tief", 2.5, if(prop("Importance") == "Sehr Tief", 0.1, 0))))) + if(prop("Ease") == "Sehr tief", 10, if(prop("Ease") == "Tief", 7.5, if(prop("Ease") == "Mittel", 5, if(prop("Ease") == "Hoch", 2.5, if(prop("Ease") == "Sehr hoch", 0.1, 0)))))) / 3 * 10) / 10

Step 5: Formula for the rating in stars

We use the numerical rating for sorting. The assignment of stars makes it easier for the eye to rate them.

image

This is what the rating with stars looks like.

Define which label should be displayed for which values. In this example, we use 3 stars. "👀" means that no rating has been given yet.

Value
0
<4
<6
<8
Else
Result
👀
☆☆☆
★☆☆
★★☆
★★★
0 (No value)
less than 4
less than 6
less than 8
Other value

This formula goes into the database property ★Bewertung.

format(
if(prop("#Bewertung") == 0, "👀", 
if(prop("#Bewertung") < 4, "☆☆☆ " + format(prop("#Bewertung")), 
if(prop("#Bewertung") < 6, "★☆☆ " + format(prop("#Bewertung")), 
if(prop("#Bewertung") < 8, "★★☆ " + format(prop("#Bewertung")), 
"★★★ " + format(prop("#Bewertung"))
)))))

It will look like this:

image

Summary of creating a Notion formula

By assigning values to the select options, we can calculate and create ratings. To better understand formulas, I recommend copying and formatting them into a Notion code block. Remember to always perform this step to remove the formatting before copying the formula into the browser address bar (as described above).

3 simple prioritization frameworks

To help you choose the right prioritization framework, here is an overview of the 3 most common and simple ones. You can easily adapt the Notion formula to them.

Prioritization frameworks are useful tools for selecting the best concept from different ideas. They evaluate ideas based on specific criteria such as potential, importance, ease, impact, confidence, effort, or reach. They help with decision-making by providing a structured method for prioritizing ideas and can help ensure that resources are used effectively and that the goals of the company or project are achieved. There are different frameworks that can help with the prioritization of ideas, such as the PIE framework, the ICE framework, the RISE framework, or the Kano model. The choice of the appropriate framework depends on the individual requirements and goals.

PIE Framework (Potential, Importance, Ease)

The PIE framework evaluates ideas based on three criteria: Potential, Importance, and Ease.

The advantage of the PIE framework is that it is easy to understand and apply. It does not require any special knowledge or skills and can be used by any team member. Additionally, it is flexible enough to be adapted to the specific requirements and goals of the company or project.

Disadvantages of the PIE framework: It does not consider the difficulty of implementation or the costs associated with implementing an idea. It is also possible that some criteria are more important than others in some cases, depending on individual requirements and goals. Finally, the ratings are subjective and can vary from person to person.

image

Idea rating according to the PIE framework in Notion

Potential [1-10] refers to the potential impact of the idea.

A simple example of a promising idea could be to open a new restaurant with unique cuisine that is not yet available in the region. The restaurant could offer special dishes that are appreciated by locals and tourists alike. This could attract many customers and increase revenue. An idea with low potential, on the other hand, could be a change in the menu that only has a small impact on revenue.

Importance [1-10] indicates how important the idea is to the organization or project.

An example to explain the Importance rating could be a company that specializes in developing environmentally friendly and sustainable products. If an idea is presented for a new product that fits the company's vision, it could receive a high Importance rating because it contributes to the core business of the company and may provide a competitive advantage. Conversely, an idea for a product that does not fit the company's vision could receive a lower Importance rating because it would deviate from the main goal of the company and waste resources.

Ease [10-1] refers to how easy it would be to implement the idea.

The Ease criterion evaluates how easy it would be to implement an idea. An idea with a high Ease value (low effort) could be a small change to an existing product that can be quickly implemented. An idea with low Ease (high effort) could be a complex infrastructure that needs to be built to implement a new technology.

Each idea is rated on a scale of 1 to 10 in these three criteria. The ratings are multiplied together, and the ideas with the highest results are prioritized.

Formula: Potential * Importance * Ease = Priority

ICE Framework (Impact, Confidence, Effort)

The ICE framework is particularly suitable for prioritizing ideas within a project as it focuses on impact (Impact), confidence in implementation (Confidence), and effort (Effort). It is simple and does not require any special knowledge. It is fast and not as subjective as other frameworks, as it focuses on measurable criteria.

However, it does not take into account all important factors when prioritizing ideas, such as their importance to the organization or project. Additionally, the ratings of the different criteria can be difficult to compare, as they are rated on different scales.

image

Download the template

icon
image

Beli Löw

Beli is an IT project manager, tool enthusiast, entrepreneur and has organized his whole life with Notion. His news sources are release notes from tools. There is (almost) no feature or shortcut that he does not know.

Keep Reading…