VLOOKUP a Condition, Yield an average CYCLE TIME of the population within a date range

[WADECIMA]

Can't seem to get the right formula for Non-Conformance Closure Cycle time to measure how well one aspect of our NCR process is working.
NOTE: All of the information I am working with is DATA-LINKED from another system that can't be modified easily due to the front end design of the input template.


PROBLEM: Need to identify how many NCR's were closed within a user defined DATE range (i.e: 1/1/2018 - 1/31/2018)

We have a formula to count how many NCR's were closed within the user defined date range.

STEP 1 [=COUNTIFS('NCR DATA'!X:X,">="&C1,'NCR DATA'!X:X,"<="&C2+1)]

The 3 main data columns are as follows:

Column A​	Column T​	Column X​
DATE OF CREATION	NCR STATUS	VERIFIED ON
4/25/2018	OPEN
4/12/2018	CLOSED	4/28/2018
3/30/2018	PENDING	4/18/2018
3/8/2018	CLOSED	3/15/2018
3/1/2018	CLOSED	3/10/2018
1/18/2018	CLOSED	1/20/2018
1/18/2018	CLOSED	1/25/2018
1/15/2018	CLOSED	1/29/2018

<tbody>
</tbody>

Step 1: Identify the population of all CLOSED NCR's that fall within the user define DATE range. (GOT THAT PART)
Step 2: Calculate the closure cycle time in workdays for each CLOSEDNCR (Column X - Column A).
Step 3: Calculate the AVERAGE days based on the number of NCR's closed. =(SUM of all WORKDAYS TO CLOSE NCRS / Number of NCR's closed).

I would appreciate any ideas on how to accomplish this.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Why do you have C2+1 in your step 1 formula? To me, that would include any NCRs closed on 1 February if your date range ended on 31 January (C2). Until I hear otherwise, I will assume that '+1' is a mistake. And doesn't the countifs need to check for 'Closed' in column T to avoid the possibility of counting a 'Pending'?

2. Note that my dates are in d/m/y format.

3. I would recommend not using whole column references. Some functions can work out to only use the 'used rows' but others will calculate all 1,000,000+ rows! I have used to row 100 in my examples below but if this is what you want, adjust for whatever your data size is.

4. I have assumed workdays means Monday - Friday.

My 'NCR DATA' sheet is what you have shown above, using rows 1:9 in columns A, T and X.

Here is my results sheet. Does it do what you want?

Excel Workbook
	B	C
1	Start	1/01/2018
2	End	31/01/2018
3
4	Count	3
5	WorkDays	19
6	Average	6.333333333
Results

 

[WADECIMA]

Peter:

You were right to question the C2+1....I was trying to correct for a TIME-STAMP issue which turned out to be my error in selecting a default date format with '*d/mm/yyyy' instead of 'd/mm/yyyy'. I forgot the asterix(*) makes the date time sensitive which forced the COUNTIFS formula on my dashboard to ignore any data that was entered after 0:00:00.

I have since fixed the date format. And thanks for the formula, will let you know the outcome shortly.

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[WADECIMA]

The COUNT formula you provided in C4 works and provided an extra layer of validation (thanks).

The formula in C5 provides a '#VALUE!' result. I have checked the column/row references and they are in order. I also tried ARRAY bracket but that made no difference.

What am I missing?

 

[WADECIMA]

Had a realization the formula in C5 assumes the NCR initiation date is ">=" the start date for the data analysis from cell C1. Fact is some NCR could have started before the date entered in cell C1.

SUMPRODUCT(--('NCR DATA'!A2:A100>=C1),--('NCR DATA'!X2:X100<=C2),--('NCR DATA'!X2:X100<>""),NETWORKDAYS('NCR DATA'!A2:A100+0,'NCR DATA'!X2:X100+0)).

	B	C
1	Start	1/01/2018
2	End	31/01/2018
3
4	Count	3
5	WorkDays	19
6	Average	6.333333333

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 86px;"><col style="width: 85px;"></colgroup><tbody>
</tbody>

I made some changes to the Date of Creation for 2 of the sample data points.

(A) DATE OF CREATION	(T) NCR STATUS	(X) VERIFIED ON
4/25/18	OPEN
4/12/18	CLOSED	4/28/18
3/30/18	PENDING	4/25/18
3/8/18	CLOSED	3/15/18
12/1/17	CLOSED	1/31/18
11/15/17	CLOSED	1/20/18
1/18/18	CLOSED	1/25/18
1/15/18	CLOSED	1/29/18

<tbody>
</tbody><colgroup><col span="3"></colgroup>

 

[Peter_SSs]

So, what are the expected results, calculated manually, for that latest sample data?
Any detail of how you made that (manual) calculation would also be beneficial.

 

[WADECIMA]

After looking at this with a student in our office, who is quite adept at Excel, he came up with this ARRAY formula which delivered the needed results. We did look at your formula which triggered an idea on is part. This is just test data to prove the formula works and I will apply your tip to limit the ROWS in the formulas.


EMBEDDED IN CELL E6 {=SUM(IF(B:B="CLOSED",IF(C:C<=B3,IF(C:C>=B2,NETWORKDAYS(A:A+0,C:C+0),0),0),0))/COUNTIF(B:B,"CLOSED")}

	A	B	C	D	E
2	START DATE	1/1/2018
3	END DATE	2/28/2018
4
5	NCR CREATION	NCR STATUS	NCR CLOSURE DATE		AVG CLOSURE NETWORKDAYS
6	3/1/2018	CLOSED	4/28/2018		13.4
7	2/20/2018	CLOSED	2/21/2018
8	2/15/2018	CLOSED	2/22/2018
9	2/9/2018	CLOSED	2/20/2018
10	2/9/2018	CLOSED	2/23/2018
11	1/16/2018	CLOSED	2/14/2018
12	11/20/2017	CLOSED	1/20/2018

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[WADECIMA]

Made a few small changes.

=SUM(IF(C6:C12="CLOSED",IF(D6:D12<=C3,IF(D6:D12>=C2,NETWORKDAYS(B6:B12+0,D6:D12+0),0),0),0))/COUNTIFS(C6:C12,"CLOSED",D6:D12,"<="&C3,D6:D12,">="&C2)

 

[Peter_SSs]

Glad that you got something that you are happy with. Thanks for letting us know.