Help with milestone tracking formula
@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!
Help Article Resources
Check out the Formula Handbook template!