The Formula Column - Explanation and Use Cases

Missing the Excel formula and mathematic capabilities? Not to worry! We came up with the formula column just for this. This column allows you to make calculations with other columns. It goes from simple mathematical equation to more complex functions and formulas. 

How to add it?

To add it, click on the + icon located on the top right of your board and select "column center".

Column_center.png

Once in the center, select the option "Formula" as in the screenshot below:

Screen_Shot_2018-07-05_at_12.34.51.png

How does it work? 

Once your column added, click on the right of your column title and pick "column settings". This is where you will add your formula. 

Screen_Shot_2018-06-21_at_16.58.55.png

You can also simply click inside the column and the formula window will appear. 

Tips to know before staring

  • To custom the unit of your column, use & "unit". For instance, if I want my result in Pounds, I will write at the end of the equation & "£"
  • Use the column drop-down in your column settings to make easy calculation between your columns.

formula.gif

  • If your formula is not working, check if you are missing a parenthesis or a comma. In a lot of cases, the formula fails because of these errors. 
  • You can use the formula on the following columns: votes, creation log, rating, checkbox, country, numbers, person, status and text. 

Use Cases

Calculate the monthly commissions of each sales representative

In this example, I want to calculate the total amount of commission made per employee per month in £.

Formula: ({Deals}*({% Commission}/100)) & "£"

large-Screen_Shot_2018-06-20_at_10.32.11.png

Calculate the monthly commmissions with conditions

Calculate if your employees are entitled to get a bonus. An employee can get a bonus only IF the "total sales" is higher than $350,000 AND IF the amount of deals is higher than 12. 

Formula: IF(AND({Total Sales}>350000,{Deals}>12),"$250","$0")

large-Screen_Shot_2018-06-20_at_11.53.38.png

Calculate the monthly change % between two values

I want to calculate the monthly change between the results of January and February. 

Formula: (({February Total Sales}-{January Total Sales})/{January Total Sales}) & "%"

large-Screen_Shot_2018-06-20_at_10.55.33.png

Calculate the roll-over budget

Calculate the rollover budget (planned cost vs. actual cost) to see how much money you have left for a project.

Formula: ({Planned Cost}-{Actual Cost}) & "$"

large-Screen_Shot_2018-06-20_at_13.43.30.pngCalculate the total expenses of a project

We are sending some of our employees to our NYC office and have a few expenses to take into consideration: the flights, the insurance, the hotel and the food expenses. 

Formula: SUM({Flight},{Hotel},{Insurance},{Expenses}) & "$"

large-Screen_Shot_2018-06-20_at_14.09.53.png

Find if your total expenses are over budget or in budget

My budget is $6500 per employee. I want to find out if the total amount I spent for each employee is in the budget or over it. 

Formula: IF((SUM({Flight},{Hotel},{Insurance},{Expenses}))>6500, "Over Budget","Good")

Screen_Shot_2018-06-21_at_20.18.35.png

Calculate a risk map for your investments

Create a risk map to estimate if an investment is high risk, medium risk or low risk. For that we need two data: the risk impact and the probability of the event happening. You can then add a status column to categorize each type of investment.

Formula: (({Risk Impact}*{Probability})/100) & "%"

large-Screen_Shot_2018-06-20_at_15.07.28.pngCalculate the average between several number columns

In this example, we want to calculate the average of the grades for the last 4 weeks

Formula: AVERAGE({Week 1},{Week 2},{Week 3},{Week 4})

 Screen_Shot_2018-06-21_at_9.58.31.png

Get a value based on several conditions 

If the student got 80 or more at his test, the result is "Excellent". If the result is above 50, it is "good". If it is above 30, you"can do better". If the result is below 30, then it is "terrible".

Formula: IF({Exam Score}>80,"Excellent",IF({Exam Score}>50,"Good",IF({Exam Score}>30,"Can do Better","Terrible")))

 large-Screen_Shot_2018-06-20_at_13.20.24.png

Cost calculation of a project

I want to calculate the cost to perform a task based on the fee per hour and the total amount of hours

Formula: ({Number of hours}*{Rate per hour}) & "$"

 Screen_Shot_2018-06-21_at_11.12.03.png

Calculate the profit margin

To calculate the profit margin of each of your project, you need three data: the revenue, the cost and the profit. From there, you apply the following formula:

Formula: ROUND({Profit}/{Revenue}*100, 1)& "%"

Screen_Shot_2018-06-21_at_12.23.39.png

Keep Track of your inventory

You can easily manage your inventory with monday.com. This example explains you how to calculate your current available stock and how much were sold in percentage.

Formula 1: {Purchased}-{Reserved}-{Sold}

Formula 2: ROUND({Sold}/{Purchased}*100,2) & "%"

Screen_Shot_2018-06-24_at_12.08.27.png

Keep Track of your shipments

This simple formula allows you to keep track of your shipments and easily find out if they are good to go in transit. There are a few options to calculate it but the most frequently used one is the following: 

Formula: H*L*W/6000

Screen_Shot_2018-06-28_at_10.28.17.png

 

If you have any further questions about the formula column please don't hesitate to reach out to our customer success team at [email protected]! We are here for you 24/7.  

 

Was this article helpful?
16 out of 17 found this helpful

Comments