Percentages of data by month

[Ryan1996]

Hi,

I have a table like the below and want to be able to see what percentage of each month had been closed within the allowed time. For example row two has an end date of the 26/08/2020 and the issue was closed on the 21/07/2020 so therefore this was completed in date and would be 100% however i need to work out the percentage of all from may/june/july, etc. So i would need to know that for example May was maybe 80% completed on time, then June was 96% or something along those lines.

I'll put the % into a separate table on the same sheet. Column M.

	A	B	C	D	E
1	Date Raised	Target Date	Days to Go	Date Closed	Status
2	28/05/2020	26/08/2020	21		Open - In Target
3	28/05/2020	26/08/2020	Closed	21/07/2020	Closed
4	28/05/2020	26/08/2020	21		Open - In Target
5	24/06/2020	24/07/2020	Closed	24/07/2020	Closed
6	24/06/2020	24/07/2020	Closed	24/07/2020	Closed
7	24/06/2020	24/07/2020	Closed	24/07/2020	Closed
8	24/06/2020	24/07/2020	Closed	24/07/2020	Closed
9	24/06/2020	23/08/2020	Closed	24/07/2020	Closed
10	24/06/2020	22/09/2020	Closed	24/07/2020	Closed
11	24/06/2020	22/09/2020	Closed	24/07/2020	Closed
12	24/06/2020	22/09/2020	Closed	24/07/2020	Closed

 

[mamady]

Hi Dear,

Put the below formula in cell M2 and drag down
=IF(ISNUMBER(D2),(D2-A2)/(B2-A2),"")

For the second part, i.e. to see the performance of a whole month, you can list the months separately in column N and fill in the below formula (in cell O1 for example)
=SUM(IF(MONTH($A$2:$A$12)=MONTH(N2),$M$2:$M$12,""))/COUNT((IF(MONTH($A$2:$A$12)=MONTH(N2),$M$2:$M$12,"")))

N	O
May-20​	60%​
Jun-20​	69%​

Regards

 

[Ryan1996]

Hi Mamady,

Thanks for your help. Could you explain the first formula a little more. If the closing date was before the required end date the percentage may turn out like 33%, how can i make it that if the 'Date closed' is before the 'Target date' this still shows 100%. If the 'Date closed' is after the 'target date' the response can be 0%. Is this possible?

again, appreciate your help.

Ryan

 

[mamady]

Hi Mamady,

Thanks for your help. Could you explain the first formula a little more. If the closing date was before the required end date the percentage may turn out like 33%, how can i make it that if the 'Date closed' is before the 'Target date' this still shows 100%. If the 'Date closed' is after the 'target date' the response can be 0%. Is this possible?

again, appreciate your help.

Ryan

Hi Rayan,

my formula was to calculate how much time it too to Close out of the 90 days. I.e. 80% means it took 72 days from the Date raised date.

however, if you want to calculate that any close date on or before the target date should be 100%, and any after is 0%, you can use the below formula
=IF(AND(ISNUMBER(D2),D2<=B2),100%,IF(AND(ISNUMBER(D2),D2>B2),0%,""))

Regards

 

[Ryan1996]

i ended up using this - =IF(D2>B2,"0%", "100%") for each of the rows but now i need the bit like you did before based on the months.

So i changed a few bits and so here's the info. The above formula is in column M. and have months listed in column N. So i now need in column O a percentage of how many from column M meet 100% for each month. So as an example lets say there are 10 records (rows) for May and 8 of them are 100% and 2 are 0% in column M it should show 80% in column O.

Apologies for the confusion. I really do appreciate the help. Just can't get the formula right for the above.

 

[mamady]

Hi Dear,

First of all, please adjust your formula to remove the double quotation .. as such will render the 0% and the 100% as text. It should be
=IF(D2>B2,0%, 100%)

about the logic, please note that your formula will still return 100% for the open records (has no close date) .. for you to consider if you want to change this

As to the last part, please try the following formula
=COUNT(IF(MONTH($A$2:$A$12)=MONTH(N2)*($M$2:$M$12=100%),$M$2:$M$12))/COUNT(IF(MONTH($A$2:$A$12)=MONTH(N2),$M$2:$M$12))

 

[Ryan1996]

You're a star, this seems to be working!

One last thing if you don't mind. I tried adding an else to the end of the above but it screwed it up. Is it possible to add a replacement for those without data. Currently if there isn't any data to show the box shows "#DIV/0!" Is it possible to make it say "No Data" or simply "100%"

 

[mamady]

Hi Dear,

Try
=IFERROR(COUNT(IF(MONTH($A$2:$A$12)=MONTH(N2)*($M$2:$M$12=100%),$M$2:$M$12))/COUNT(IF(MONTH($A$2:$A$12)=MONTH(N2),$M$2:$M$12)),"No Data")

 

[Ryan1996]

All sorted, thanks!

 

[mamady]

I'm glad