Help with milestone tracking formula

Options

@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!


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/28/23 Answer ✓
    Options

    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
    stacygo6 ✭✭
    Answer ✓
    Options

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    edited 07/28/23 Answer ✓
    Options

    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
    stacygo6 ✭✭
    Answer ✓
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!