Calculate Weekly Percentage

[janarthenan]

Hi Peter

Hopefully I am not being a pain and was wondering if you are able to help me with something else please.

I have created a training matrix and I need to calculate some percentages on a weekly basis.
The problem I am facing is that - last week my percentage was lets say 15, but when i enter a new date and get 20% this week, this also changes it for the last week.

Is there at all a way for the percentage to ignore values that are of a future date?

	A	B	C	D	E	F	G
1	Column1	Start Date	Designation	Infection control	Fire Safety and Awareness	Health and Safety	Leaver
2	1	05/03/2018	HSCW				leaver
3	2	05/02/2013	SN	28/08/2017	28/08/2017	28/08/2017
4	3	01/08/2013	HSCW	26/09/2017	26/09/2017	26/09/2017
5	4	26/09/2014	SN	13/11/2017	13/11/2017	13/11/2017
6	5	19/04/2017	HSCW	24/05/2017	01/05/2017	24/05/2017	leaver
7	6	15/09/2017	Admin	23/01/2018	25/01/2018	23/01/2018
8	7	13/09/2017	HSCW	14/09/2017	14/09/2017	14/09/2017
9	8	22/10/2014	OT	14/09/2017	08/09/2017	08/09/2017
10	9	17/11/2014	HSCW	23/08/2017	23/08/2017	24/08/2017
11	10	07/08/2017	MANAGER	05/09/2017	05/09/2017	05/09/2017
12	11	04/12/2017	D.MANAGER	05/12/2017	05/12/2017	05/12/2017
13	12	04/11/2015	HSCW	11/09/2017	21/09/2017	21/09/2017	leaver
14	13	08/04/2016	HSCW

<tbody>
</tbody>

This is what I am using currently:

	C	D	E	F
40	Week Ending	Infection control	Fire	Safeguarding Adults
41	15-Jul-18	93%	93%	93%
42	29-Jul-18	93%	93%	93%

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Stat. & Mand. Training​

Worksheet Formulas Cell Formula D41 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Infection control],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") E41 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Fire Safety and Awareness],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") F41 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Health and Safety],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") C42 =C41+14 D42 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Infection control],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") E42 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Fire Safety and Awareness],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") F42 =COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Health and Safety],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

But the problem with this is If someone completes a training on the 30th of July, this changes the percentage for Week Ending 15th and the 29th of July.

So my objective is: If a staff has completed a training and he is NOT a leaver, and his start date is BEFORE the week ending date and the date of his training is LESS than a year (we do our training again every year) and if a new training date is entered this should not affect the previous week.

Thank You!
J

 

[Peter_SSs]

Split from https://www.mrexcel.com/forum/excel-questions/1021331-countif-only-one-per-row.html

As this is a different question, I have split it off to its own thread. You wouldn't have many potential helpers looking at a thread that already had 20+ posts in it.

 

[janarthenan]

Thanks ?