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

Find more posts tagged with

Accepted answers

Genevieve P.

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

All comments

Genevieve P.

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]#

Cheers!

Genevieve

Ric

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

Genevieve P.

Hi Ric,

I'm glad to hear it! 🙂 Thanks for following up.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.