% Completion Formula based on drop down status

MJ_1 · 03/21/23

Hi there,

Help needed!

I need to report a % completion rate based on the status of a milestones column where a number of actions/milestones need to be completed in order for the formula column to indicate % status. Is there any formula that I could use to read the status drop downs, and auto fill a % completion rate in the completion rate column based on how many of the 5 mandatory milestones the rep has completed and selected?

For example, here is the Engagement Model Milestones column, which has 6 milestones (values) which need to be selected and updated by the rep as they are completed. I would like a column on the same spreadsheet to indicate the completion % based on the rep having completed 1-5 of those milestones. For example, as 1-5 are mandatory to receive the 100% completion %, each of these could 1-5 milestones could have a % value of 20%, so if the rep has only chosen 1 & 2 in the drop down, then they are at 40% completion on the % completion column. If they have only done 1, 2 & 3 they would be at 60% completion, and if they have done 1-5 they are at 100% completion. If they did all 6 they would remain on 100% completion as #6 is optional and not required for all customers and is therefore not included in the KPI for the formula calculating the auto % of completion. Any help would be much appreciated, if this is at all possible!

Thanks!

:)

Genevieve P. · 04/23/24

Hi @MJ_1

Thank you for your detail in this request! We can break apart your statement into each of the IF statements required:

if the Status is Not Required it is N/A
if the Status is Issue Not Delivered it is N/D
IF the Engagement Model = High, then Engagement Model Milestones 1-5 are required for 100% OKR (20% each milestone 1-5)
IF the Engagement Model = Medium, then only Engagement Model Milestones 1, 2, 3 & 7 are required to achieve 100% (25% for each milestone).
IF the Engagement Model = Low, then only Engagement Model Milestones 1 & 2 are requied to achieve 100% (50% for each milestone)

^ This is the paragraph you wrote above but a bullet point each time you said IF. That's exactly how you would build an IF statement in Smartsheet.

I'll break out each of those same bullet points into their formula version below. Then we just add them together:

=IF([STATUS FOR ONBOARDING]@row = "NOT REQUIRED", "N/A",

IF([STATUS FOR ONBOARDING]@row = "ISSUE - NOT DELIVERED", "N/D",

IF([ENGAGEMENT MODEL]@row = "HIGH", (IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "1. POSE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "2. POCI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "3. MACI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "4. RRE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "5. NPS"), 0.2, 0)),

IF([ENGAGEMENT MODEL]@row = "MEDIUM", (IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "1. POSE"), 0.25, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "2. POCI"), 0.25, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "3. MACI"), 0.25, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "7. EOS"), 0.25, 0)),

IF([ENGAGEMENT MODEL]@row = "LOW", (IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "1. POSE"), 0.5, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "2. POCI"), 0.5, 0))

Add that in and let me know if it works for you!

Cheers,

Genevieve

MJ_1 · 05/14/24

@Genevieve P.

Thank you so much! I have just got it working. I needed to copy each of the statements individually into the cell formula and now it is working. Greatly appreciate the support and advice! :)

Jeff Reisman · 03/22/23

@MJ_1

Try this one. Each IF will look for one of the 5 values you care about in the multi-select column, and if it finds it, it returns a value of .2, otherwise it returns 0. If you have only one of them, the cell becomes .2 + 0 + 0 + 0 + 0 which equals .2, and if the column is formatted for %, will display as 20%. If you have two of them, .2 + .2 = .4, or 40%, etc.

=IF(HAS([Engagement Model Milestones]@row, "1. POSE"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "2. POCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "3. MACI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "4. RRCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "5. NPS"), .2, 0)

MJ_1 · 03/22/23

@Jeff Reisman Thank you so much. I was experimenting with IF statements but couldn't work out how it could calculate the % based on some of them being completed, but not all. This is working! Much appreciated!

MJ_1 · 03/23/23

@Jeff Reisman

I have a follow up question which would help with the data integrity. I have another column on the Smartsheet 'Status for Onboarding' below which has 2 drop down options which would mean the above formula for % is not applicable, as we have not delivered due to an issue "ISSUE - NOT DELIVERED" , or we are no longer required to do the delivery "NOT REQUIRED", so for both of those scenarios the Engagement Model Milestones tasks that determine the Engagement Model Milestones OKR % are not needed for the customer and so a 0% being recorded would not be a true representation of the rep's completion %.

Would it be possible to add to your formula the ability to read the above 'Status for Onboarding'' column, and for those rows which have the Status "Not Required" to show N/A in the Engagement Model Milestones OKR column, and for the rows that have "ISSUE - Not Delivered" to show N/D and a red cell colour (red cell nice to have not essential) in the Engagement Model Milestones OKR column. I am not sure how this would work as I have made the Engagement Model Milestones OKR a formula and % column now, as you can see below. I need to somehow distinguish a 0% from the rows that should have a % and milestones completed, from those that should not as we did not deliver them for 2 reasons as outlined above.

Or is it possible/better to add an IF statement to a drop down selection Engagement Model Milestones column, so that this column below, reads the Status for Onboarding column first, and instead auto-populates an N/A for Not Required or N/D for Not Delivered depending on the Status ? We could then use those and pick them up for the OKR formula. I am not sure if an IF statement can also be applied to a multi-select column... Again though I am not sure how I would use the % to distinguish them..

Many thanks for advice!

Jeff Reisman · 03/23/23

@MJ_1

Let's start with eliminating the % the status is not required or not delivered. We can do this by wrapping the whole series of IFs that you already have inside another IF with OR!

=IF(OR([Status for Onboarding]@row = "NOT REQUIRED", [Status for Onboarding]@row = "ISSUE - NOT DELIVERED"), "", (IF(HAS([Engagement Model Milestones]@row, "1. POSE"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "2. POCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "3. MACI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "4. RRCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "5. NPS"), .2, 0)))

(Check the color-coding on the parentheses once you put this in your sheet, and make sure I've got the correct number at the end! The BOLD part becomes the "value if false" in the IF for Status for Onboarding.) This formula would leave the % column blank, which would not register as 0%.

If you'd rather populate N/A or N/D for those exception rows:

=IF([Status for Onboarding]@row = "NOT REQUIRED", "N/A", IF([Status for Onboarding]@row = "ISSUE - NOT DELIVERED", "N/D", (IF(HAS([Engagement Model Milestones]@row, "1. POSE"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "2. POCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "3. MACI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "4. RRCI"), .2, 0) + IF(HAS([Engagement Model Milestones]@row, "5. NPS"), .2, 0))))

(Again - check the parentheses!)

MJ_1 · 03/26/23

@Jeff Reisman thanks very much. I have decided to go with the 2nd option for status N/A and N/D being shown. Many thanks for this! I am very new to formulas, in Smartsheet and Excel so this is extremely helpful and appreciated!

MJ_1 · 04/10/24

Hi Smartsheet Community! Be very grateful for help on a further evolution of my Smartsheet in terms of formulas.

Currently, I have the following 3 columns, where Jeff (community) above helped me with a formula to ensure that when a Status of Onboarding is selected, then depending on the drop downs selected in the Engagement Model Milestones Column (Column 2), the Engagement Model OKR Column % (Column 3) will be auto-populated. Engagement Model Milestones 1-5 of the 6 in the drop down list are currently required for the customer success rep to receive 100% in the Engagement Model OKR % (Column 3), Engagement Model Milestones (Column 2) 1-5 are all worth 20% each as Engagement #6 is optional. This is the formula currently being used to calculate the Engagement Model OKR (Auto) Column based on the Status of Onboarding (Column 1), and selected drop downin Engagement Model Milestones (Column 2):

Current Formula in Column 3:

=IF([STATUS FOR ONBOARDING]@row = "NOT REQUIRED", "N/A", IF([STATUS FOR ONBOARDING]@row = "ISSUE - NOT DELIVERED", "N/D", (IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "1. POSE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "2. POCI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "3. MACI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "4. RRE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "5. NPS"), 0.2, 0))))

As a next evolution of this Smartsheet, I would like to have a new column, Engagement Model (NEW Column) to be checked after the Status for Onboarding column, for this new formula. This column would have 3 Service levels, High, Medium and Low (H/M/L) drop-downs, and depending on which one of those is selected in this new Column, then only certain Engagment Model Milestones (Column 2) are required to achieve 100% in the Engagement Model OKR (Auto) (Column 3).

For example, I would like the new formula to enable the Engagement Model drop down to determine the # of Engagement Model Milestones required in order to achieve the 100% OKR. A formula in the OKR column that somhow captures:

The current IF statement related to the Status for Onboarding, where if the Status is Not Required it is N/A in the OKR column and if the Status is Issue Not Delivered it is N/D in the OKR column. Then I would like it to check IF the Engagement Model = High, then Engagment Model Milestones 1-5 are required for 100% OKR (20% each milestone 1-5 and 6 is optional so does not have a % attributed), then IF the Engagement Model = Medium, then only Engagement Model Milestones 1, 2, 3 & 7 are required to achieve 100% (25% for each milestone). Then IF the Engagement Model = Low, then only Engagement Model Milestones 1 & 2 are requied to achieve 100% (50% for each milestone)

Example of the new column below, but notice that the % is not correct as it is using this standard formula in Engagement Model OKR (Auto) which does not currently factor in the NEW Engagement Model service levels after the Status for Onboarding IF statement.

=IF([STATUS FOR ONBOARDING]@row = "NOT REQUIRED", "N/A", IF([STATUS FOR ONBOARDING]@row = "ISSUE - NOT DELIVERED", "N/D", (IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "1. POSE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "2. POCI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "3. MACI"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "4. RRE"), 0.2, 0) + IF(HAS([ENGAGEMENT MODEL MILESTONES]@row, "5. NPS"), 0.2, 0))))without reflecting on the new Engagement Model column for a check first.

Any help would be very much appreciated! :)

MJ_1 · 04/11/24

@Jeff Reisman you were immensely helpful when I needed a formula for a drop-down then % on this original thread. I have added a new email to the bottom of this thread but did not tag you above. I think as this is a new question on the bottom it is not visible to others easily. If you do have any time to view the new questions based on the evolution of my Smartsheet I would be most grateful!

Genevieve P. · 04/23/24