Avg duration in months using dates from different Gates in same Program

[mrainford]

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.