Missing Excel formulas? Not to worry! We came up with the Formula Column just for you. It's capable of nearly any calculation from simple mathematical equations to more complex formulas. With our functions, you can create standalone calculations or pull data in from other columns to give you the information you need. 🙌

 

How to add it

To add it, click on the + icon located on the top right of your board and select "Formula" from the resulting dropdown menu:

 

How it works

The formula builder will automatically open when you add the Formula Column. If you've exited the formula builder, simply click anywhere within the column to begin building your formula.  

In the formula builder, you'll first find a list of the columns on your board which you can use within the calculations that you create, followed by the list of available functions that we offer. To build a formula, you can use any of the available functions or you can use traditional math symbols.

If you hover over a function, the display will show you a description of the function and an example of how it works:

To add a formula, click into a cell within the Formula Column and begin to type out your formula. Here, you can use the columns from your board as well a variety of functions to build out the formula that you need!

 

Let's take our Company Budget Tracking board for example. Here, we want to easily show if our actual spending is over or under the budget that was allotted to it. We used the below formula to see this information at a glance!

 

What do the colors signify? 

You may have noticed that certain parts of your formula (functions, columns, punctuations) are colored within the editor! We refer to this as "syntax highlighting" and it allows you to build out your formula with ease, and to prevent or allow you to notice potential errors within your formula. Red coloring indicates an error in your formula.

Let's take the below formula for example, where we are attempting to subtract the "Actual Spending" column from the "Budgeted" column:

Here, we can see that there is a red parenthesis at the end of our formula. The reason for this is that that parenthesis is redundant and not needed! As soon as we delete the final parenthesis, our formula will be good to go. 🙌

 

General rules

• Using brackets

When building a formula, you may need to reference a column in order to pull that column’s value into your function. To do that, you’ll need to include the name of the column you’re referencing within these symbols: {}

It is important to use the columns’ name exactly as it appears on your boards. For example, this column:

Must be typed as “{Estimated SP}”. Like this:

SUM({Estimated SP},10)

This will take the value in the “Estimated SP” column and add 10 to it.

 

• Values are case sensitive 

Treat values as if they’re case sensitive. So, if your column name is “Estimate SP”, be sure to use a capital E, a capital S, and capital P in your formula.

This is correct: SUM({Estimated SP},10)

This is incorrect: SUM({estimate sp},10)

 

 

• The comma is dynamic

When used in the formula column, the comma symbol represents something different depending on the function. Take the above formula as an example. In this formula:

SUM({Estimated SP},10)

The comma represents an addition symbol, also known as +.

In the following function:

MULTIPLY({Estimated SP},2)

The comma represents a multiplication symbol, also known as x. 

When using the IF function, the comma represents the separation of each component of a conditional statement. More on that later.

 

• Using parentheses

When you open a statement, you must also close the statement. You open a statement with a ( and close it with a ). For example:

MULTIPLY(SUM({Estimated SP},10),2)

The above formula will take the Sum of the Estimated SP column plus 10 and multiply that total by 2.

 

• Using quotation marks 

When a statement reads text of any kind, whether a Status label or a Text Column, you must put the text in quotation marks. For example, this formula is looking for the word "Yes" in the Text Column:

IF({Text}="Yes","Awesome!","Too bad.")

 

 

• Formatting your formula

Using a text editor to write your formula and then copying and pasting will result in the formula builder reading it as illegal. The formatting of quotation marks and commas can impact the effectiveness of your formula. Take a look at the quotation marks in the following.

This is correct: IF({Text}="Yes","Awesome!","Too bad.")

This is incorrect: IF({Text}=”Yes”,”Awesome!”,”Too bad.”)

 

• Using decimals

When multiplying by a decimal, always include a 0 before the decimal. If not, your formula will return an "Illegal Formula" error.

This is correct: MULTIPLY(10,0.25)

This is incorrect: MULTIPLY(10,.25)

 

Compatible columns

Much like formulas in Excel, the Formula Column has its own logic. But not to worry! We're here to help you figure it out. The following are the column types which are compatible (supported) for use in the Formula Column, and columns that are incompatible (not currently supported).

 

Instantly create formulas using AI

 

The tool you've been waiting for is here! You can now instantly build the formula of your dreams, simply by stating what exactly you're looking to calculate using the brand-new monday AI. 🙌

To make use of this feature, start by clicking on any cell within your Formula Column. From there, click on "Formula builder", as shown below:

 

From the AI formula builder, you'll be able to ask the tool to generate any formula that you'd like to use for your board! For our event planning board, let's say that we want to multiply the number of attendees by the cost per person.

All we need to do is ask the AI assistant to create this formula for us, click the "Copy" button, paste it into the main formula editor, and edit it as necessary before clicking to "Set formula"!

 

Do note, as mentioned above, the AI formula builder is not able to read column names or values from your board. For this reason, after you copy the formula received from the AI formula builder, it is important that you change all column names (within the curly brackets {} ) to directly reflect the names of the columns used from your board!

 

Limitations

1. Any formula you add will apply to the entire column.

2. The Formula Column can only perform calculations horizontally (on the item level) and not vertically. For example, Item A cannot pull any information into a Formula Column on item B.

3. The Formula Column can read data from other column cells, but not from the column summary or any other area of the board.

4. It also can not read other boards, so a function like a VLOOKUP is not possible at this time.

 

The IF function

The IF function is one of the most commonly used formula types and it is used to create a conditional statement. If you aren’t familiar with conditional statements, we can help! The IF statement will return or output one of two values. Let’s use this as an example: IF(a=true,b,c)

 

Or put in simpler terms:

If a is true, then return b.

If a is false, then return c.

 

The result, whether the formula returns a value of b or c, is conditional upon whether or not a is true.

 

Let’s look at that using a board and the Formula Column. For this example, we want the Formula Column to read “YAY!” when a task is done. If the task isn’t done, we want the formula to say “Not yet…”. To achieve this, we would use this formula:

IF({Status}="Done","YAY!","Not yet...")

Take a look at the results of this formula:

You'll notice in the above results, that any label that isn't "Done" reads as "Not yet...". If I want to add to the IF statement so that the "Working on it" label outputs "Almost." in the Formula Column, I'll need to build from my original statement.

IF({Status}="Done","YAY!","Not yet...")

becomes

IF({Status}="Done","YAY!",IF({Status}="Working on it","Almost.","Not yet..."))

That means: IF(a=true,b,IF(c=true,d,e))

 

Or put in simpler terms:

If a is true, then return b.

If c is true, then return d.

If a and c are false, then return e.

 

You can build on this nesting of IF() statements endlessly. But remember! Anytime you open a parenthesis, you must close it. See the colorful parenthesis in the formula above for an example of how this works.

 

Customization

Just as with the Numbers Column, you can see the overall calculation for each group. You will also be able to change the unit and the function as below:

 

Use cases 

The possibilities with the Formula Column are endless! To get an idea of how the Formula Column can work for you, check out our article on Formula Use Cases as well as the board below.

For a list of all of the available functions, check out our Formula Library.

 

 

If you have any questions, please reach out to our team right here. We’re available 24/7 and happy to help.