Having bit of a challenge with the following requirement.
Duration of a stage for a program is equal to Current gate - actual date minus Prior gate actual date.
Example
Gate 1- Concept: N/A or ~ ( there is no prior gate so there can not be any duration for it)
Gate 2 – Program approved:
1.Calculate duration for one program: Gate 2 actual date minus Gate 1 actual date.
2.Calculate duration for the next program: Gate 2 actual date minus Gate 1 actual date
3.etc
4.Then add up all durations and divide by number of programs, and then divide by 30 to get months.
5.If Gate 1 or Gate 2 is Pre NPL gate , do not include it in calculation for Gate 2.
Same as above for the rest of the gates, just need to change Gate numbers.
See data attached - your assistance is greatly appreciated.