Help with milestone tracking formula

stacygo6

@Paul Newcome Hi Paul, I've sourced many of your formulas in the past, but haven't found one that can help me with the following.

I need help with two formulas please. We have a sheet that needs to track when Members hit milestones for participants. For every Reporting Period each Member submits their Cumulative Participant Actual numbers via a form. Each Member has a different Total Participant Goal. The four Milestone Goals are: Milestone 1 = 25% of Total Annual Participant Goal, Milestone 2 = 50%, Milestone 3 = 75%, Milestone 4 = 100%. We pay members a set fee by member when they reach each milestone. We need to ensure that we do not pay a single milestone multiple times.

We need a formula in the Milestone Reached Column that tells us:

• A new milestone has been reached by the member and which new milestone was reached during that Reporting Period

It is feasible that multiple milestones are reached during a single Reporting Period, so the formula needs to account for this.

We need a formula in the Milestone Reached Date that tells us:

• The Reporting Period date the milestone was reached

I've attached a download of the sheet and have provided a screenshot. Thank you so much in advance for any help you can provide!

OA Example for Formula SOS.xlsx

Paul Newcome

Is there a facepalm emoji? Ugh. I didn't include anything about the Member column in my formula.

=IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.25, Member:Member, @cell = Member@row) = 1, " M1 ")

You can replicate the above for each milestone and then "add" them together to get it to show multiple milestones.

=IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.25, Member:Member, @cell = Member@row) = 1, " M1 ") + IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.50, Member:Member, @cell = Member@row) = 1, " M2 ")

stacygo6

@Paul Newcome We have a winner! So awesome! Thank you so very much Paul.

Paul Newcome

Let me make sure I understand...

In the bright yellow column, you want a formula that outputs 1, 2, 3, or 4 based on the percentage of cumulative vs goal (or cumulative vs the goal columns).

Then in the more peach/orange colored columns you want the date that each milestone was reached.

If the above is correct, then you can use something like the below.

Milestone Reached:

=IF([Cumulative Participants Actual]@row / [Total Annual Participant Goal]@row>= 1, "Milestone 4", IF([Cumulative Participants Actual]@row / [Total Annual Participant Goal]@row>= 0.75, "Milestone 3", IF([Cumulative Participants Actual]@row / [Total Annual Participant Goal]@row>= 0.50, "Milestone 2", IF([Cumulative Participants Actual]@row / [Total Annual Participant Goal]@row>= 0.25, "Milestone 1"))))

Then you would use 4 Record A Date automations set up to be triggered when the Milestone Reached column changes to (value). The first one would be set to record the date in the first date column when the Milestone Reached column changes to "Milestone 1". Then repeat this logic for the remaining three updating the trigger and column as needed.

stacygo6

@Paul Newcome Thank you, but we're not quite there yet. You're correct that we want the bright yellow column formula to output 1, 2, 3, or 4 based on the percentage of cumulative vs goal (or cumulative vs the goal columns). But we have two issues:

1.) We only want the output of 1, 2, 3, or 4 on the row of the first time the milestone was met - See screenshot using Atlanta, GA as an example

2.) A member can reach two milestones in a single Reporting period, so we need the formula to be able to output 2, 3 when this happens - See screenshot using Greenville, SC as an example

Thanks again for your help Paul!

Paul Newcome

Lets try this just for Milestone 1 to make sure we can first limit it to only the row where it happened. Once we get that part down, we can start working on the logic for getting multiple milestones output.

=IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell<= Reporting Period]@row, [Cumulative Participants Actual]:[Cumulative Participants Actual], @cell / [Cumulative Participants Actual]@row>= 0.25) = 1, "M1")

stacygo6

@Paul Newcome That formula isn't returning accurate results - either returns M1 in a reporting period where they didn't reach the milestone, or doesn't return M1 for when they have met the milestone.

I have created a mock-up of this in Smartsheet. Is there a way I can share that sheet with you? May be easier. Thanks again for your help.

Paul Newcome

If you can, try making a copy and leaving in only the data that is needed (sensitive data that is needed can be replaced with sample data), publish it as editable by anyone, and then provide the link here please.

stacygo6

@Paul Newcome Here is a sheet with fake data. The yellow highlighted column is the formula I came up with. It correctly identifies when the milestone is reached BUT I haven't been able to figure out how to only have it return the result for only the first reporting period the milestone is met, or for when multiple milestones are met in the same reporting period - for example Greenville where they met milestones 2&3 in the reporting period of 08/31/22. The blue highlighted column is your first stab at the formula which as explained previously returns incorrect results - except for Atlanta. Thanks once again for your help.

https://app.smartsheet.com/b/publish?EQBCT=4cfb343d25bb4d008d0ef7bf1f7f1e86

Paul Newcome

Is there a facepalm emoji? Ugh. I didn't include anything about the Member column in my formula.

=IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.25, Member:Member, @cell = Member@row) = 1, " M1 ")

You can replicate the above for each milestone and then "add" them together to get it to show multiple milestones.

=IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.25, Member:Member, @cell = Member@row) = 1, " M1 ") + IF(COUNTIFS([Reporting Period]:[Reporting Period], @cell <= [Reporting Period]@row, [Participants % of Goal Reached]:[Participants % of Goal Reached], @cell >= 0.50, Member:Member, @cell = Member@row) = 1, " M2 ")

stacygo6

@Paul Newcome We have a winner! So awesome! Thank you so very much Paul.

Paul Newcome

Happy to help. 👍️