Adding and or dividing sums of number columns

[heather.charest]

Hello Monday.com community.
I am trying to build a budget template in Monday.com. I need to build costs for several deliverables in one workspace. Using the sum columns, I would like to be able to add the sum of several groups within my workspace/budget and also divide these sum totals to get the gross margin. Anyone figure out how to create sum of a sum and or averages?

[timlittletech (Tim Little)]

@heather.charest try using the number columns on a dashboard,
Gross margin is a bit more difficult but you can kind of get the data using a couple of number columns and a pie chart on a dashboard.
Unfortunately at this time Dashboards don’t support doing calculations on column data so any total calculations like gross margin have to be done with workarounds like the pie chart I mention above.

[heather.charest]

Thanks for the reply. The challenge is that I am needing to calculate the sum of several formula columns as well as overall gross margin. I think this might be something beyond Monday.com’s capabilities

[PayWay369 (paytonwest@hotmail.com)]

was there an update on this? it is hindering our ability to move forward.

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi @PayWay369,

There is a solution through a third-party app called the Advanced Formula Booster, which completely reinvents formulas in monday.com. One of the key differences is that it does not use the formula column, but rather can cast the results to any column type (In this case, the target columns are numeric).

It has many other advantages, including a syntax editor that accommodates multiple lines, the use of variables to store temporary values, and many functions not available in monday.

Demo

One formula

For this, you need a single formula that runs in the Sales board:

1. The first line retrieves the name of the group the sales item is in, and stores it in a variable called [GroupName] (variables are in-between square brackets).
2. The 2nd line calculates the total of sales in this group and stores it in [CatTotal]
3. The 3rd line finds the position of the item with the same name as the item’s group in the Summary board.
4. The 4th line writes [CatTotal] to the Summary board item we identified in the previous line. This is achieved by using a position indicator: #[PosInSummary]
5. The 5th line calculates the gross margin by multiplying [CatTotal] by the % column of the Summary board.

You can simulate the formula directly in the app which greatly helps constructing the proper formula. Here is a simulation example:

One automation
For the formula to run, you simply add 1 automation to the Sales board:

In other words, a single automation is used to modify 2 columns in another board… You could actually do a lot more considering the Syntax Editor can take up to 100 lines.

Hope it helps.