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
2
3
4
5
6
7
8
9
10
11
12
13

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Column1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Designation[/TD]
[TD="align: center"]Infection control[/TD]
[TD="align: center"]Fire Safety and Awareness[/TD]
[TD="align: center"]Health and Safety[/TD]
[TD="align: center"]Leaver[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]05/03/2018[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]leaver[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]05/02/2013[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]28/08/2017[/TD]
[TD="align: center"]28/08/2017[/TD]
[TD="align: center"]28/08/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]01/08/2013[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]26/09/2017[/TD]
[TD="align: center"]26/09/2017[/TD]
[TD="align: center"]26/09/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]26/09/2014[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]13/11/2017[/TD]
[TD="align: center"]13/11/2017[/TD]
[TD="align: center"]13/11/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]19/04/2017[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]24/05/2017[/TD]
[TD="align: center"]01/05/2017[/TD]
[TD="align: center"]24/05/2017[/TD]
[TD="align: center"]leaver[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]15/09/2017[/TD]
[TD="align: center"]Admin[/TD]
[TD="align: center"]23/01/2018[/TD]
[TD="align: center"]25/01/2018[/TD]
[TD="align: center"]23/01/2018[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]13/09/2017[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]14/09/2017[/TD]
[TD="align: center"]14/09/2017[/TD]
[TD="align: center"]14/09/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]22/10/2014[/TD]
[TD="align: center"]OT[/TD]
[TD="align: center"]14/09/2017[/TD]
[TD="align: center"]08/09/2017[/TD]
[TD="align: center"]08/09/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]17/11/2014[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]23/08/2017[/TD]
[TD="align: center"]23/08/2017[/TD]
[TD="align: center"]24/08/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]07/08/2017[/TD]
[TD="align: center"]MANAGER[/TD]
[TD="align: center"]05/09/2017[/TD]
[TD="align: center"]05/09/2017[/TD]
[TD="align: center"]05/09/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]04/12/2017[/TD]
[TD="align: center"]D.MANAGER[/TD]
[TD="align: center"]05/12/2017[/TD]
[TD="align: center"]05/12/2017[/TD]
[TD="align: center"]05/12/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]04/11/2015[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]11/09/2017[/TD]
[TD="align: center"]21/09/2017[/TD]
[TD="align: center"]21/09/2017[/TD]
[TD="align: center"]leaver[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]08/04/2016[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

This is what I am using currently:

	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]40[/TD]
[TD="align: center"]Week Ending[/TD]
[TD="align: center"]Infection control[/TD]
[TD="align: center"]Fire[/TD]
[TD="align: center"]Safeguarding Adults[/TD]

[TD="align: center"]41[/TD]
[TD="align: center"]15-Jul-18[/TD]
[TD="align: center"]93%[/TD]
[TD="align: center"]93%[/TD]
[TD="align: center"]93%[/TD]

[TD="align: center"]42[/TD]
[TD="align: center"]29-Jul-18[/TD]
[TD="align: center"]93%[/TD]
[TD="align: center"]93%[/TD]
[TD="align: center"]93%[/TD]

</tbody>

Stat. & Mand. Training​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D41[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Infection control],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E41[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Fire Safety and Awareness],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F41[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Health and Safety],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C42[/TH]
[TD="align: left"]=C41+14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D42[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Infection control],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E42[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Fire Safety and Awareness],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F42[/TH]
[TD="align: left"]=COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Health and Safety],">="&[@[Week Ending]]-365,Tier1[Leaver],"")/COUNTIFS(Tier1[Start Date],"<="&[@[Week Ending]],Tier1[Leaver],"")

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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 ?