Tracking Project Status Success Rate
Hi Smartsheet Comm,
I have a column with a drop down list of 5 choices of project status (Under Development, Proposal, Rejected, Withdrawn, Contract Won). I've used an automation to copy the row to a new sheet whenever the project status changes, but I'm having difficulty in taking this to the next level and measuring the percentage of projects that transitions into the next stage, for example "Under Development" to "Proposal", or from "Under Development" to "Withdrawn".
Is there a way I can capture this sort of information?
Warm regards,
Ric
Best Answer
-
Hi Ric,
Since you have a secondary sheet that records every change, you could use COUNTIF formulas looking at that second sheet to find out how many changes have occurred.
I would personally put these calculations in a Sheet Summary field.
For example, you could count how many rows had changed to "Under Development":
=COUNTIF([Project Status]:[Project Status], "Under Development")
Then you can count how many rows have "Proposal":
=COUNTIF([Project Status]:[Project Status], "Proposal")
To compare these two numbers, you can either put the two formulas together or reference the cells with the formula:
=[Proposal Formula]# / [Under Development Formula]#
(See: Create a Cell or Column Reference in a Formula) That will show you how many rows have "Proposal" compared to how many had come in as "Under Development".
Then you can do the same with "Withdrawn":
=COUNTIF([Project Status]:[Project Status], "Withdrawn")
=[Withdrawn Formula]# / [Under Development Formula]#
Keep in mind this assumes that you would only change the Status to "Proposal" or "Withdrawn" after the original value was "Under Development". If the row skipped right from blank to "Proposal", you wouldn't have the correct baseline to compare against. Does that make sense?
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
Hi Ric,
Since you have a secondary sheet that records every change, you could use COUNTIF formulas looking at that second sheet to find out how many changes have occurred.
I would personally put these calculations in a Sheet Summary field.
For example, you could count how many rows had changed to "Under Development":
=COUNTIF([Project Status]:[Project Status], "Under Development")
Then you can count how many rows have "Proposal":
=COUNTIF([Project Status]:[Project Status], "Proposal")
To compare these two numbers, you can either put the two formulas together or reference the cells with the formula:
=[Proposal Formula]# / [Under Development Formula]#
(See: Create a Cell or Column Reference in a Formula) That will show you how many rows have "Proposal" compared to how many had come in as "Under Development".
Then you can do the same with "Withdrawn":
=COUNTIF([Project Status]:[Project Status], "Withdrawn")
=[Withdrawn Formula]# / [Under Development Formula]#
Keep in mind this assumes that you would only change the Status to "Proposal" or "Withdrawn" after the original value was "Under Development". If the row skipped right from blank to "Proposal", you wouldn't have the correct baseline to compare against. Does that make sense?
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Hi Genevieve,
You are an absolute champion, thank you for introducing the sheet summary field. It was very easy implementing the values on a dashboard afterwards as well.
Cheers,
Ric
-
Hi Ric,
I'm glad to hear it! 🙂 Thanks for following up.
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!