Find record amount of days with no incidents

[Pofski]

Hi,

my situation is as follows.
We have 3 columns
Column A has dates of different facility's
Column B has different incidents
Column C has different dates.

I am looking for a way to have the max (and maybe min) amount of days between certain incidents for each facility.

So, for example, what would be the maximum amount of days between a finger cuts in the cookie factory.

I have attached a example file.
At this moment, i have absolutely no idea how to even start with it. I was hoping on of the excel wizards here could help me out.

Thank you in advance

 

[Xiq]

Hi Pofski,

Please check attached file.

 

[Jake Collins]

if you order column c by date order then in D3 use

=IF(MAX(($A2:A$3=A3)*($B2:B$3=B3)*($C2:C$3))<>0, C3-MAX(($A2:A$3=A3)*($B2:B$3=B3)*($C2:C$3)),0)

will give you the number of days since the the previous occurance in the list

copy down

then for the max use something like

=MAX(($A$3:$A$320=A3)*($B$3:$B$320=B3)*($D$3:$D$320))

where A3 and B3 are storing the combination of facility and incident

for the min

=LARGE(($A$3:$A$320=A3)*($B$3:$B$320=B3)*($D$3:$D$320),MAX(SUM(($A$3:$A$320=A3)*($B$3:$B$320=B3)*($D$3:$D$320<>0)),1))

again with A3 and B3 storing the combination of facility and incident you want the min for


(all entered using ctrl shift enter)

 

[Pofski]

thnx Jake, that works perfectly