Taking the difference between dates in a table that can be filtered

[Derick_T]

Hello,
Iam trying to calculate the days between failures in column E. My goal is to beable to take the difference between the respective date in column A and get thedifference between the date previous cell.


Theproblem I am running into is being able to do this considering I want to beable to filter by "Site" (column B). All my failure dates aresequential across multiple sites (column A data). My existing formula workswhen trying to get the difference between days for all sites but not when I filterby sites.




Just an added piece of information in case it helps understand the question alittle better or even some insight into why I am focused on getting thedifference between failures in Column A; I am then turning around and gettingthe column E average (in column F) which would represent the average daysbetween failures for the specific event I am analyzing. Being able to filter bysite (column B) is important so I can compare the different average daysbetween failures across the different sites.

 

[RasGhul]

Hi Derick,

With your site criteria it may be easier to extract records first then calculate;

Book1
	A	B	C	D	E	F	G
1	Date Failure	Site		Start Date	3/01/2017
2	3/01/2017	A		End Date	19/01/2017
3	3/01/2017	A		Count Records	9
4	5/01/2017	C		Site		Average	Avg Excluding Zero
5	5/01/2017	C		C		1.444444444	2.166666667
6	6/01/2017	A
7	6/01/2017	C		Date Failure	Site	Calc days between	Days Between
8	6/01/2017	C		5/01/2017	C	0	1.444444444
9	9/01/2017	A		5/01/2017	C	0	1.444444444
10	9/01/2017	A		6/01/2017	C	1	1.444444444
11	9/01/2017	A		6/01/2017	C	0	1.444444444
12	9/01/2017	A		11/01/2017	C	5	1.444444444
13	9/01/2017	A		13/01/2017	C	2	1.444444444
14	9/01/2017	A		15/01/2017	C	2	1.444444444
15	11/01/2017	C		17/01/2017	C	2	1.444444444
16	13/01/2017	C		18/01/2017	C	1	1.444444444
17	15/01/2017	A
18	15/01/2017	C
19	17/01/2017	C
20	18/01/2017	C
21	19/01/2017	A
22	19/01/2017	A
Sheet1

Cell Formulas
Range	Formula
E3	=COUNTIFS($A$2:$A$22,">="&$E$1,$A$2:$A$22,"<="&$E$2,$B$2:$B$22,$D$5)
E8	{=IF(ROWS($D$8:E8)>$E$3,"",INDEX(B$2:B$22,SMALL(IF($B$2:$B$22=$D$5,ROW($A$2:$A$22)-ROW($A$2)+1),ROWS($D$8:E8))))}
F5	=AVERAGEIFS($F$8:$F$21,$D$8:$D$21,">="&$E$1,$D$8:$D$21,"<="&$E$2)
F9	=IF(D9="","",D9-D8)
G5	=AVERAGEIF($F$8:$F$21,"<>0")
G8	=IF(F8="","",AVERAGE($F$8:$F$21))
D8	{=IF(ROWS($D$8:D8)>$E$3,"",INDEX(A$2:A$22,SMALL(IF($B$2:$B$22=$D$5,ROW($A$2:$A$22)-ROW($A$2)+1),ROWS($D$8:D8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Derick_T]

Thank you RasGhul! You understood the application perfectly and I even like your idea about averaging with and without 0's included.

A follow up question:

My data comes from a rather large dynamic table, Table1, which is on another tab in my workbook. This table constantly has data being added to it.

That said, is there someway I can extract the records directly from Table1, without having to know the finite row # where Table1 ends?

I guess a simpler, perhaps more direct way of asking my question is; how would the array formulas look if I wanted to reference Table1, with the specific column headers, Date Failure and Site just as below.

 

[RasGhul]

Ok same formulas with table referencing, so now the array formulas will calculate each time new data is added to table1.

Note though that the array formula needs to be copied down far enough to return all possible records. eg. if Site A has the most rows with 5000 lines, then I would drag the formula down to say 10000 to cover future data. So it doesn't matter how much data gets added to table1 the array formula area needs to cover total possible rows. But then you can also convert this whole area into eg table2 for the Count records, avg, & avgif calculations.

Book1
	A	B	C	D	E	F	G
1	Date Failure	Site		Start Date	03-01-17
2	03-01-17	A		End Date	19-01-17
3	03-01-17	A		Count Records	12
4	05-01-17	C		Site		Average	Avg Excluding Zero
5	05-01-17	C		A		1.333333333	4
6	06-01-17	A
7	06-01-17	C		Date Failure	Site	Calc days between	Days Between
8	06-01-17	C		03-01-17	A	0	1.333333333
9	09-01-17	A		03-01-17	A	0	1.333333333
10	09-01-17	A		06-01-17	A	3	1.333333333
11	09-01-17	A		09-01-17	A	3	1.333333333
12	09-01-17	A		09-01-17	A	0	1.333333333
13	09-01-17	A		09-01-17	A	0	1.333333333
14	09-01-17	A		09-01-17	A	0	1.333333333
15	11-01-17	C		09-01-17	A	0	1.333333333
16	13-01-17	C		09-01-17	A	0	1.333333333
17	15-01-17	A		15-01-17	A	6	1.333333333
18	15-01-17	C		19-01-17	A	4	1.333333333
19	17-01-17	C		19-01-17	A	0	1.333333333
20	18-01-17	C
21	19-01-17	A
22	19-01-17	A
Sheet1

Cell Formulas
Range	Formula
E3	=COUNTIFS(Table1[Date Failure],">="&$E$1,Table1[Date Failure],"<="&$E$2,Table1[Site],$D$5)
E8	{=IF(ROWS($E$8:E8)>$E$3,"",INDEX(Table1[[Site]:[Site]],SMALL(IF(Table1[[Site]:[Site]]=$D$5,ROW(Table1[[Date Failure]:[Date Failure]])-ROW($A$2)+1),ROWS($D$8:D8))))}
F5	=AVERAGEIFS(Table2[Calc days between],Table2[Date Failure],">="&$E$1,Table2[Date Failure],"<="&$E$2)
F9	=IF(D9="","",D9-D8)
G5	=AVERAGEIF(Table2[Calc days between],"<>0")
G8	=IF(F8="","",AVERAGE([Calc days between]))
D8	{=IF(ROWS($D$8:D8)>$E$3,"",INDEX(Table1[[Date Failure]:[Date Failure]],SMALL(IF(Table1[[Site]:[Site]]=$D$5,ROW(Table1[[Date Failure]:[Date Failure]])-ROW($A$2)+1),ROWS($D$8:D8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Derick_T]

Thanks.

I am not getting the formulas to work for me. I event went to the first example above (not trying to reference a Table on another worksheet) to try something more fundamental. The issues I am having when I apply those formulas are:
1. When I type A in D5, I get C in E8 and vice versa.
2. The first date referenced doesn't quite align to the correct value for either site. Using the exact scenario above I get 06-01-17 as my first event date for Site A (even though it's referencing site in the next column over, but that is also the wrong date for Site C).
3. I am not getting valid data when I copy the D8 and E8 formulas down. I just get an invalid number (#NUM!) reference.

What I am seeing when I apply all the formulas:

Date Failure	Site	Start Date
A	End Date
A	Count Records
C	Site
C	A
A
C	Date Failure	Site	Calc days between	Days Between
C	C
A	#NUM!​
A	#NUM!​
A	#NUM!​
A	#NUM!​
A	#NUM!​
A	#NUM!​
C	#NUM!​
C	#NUM!​
A	#NUM!​
C	#NUM!​
C	#NUM!​
C
A
A

<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]

</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]03-01-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]03-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: right"]19-01-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]03-01-17[/TD]

[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]05-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]Average[/TD]
[TD="align: right"]Avg Excluding Zero[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]05-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]06-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]06-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]06-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]06-01-17[/TD]

[TD="align: right"]#NUM![/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]09-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]11-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]13-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]15-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]15-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]17-01-17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]
[TD="align: right"]

#NUM!​

[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]18-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]19-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]19-01-17[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

I've been over the formulas a couple of times, and I honestly can't wrap my head around the second if statement in the Array Formulas, so it's hard for me to dissect where the issue could possible lie.

Along those same lines, and if I'm able to get the above to work (moving onto the second version where I am trying to reference Table1) what is the purpose of the underlined parts in the array formula below?

Array Formulas[TABLE="width: 100%"]
<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10, align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]D8[/TH]
[TD="align: left"]{=IF(ROWS($D$8:D8)>$E$3,"",INDEX(Table1[[Date Failure]:[Date Failure]],SMALL(IF(Table1[[Site]:[Site]]=$D$5,ROW(Table1[[Date Failure]:[Date Failure]])-ROW($A$2)+1),ROWS($D$8:D8))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]E8[/TH]
[TD="align: left"]{=IF(ROWS($E$8:E8)>$E$3,"",INDEX(Table1[[Site]:[Site]],SMALL(IF(Table1[[Site]:[Site]]=$D$5,ROW(Table1[[Date Failure]:[Date Failure]])-ROW($A$2)+1),ROWS($D$8:D8))))}[/TD]
[/TR]
</tbody>[/TABLE]


I ask because, now that I am going to extract the data from my original table, Table1, and essentially eliminate the need for having the copied that I was having to bring in manually, I'm not exactly sure what $A$2 will be referencing?

Should it perhaps be referencing a cell in Table 1?

 

[RasGhul]

Hi,

Array formula with table referencing is written slightly differently to normal ranges and the row(a2)+1 was skewing the search area;

Book1
	A	B	C	D	E	F
1	Start Date	3/01/2017				Site List
2	End Date	19/01/2017				A
3	Count Records	12				B
4	Site		Average	Avg Excluding Zero		C
5	A		1.333333333	4
6
7	Date Failure	Site	Calc days between	Days Between
8	3/01/2017	A	0	1.333333333
9	3/01/2017	A	0	1.333333333
10	6/01/2017	A	3	1.333333333
11	9/01/2017	A	3	1.333333333
12	9/01/2017	A	0	1.333333333
13	9/01/2017	A	0	1.333333333
14	9/01/2017	A	0	1.333333333
15	9/01/2017	A	0	1.333333333
16	9/01/2017	A	0	1.333333333
17	15/01/2017	A	6	1.333333333
18	19/01/2017	A	4	1.333333333
19	19/01/2017	A	0	1.333333333
Sheet2

Cell Formulas
Range	Formula
B3	=COUNTIFS(Table1[Date Failure],">="&$B$1,Table1[Date Failure],"<="&$B$2,Table1[Site],$A$5)
B8	{=IF(ROWS(B$7:B7)>$B$3,"",INDEX(Table1[Site],SMALL(IF(Table1[Site]=$A$5,ROW(Table1[Date Failure])-ROW(Table1[#Headers])),ROWS(B$7:B7))))}
C5	=IF(A5="","",AVERAGEIFS(Table24[Calc days between],Table24[Date Failure],">="&$B$1,Table24[Date Failure],"<="&$B$2))
C9	=IF(A9="","",A9-A8)
D5	=IF(A5="","",AVERAGEIF(Table24[Calc days between],"<>0"))
D8	=IF(C8="","",AVERAGE([Calc days between]))
A8	{=IF(ROWS(A$7:A7)>$B$3,"",INDEX(Table1[Date Failure],SMALL(IF(Table1[Site]=$A$5,ROW(Table1[Date Failure])-ROW(Table1[#Headers])),ROWS(A$7:A7))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Here's a link to my mock up if this helps;

https://www.dropbox.com/s/4dyqk8ba9... failure sites data_derick_t_tables.xlsx?dl=0

 

[Derick_T]

That almost works perfectly.

Only oddball thing I see happening is that I have to start the range of dates from 1/1/2017 or my correct Dates doesn't show-up in the Dates column (Table24), which as a result throws off my Average formula. The dates always show starting in January 2017 even if I select a range for 2018 or 2019 time frame.

The Counts formula seems to be right on though.

Link to the file: https://drive.google.com/file/d/0B1mgDeo6i5j2SHpfSmRfb0RMVmVjM2xYdk1MenJHQXJaNGc4/view?usp=sharing

Thanks for all the help!

 

[RasGhul]

I added a Table24 Count so I could compare Table1 site counts and it seems to be working correctly now even for different date ranges;

https://www.dropbox.com/s/suqnddq2ztx5dz2/Days Between Failures Example_jp.xlsx?dl=0

 

[Derick_T]

Works great...thanks for all the help!