I am working on a new formula to calculate the percentage of the project duration. (Example using easy numbers: if the project is scheduled to be 100 days long, and we are 30 days into the project, the percentage should be 30%.)
I have worked through this and run into a couple issues. One issue is particularly concerning because it seems that the math of the formula is just wrong, but I am hoping that is not the case and that I have just made a mistake somewhere in my work flow.
The screenshot below shows the columns involved in this formula.
The screenshot below shows the formula I am using for the percent column.
I have 2 main issues happening:
The math is incorrect. Using the first row as an example. The calculation should be 155/193. On a calculator this populates a value of .803. Monday formula is calculating this as .077.
The second issue is the formula is working for row 1, but outputting an error on row 2 even though the values are provided in both rows.
**The error in the formula builder states that I have columns named the same as one another. This is not true, all columns on this board have unique names
** I know I will need to multiply the value by 100 to get the percentage accurate, I am trying to isolate the problem so I have removed that for now in hopes to simplify finding a solution.
Let me know if I can provide any more info and thank you in advance if you are able to offer any insight!
GCavin
(Gilles Cavin - Reinventing Formulas in monday.com)
2
Hi Kayla,
These 2 columns, what type of columns are they? Formula columns as well? If so, what are your formulas?
GCavin
(Gilles Cavin - Reinventing Formulas in monday.com)
4
I can’t reproduce your issue. I would delete the columns that monday says have duplicate names and recreate them to see if it fixes the issue.
If it doesn’t, you should contact support.
Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*
Hello @kaylak I saw your post and immediately drawn to it because I’ve been on a journey to do something similar. So that I understand what it is you’re trying to accomplish, the calculation that you’re performing states, in laymen’s terms, that if you’re 30 days into a 100 day project, then 30% of the time has been used or has passed? Is that correct? If so, are you performing any additional calculations to understand how much work has been done so that you reconcile the two values? It’s the latter that I am trying to solve and can’t seem to find a way to do so.
Yes that was exactly my goal to compare the percent of time into a project and percent of work completed on a project.
This is for a high level project tracker and most of our projects are broken into 5 stages. So this high level project tracker has 5 status columns to represent the different stages of each project which are marked as done as the project progresses.
We then use the “Progress Tracking” column type to assign weighted values to each stage and see their progress together. For example stage 1 may only represent 10% of the work while phase 2 represents 30% of the work so if both phase 1 and phase 2 are marked done it will show 40% completed.
If you have not used the project tracking column before here is an article from Monday that goes into more detail about the column and how to set it up. https://support.monday.com/hc/en-us/articles/360001150365-The-Progress-Tracking-Column
Here is a screenshot that shows a basic example of what this looks like.
I put the percent of time into the project and progress tracking columns right next to each other so you can quickly compare the two. Using the second line, you will notice that you only 34% of the work has been completed, but 80% of the allocated time has passed, this would raise a red flag and prompt the project lead to look into this and determine if/what actions need to be taken to get this project back on track.
It’s not a perfect solution as it requires these status columns to be updated manually by looking at the detailed project boards and relies on people to check and compare these columns manually, but it has been really helpful for us so far!
Thanks @kaylak for the detailed response. Any chance we could sync up in a Zoom call so that I can see this board/ask more detailed questions? No obligations of course.
Hi @gbick ,
Due to the sensitive nature of content on this board, I won’t be able to join for a zoom call to share my screen. But I am happy to answer questions here or feel free to send me a message here and I can answer questions there as well!
Hi @kaylak, completely understood. How about, in a Zoom call, me bringing up our instance of Monday and you can then walk me through you how you’ve implemented this solution?
