Avg of CumNo of days taken with no of projects - Reducing the no of days hold

[SRINI_V]

I want to calculate average Days taken for Projects (All Projects), But We have minus the Days the Project was on hold due to waiting for Customer Feedback. This data has to be plotted as month on month and if it is on hole at end of the month it shall not be counted for that month and when it is reassigned the days to be added.

[v-xinruzhu-msft]

Hi @SRINI_V 

I have some doubts ,can you explain it?

1.Which picture is the original data, "Result I am getting“  or "No of commulative Days"?

2.When the project is closed, the month is not calculated This month is empty When the project is opened again, is it the effect you want to achieve, if so, you need to provide some relevant example data for when these two projects start and when they end, or do you just want to calculate the relevant average, based on the picture you give, my understanding is that you want to calculate the average.

Based on the expected result you have offered.

You can create a new calculated column.

Average = SWITCH(TRUE(),[Project 1]=BLANK()&&[Project 2]=BLANK(),0,[Project 1]<>BLANK()&&[Project 2]<>BLANK(),DIVIDE([Project 1]+[Project 2],2),OR([Project 1]=BLANK(),[Project 2]=BLANK()),DIVIDE([Project 1]+[Project 2],1))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SRINI_V]

i need to calculate average no of days,  of all projects, but each an every project opened at different dates and closed at different dates, and few projects would have been reopened /reactivated and closed several times,  We need to calculate average open /Active days of all projects,   

[SRINI_V]

Thank you for your reply , here i have given project 1 and project 2 as a sample data, But we have "N" no of projects.  Can you help me for the formula in this case