What Other Options Are There Other the Formual for Babies

I've been using Notion for almost 3 years at this signal for my task, projection, knowledge, and business organization management.

My first Notion workspace was a page with what seemed like an endless list of to-do blocks.

Then I realised databases were a more than efficient style to organize what I needed to do, with filter and sorting abilities. This is now where I point people when starting in Notion, and to make things easier I made a starter workspace Template .

It but so happened that I found myself wanting to see more condensed views of information, and to summate certain things in my Notion space. That is when I explored formulas.

I was not completely lost having had experience with excel formulas, only with no knowledge of coding, it took a lot of fourth dimension and attempt to understand what I was doing in the formula holding.

My Notion workspace is ever changing only, having the noesis with formulas gives me another level of flexibility I tin can play with.

I don't work for Notion, nor practise they sponsor me for any of the work I exercise online, through my YouTube channel, consultations or digital products, however, like many people in the Notion customs, I love to share what I have learnt.

Here'due south the TOC:

1. When to utilize formulas
2. When non to use formulas
3. Formula fundamentals
4. How I create a formula
5. How I troubleshoot a formula
6. Limitations of formulas
7. Getting started with formulas

Or if you lot would prefer to have a look at a video case, you might enjoy this "Formula tutorial for beginners".

If you are looking for more examples of formulas in Notion yous can check out the Notion Formula tutorial playlist. Or go through my examples database on my public Notion page.

When to apply formulas

Notion opens up a world of possibilities especially when you start to include databases in your workspace. At some point, you will look into the advanced properties and meet relation, rollup and formula.

Formulas can do 1 thing, but it tin lead to many other options. A formula uses electric current information in a database, to form a dependant output.

This output can be used for 3 main things:

• Calculations
• Data Consolidation
• Viewing Flexibility

Simply before using a formula I ask myself these questions:

• Why practise I need this output?
• Can I already filter for this information?
• Is this going to solve a friction point?

Then, depending on those answers I will then wait into what type of formula I may need.

I will go into farther detail almost the types of formulas after on but, I will always look for a solution that doesn't crave a formula first.

Calculations

If I am looking at calculating something, I volition expect to encounter if I can apply a rollup to get the number.

Or I volition see if I can use the table calculations to display the number I am looking for, manipulating the linked database feature to use filters to find the information I am looking for.

If I can't notice a way to use a rollup or table function for calculations, then will I brand a formula to do the calculation for me.

It is worth adding at this point, I also take into consideration what I am going to be doing with the numbers afterwards.

The numbers at the bottom of the table cannot be referenced by a formula, so if I am looking to do more than with the number calculated, I would need to consider using a rollup or a formula.

Data Consolidation

When looking to brandish the information I am storing in a database, I want the ability to run into all that I need at a glance.

Viewing flexibility volition exist covered in a infinitesimal, but virtually of the time there are elements that I don't need to see, I but want an output that can exist calculated.

In this example, I merely want to see the initials of the person and their occupation in the overview. I tin and so see farther data when I become into the page.

Another common example is looking at progress. It isn't oftentimes that I wouldn't want to see tasks washed, left and the total, and then I consolidate that information in a progress property which gives me a colour for progress.

🟢 Greenish being completed.

🟡 Xanthous showing I take 1 task left

🔴 Red showing I have a flake more than work to do.

Essentially if in that location is a conclusion I am making from looking at multiple properties that can be displayed past i output then I use a formula.

It is worth mentioning, consolidating information can also be used in filtering and sorting abilities in a database view, aslope the calendar by options in the database views.

Viewing Information

When thinking well-nigh viewing information, I first look at the views available to me. The tabular array, list, board, calendar and gallery.

If showing the properties ends with something looking like this:

Then that is the time to use a formula to put those properties into one, which allows for a dissimilar look.

Maybe instead of showing lots of information in a list view like this:

I could consolidate that then modify to view information technology like this:

Nonetheless, another use that formulas tin have when looking to modify views is by creating a property that tin be used in the filter or sorting options in the database view.

In this example I have used the formula property to create a percentage which can at present be used in the filter and ask for a percentage greater than any value I choose.

So when database views may need properties to be summarized, or requires a formula belongings for farther filter and sorting options I will apply a formula.

It is as well worth noting that a formula holding can exist used in a rollup which can affect not only the view of the database it is in, but whatsoever database that is related.

When not to apply formulas

Now I dearest to use formulas in Notion considering it gives me so much flexibility but, that means more work to prepare upward your space.

When creating small formulas that piece of work on just a couple of arguments and so the return on investment (ROIJ) for the time spent is likely to pay off, however, if you are trying to calculate and consolidate information in a much larger more complex formula, and then looking at the ROI you may come to a dissimilar conclusion.

You tin get the same output with wildly different outcomes. Some very simple looking, some much more elaborate.

If the reply to any of these questions is No, then a formula is really not necessary.

• Practise I accept to see this in 1 property?
• Do I need a different way of seeing this?
• Am I going to utilise that formula?
• Is the time spent making this going to be worth it?

If you decide you exercise demand a formula then the questions focus less on what you want to see, and direct the focus on how your going to encounter it.

Formula fundamentals

Calculations

Because Notion has such a variety of possibilities when information technology comes to properties and input data, y'all can use numbers, text or dates to calculate a unlike output using mathematical calculations or, logical calculations.

Mathematical

The ability to do simple math betwixt constant and variable numbers, with dynamic updating downwards the database, leads to useful spreadsheet views.

Then you can get further into calculations using powers, working out remainders, or including logarithms.

If you're looking to summate averages or dispense numbers, you can notice the maximum and minimum of a fix of values, and rounding them up or down to whole numbers.

In addition to this, if you are working with numbers with different sign's you tin dispense the numbers in the formula box by turning them positive using abs(), or negative using UnaryMinus().

Something to add together before I motion on to talk about appointment calculations is that you tin can apply other backdrop in your calculations.

You lot can take numbers out of text, or change a select dropdown into a number.

Possibly your looking to count characters, words or votes in a specific page.

There are lots of options.

And so you tin can take the data that you are using in your databases, and commencement doing some calculations for data assay or comparison.

When looking at the appointment property in Notion, information technology gives y'all the option to pick a year, month, day, hour and minute. It besides allows you to select a starting time engagement and end date for the page.

Y'all can also select any role of the appointment belongings which means you can do calculations to find different values.

You lot may want to calculate the time until a borderline in days, weeks, months or years.

I know personally, I adopt to apply the now() function, as information technology volition automatically apply the current time within the formula.

You lot can run into in the first cavalcade 'now', in that location is the time I took the screenshot.

The other formulas are so using that for their calculations, meaning they will modify every bit the days become on.

A final thing to mention with calculations is that y'all can combine calculations inside of other formula conditions.

When combining conditions in formulas they can get large, circuitous and can be frustrating when creating them but, they are very powerful.

This is a quick case showing the days left, and that being used within the other formulas to check if the line is in the past, present or future.

Now inside those formulas, there are logical calculations which is why information technology is displaying equally a check box.

Logical

Logical calculations are checking to encounter if the question you ask is true or false. The term used to describe this is a boolean.

Y'all can use a boolean to check if numbers are equal, larger than, smaller than or combine the questions together.

This also works with text, as it volition count the characters in the text box to piece of work out if the length of the words are equal, larger than smaller than etc.

All the same, when looking at text or other weather condition used in formulas, it is more mutual to encounter questions being asked in the formula that relates to a specific output.

Some examples could be looking for a name, excluding a proper noun or finding missing data in a listing.

Perhaps yous take more than one condition to your question, and then you can add things together using 'and' or yous could include 'or' to refine the list of information you are looking for.

It is worth mentioning that when using a boolean alone in a formula, the results can often be accomplished using one of the advanced filters in a database view. The power starts to announced when you combine boolean'southward with dissimilar outputs.

So far I have asked yes or no questions expecting a true or false output. I tin still ask those yes or no questions, but inquire for a specific output depending on the question.

In the example higher up I have asked one question – is the person employed? If the answer is true then put ✅ and the proper noun of the person. If the respond is false put ❌ and the name of the person.

The second formula asks a question beforehand – is there a name? If there is no name put "Need a Name". If there is a name then ask the first question (is the person employed?) leading to the same answers as the first formula.

The logic of asking a question and it displaying different answers can be applied to any and all types of backdrop, text, numbers and dates leading to an almost endless possibility of outputs.

And then to summarize logical formulas, you can ask a question or multiple questions, and requite information technology the answers yous want to see for the yes or no consequence.

Information consolidation

Equally mentioned earlier in this mail service, consolidating information from multiple properties into 1 formula property tin can be done to reduce searching for what you need.

Calculations are a blazon of information consolidation giving you an output, just similar logical calculations give y'all an output.

It is by using nested logical questioning that can lead to much of the data condensing.

In the example to a higher place the formula has multiple questions with multiple answers, some with yes or no leading to a display, but i with an output of another property.

Is in that location a kickoff name? yes (carry on with other questions) : no (put "Need a First Name -")

Is at that place a last name? yes (carry on with other questions) : no (put "Need a Last Name -")

Are they employed? yes (put ✅ and person initials and carry on with other questions) : no (put ❌and person initials)

Are they working on a project? yes (carry on with other questions): no (put "No Project")

What stage are they at in the project? put the 'projection stage' they are at.

Now this case shows how you lot tin combine boolean logic, and regular outputting of information together, to create a condensed view of information.

This basically ways that if you have a gear up of questions with answers you can evidence, then a formula will let you brandish all of that in one property.

A discussion of warning. When creating formulas that are complex with lots of moving parts information technology can take a lot of fourth dimension. So making sure you have the understanding of how to troubleshoot the formula is critical to saving you time subsequently on.

Viewing flexibility

I am going to utilize the same formula as I did above and evidence how that alone tin can modify the fashion you lot could view that database.

Firstly in the calendar view, by selecting the formula holding I can now meet all of the information I would need in just one line.

The same affair is then possible with the gallery view.

But for me, in my ain personal apply case, I utilize a formula in a list view to show the information I need.

To give you a petty bit of context, this listing is my tasks for today (the twenty-four hour period I am writing this), which is located on my main dashboard.

I can see the task I need to practice in a list with the name of the chore on the left, the project the task is related to so a formula which shows my progress of the project (automatically calculated), and the fourth dimension I have left earlier the due date of the projection (automatically calculated).

Another apply for formulas when looking at viewing flexibility is to filter views of a database by an automatically calculated value.

An instance of this could exist that you are calculating a review date, action date or recurring date dynamically using a formula.

In the instance above in that location is a rollup date, 2 appointment properties and a formula date that has been selected for a database view.

So in summary, a formula can be used to view more information in a smaller space, view information with articulate explanations or, for filtering database views.

How I create a formula

Each formula will have unlike elements, functions and backdrop to achieve the desired output notwithstanding, there is a process that each formula requires.

Step i

Know what y'all want to see.

This sounds obvious, but when you are working with formulas y'all tin evidence something in multiple dissimilar means. Have a date as an instance.

You lot could view the engagement as a number for day of the week, solar day of the month or day of the yr. Information technology could be the weekday short, medium or the total name.

Then you could recollect near the week, month, year with minutes and hours included for the timestamp after, all having dissimilar options.

And then questions around formatting. Exercise you want spaces, symbols, emoticons in there?

Agreement what you desire the formula to look like is disquisitional before you start building.

Pace ii

Know what information you need.

When you understand what you desire to see, because most formulas work from other properties. The information yous need to create the desired output, needs to be in a place the formula tin access.

This may require calculation relations, rollups or other properties and then you can ask the questions you demand in the formula.

Stride three

Building the formula.

Anybody volition have their own process for creating a formula and with each formula, it may require dissimilar steps, for example:

A short formula peradventure like shooting fish in a barrel to conceptualize in your mind and blazon out in the formula box.

Notwithstanding,

A longer formula with more than queries may crave more planning and testing.

How I troubleshoot a formula

Troubleshooting a formula can be done during the cosmos of the formula, recently afterward completing the formula, due to a condition that may take been missed or, time after the formula due to potential additions or changes you desire to brand.

The Notion formula fault bulletin equally expressed in the adjacent department has it's pro's and cons', but for the purpose of troubleshooting, it is a nice indicator were in that location may be an upshot.

Having looked at the mistake message during the creation process, there could be an obvious fault, or 1 that requires more investigation.

Equally each style of formula may accept different error bug, I tin can't become over them all in this commodity but, they are normally down to 1 off or a combination of these things:

• Syntax fault (formula punctuation)
• Misspelt function (typically a missing letter or capital)
• Non plenty arguments (missing a question, normally an answer)
• Incorrect property reference (typically due to re-create and paste)

When trying to troubleshoot a formula yous have just completed, typically because of a condition you forgot to include, there are a couple of approaches.

• Read through and adjust the formula
• Pull apart the formula elsewhere to dissect it
• Use your cosmos process to assist with any changes

When I am creating larger formulas I accommodate how I create formulas to aid with troubleshooting them later on.

That modify in the process when creating the formula also helps when it comes to referring dorsum to the formula for a change weeks after completion.

Formula limitations

Non prison cell referenced

When information technology comes to the things that formulas currently tin can't practice in Notion, it relates to the way the work. Each formula is a property in a database, meaning that information technology volition apply to every page.

This is a big positive when yous are looking to apply the formula to all of the pages merely, because information technology is non prison cell referenced, if you only want to apply something to a specific page y'all would need to discover some other workaround.

Potentially using a rollup, showing the database in the page with self-referenced filters, or another solution that may work for the specific apply case.

Only horizontal

Due to the manner the formulas piece of work, yous can only utilise the data for the formula if the data is on the page.

This means that if y'all are looking for sums or totals of data from vertical values you would need to apply a combination of relations and rollups, or, employ the table calculations to get the wanted output.

Table calculations

Using table calculations to get a vertical value is useful but, in most cases that value needs to be used elsewhere. At present, due to the fact you lot can't use table calculations in formulas, you would need to use the rollup solution.

Tabular array calculations are useful for quick information for that view merely, for collecting data, rollups need be used if y'all demand the number in formulas elsewhere in your space.

Regular expressions

Equally Notion formulas use JavaScript, you can use regular expressions within a couple of the functions, replace() and replaceAll().

This tin can atomic number 82 to some really powerful solutions and combinations of information consolidation in the formula property only at the time of writing this there are some restrictions.

When using whatsoever control that includes \ earlier it, Notion will execute the formula, but will change it inside the box (information technology adds another leaving \\ which doesn't work), significant if you edit the formula afterwards on, you volition need to correct that part of the formula.

This is a elementary instance where you could use regular expressions, but for those people that want to expand on this using \ commands, they will be limited.

Formula error editor

The editor has lots of useful tools like the sidebar of function options, the window for office caption and the error message when there is something wrong.

However, when you get something similar this:

You can meet the fault is at graphic symbol 567 significant you need to either count all the way through the formula to discover the place it is talking virtually, work out the error from but looking over it or, what I do something, simply start again 🤦‍♂️.

Another limitation of the error message is that some of the mistake codes don't fit in the window.

Now almost of the time you tin find the location by using the information you have merely, it does mean yous need to work out exactly what the syntax fault is on your ain.

I tend to excerpt that part of the formula and work on it elsewhere, which I went over in more detail when troubleshooting a formula.

Formula editor scroll bar

When working with formulas they tin can become complex and potentially quite large. Unfortunately at the time of writing this, there is no scroll bar in the formula box.

In many cases, the size of the formula box isn't an issue but if y'all are looking to ask multiple questions in your formula, you lot may exceed the limit of the box.

What this means is that you need to navigate your way through the formula using your pointer keys, or ctrl + terminate to leap to the end.

This can plain affect the way you create formulas, which I went over in the section above.

Formula Wishlist

At that place are some things that are not available inside Notion currently but I thought information technology would be worth bringing up a couple of ideas.

• Assuasive formulas inside the page name property in the database.
• Inline formulas, so yous can use a formula inside the text on a page.
• Referencing page items, using page values in calculations.

If you have whatever other suggestions, let me know!

Getting started with formulas

Notion is not a boxed app and therefore does require upfront work to go used to it, and the manner things connect.

Formulas are like a new language to some, so putting the way Notion relates data, with the abilities offered by formulas, can result in a long journey.

Having explored other apps like Roam, ClickUp, Evernote and Taskade, alongside other competitors most notably Coda, few apps come up close to offering the same functionality for free.

Formulas have time to learn but are well worth exploring.

Requite them a go and let me know on Twitter (@DannyHatcher) what you think.

If you want to larn more than about Notion you can visit my YouTube channel or explore my Notion page for some more insights and ideas.

avilafithaly36.blogspot.com

Source: https://dannyhatcher.com/notion-formulas-why-when-and-how-i-use-them/

Share this post