Tracking Project Status Success Rate

Ric

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

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

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

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.