using sumproduct to find shortest and longest occurrence

rhybeka · Oct 9, 2013

Hi all! I received help at another Excel forum for the first part of my problem ( finding the occurrences of temperatures reported from a probe : Count the number of occurences in a column thanks PCI!), and I happened to find more material over here that was helpful, so I thought I’d post the second half here. I'm a newb with a lot of these types of functions (SUMPRODUCT/MATCH/FREQUENCY, etc) I've been attempting for the past few days to cobble together a formula (or three) that would allow me to find:
• smallest occurrence for the probe(s) that were out of range or an alarm went off
• largest occurrence for that probe(s) that were out of range or an alarm went off
• average length of occurrence for the probe(s) that were out of range or an alarm went off

Something just isn’t clicking.

The original formula for the occurrences for the temp probe 1 in my sample data file is:

Rich (BB code):

=SUMPRODUCT((D1:D433<>1)*(D2:D434=1))

so for the first probe
• 12 occurrences where the temperature was out of the specified range
• 28 occurrences where the temperature set off an alarm.

So to count the shortest occurrence, I need to find a way to count the number of 1’s in the D column and have Excel just return to me the shortest one it found – most likely just a 1 but we have to check to make certain. There could be a few rarities that are longer though. Same basic theory for the longest occurrence, just reversing and having it return the largest occurrence of 1’s in column D. Since I can't seem to wrap my brain enough around the first two I haven't even tried an average

Would someone mind pointing me in the right direction??

Rich (BB code):

Excel 2010
A B C D E F G H
Date 


<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>

</THEAD><TBODY>

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


[TD="align: center"]Time[/TD]
[TD="bgcolor: #d8e4bc, align: center"]Temprature Probe 1[/TD]
[TD="align: center"]out of range[/TD]
[TD="bgcolor: #b7dee8, align: center"]alarms[/TD]
[TD="bgcolor: #d8e4bc, align: center"]Tempurature Probe 2[/TD]
[TD="align: center"]out of range[/TD]
[TD="bgcolor: #b7dee8, align: center"]alarms[/TD]


[TD="align: center"]2[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]3[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]4[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]5[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]6[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]7[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]10:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]8[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]10:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]9[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]11:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]10[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]11:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]11[/TD]
[TD="align: right"]10/12/12[/TD]
[TD="align: right"]12:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]12[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]5:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]13[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]14[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]6:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]15[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]7:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]16[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]17[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]18[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]19[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]20[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]21[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]15:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]73[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]22[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]15:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]23[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]16:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]24[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]16:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]25[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]17:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]26[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]23:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]27[/TD]
[TD="align: right"]10/16/12[/TD]
[TD="align: right"]23:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]28[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]0:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]29[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]0:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]30[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]1:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]31[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]1:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]32[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]2:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]33[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]2:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]34[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]3:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]35[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]3:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]36[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]4:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]37[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]4:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]38[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]5:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]39[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]5:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]40[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]41[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]6:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]42[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]7:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]43[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]67[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]44[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]45[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]46[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]47[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]48[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]14:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]75[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]73[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]49[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]15:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]73[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]50[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]15:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]77[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]51[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]16:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]52[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]16:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]77[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]53[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]17:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]54[/TD]
[TD="align: right"]10/17/12[/TD]
[TD="align: right"]17:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]76[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]74[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]55[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]3:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]56[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]3:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]57[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]4:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]58[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]4:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]59[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]5:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]60[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]5:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]61[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]62[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]6:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]63[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]7:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]64[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]65[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]68[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]66[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]67[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]68[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]69[/TD]
[TD="align: right"]10/18/12[/TD]
[TD="align: right"]23:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]70[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]


[TD="align: center"]70[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]0:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]71[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]0:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]72[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]1:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]70[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]0[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]73[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]1:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]74[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]5:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]75[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]76[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]6:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]77[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]7:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]78[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]79[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]80[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]81[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]82[/TD]
[TD="align: right"]10/19/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]83[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]5:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]84[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]5:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]85[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]86[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]6:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]87[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]7:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]88[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]7:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]89[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]8:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]90[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]8:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]91[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]9:00[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]92[/TD]
[TD="align: right"]10/20/12[/TD]
[TD="align: right"]9:30[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]
[TD="bgcolor: #d8e4bc, align: center"]69[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #b7dee8, align: center"]1[/TD]


[TD="align: center"]93[/TD]
[TD="align: right"]Total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]62[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]46[/TD]


[TD="align: center"]94[/TD]
[TD="align: right"]Overall Time (in hrs)[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]13.5[/TD]
[TD="align: center"]31.0[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]16.0[/TD]
[TD="align: center"]23.0[/TD]


[TD="align: center"]95[/TD]
[TD="align: right"]Occurences[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]


[TD="align: center"]96[/TD]
[TD="align: right"]Shortest Occurrence[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]


[TD="align: center"]97[/TD]
[TD="align: right"]Longest Occurrence[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]


[TD="align: center"]98[/TD]
[TD="align: right"]Avg Length of Occurrence[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]


</TBODY>
Sheet1
[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]G1[/TH]
[TD="align: left"]=D1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]H1[/TH]
[TD="align: left"]=E1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]D2[/TH]
[TD="align: left"]=IF(C2<=68,1,IF(C2>=77,1,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]E2[/TH]
[TD="align: left"]=IF(C2=69,1,IF(C2=76,1,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]D94[/TH]
[TD="align: left"]=IF(D93/2=0,"",D93/2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]E94[/TH]
[TD="align: left"]=IF(E93/2=0,"",E93/2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]F94[/TH]
[TD="align: left"]=IF(F93/2=0,"",F93/2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]D95[/TH]
[TD="align: left"]=SUMPRODUCT((D1:D92<>1)*(D2:D93=1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]E95[/TH]
[TD="align: left"]=SUMPRODUCT((E1:E92<>1)*(E2:E93=1))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

rhybeka · Oct 10, 2013

well, hopefully the title isn't scaring anyone off - I don't need to use sumproduct to find my shortest/longest occurrence if there is a better / easier way

I've been trying to adapt a few formulas with no good results - think I'm doing something wrong.

Aladin Akyurek · Oct 10, 2013

Looks like you want...

1. Control+shift+enter, not just enter

Rich (BB code):

=MIN(IF(FREQUENCY(IF($D$2:$D$92=1,ROW($D$2:$D$92)),
  IF($D$2:$D$92<>1,ROW($D$2:$D$92)))>0,FREQUENCY(IF($D$2:$D$92=1,ROW($D$2:$D$92)),
  IF($D$2:$D$92<>1,ROW($D$2:$D$92)))))

2. Control+shift+enter, not just enter

Rich (BB code):

=MAX(FREQUENCY(IF($D$2:$D$92=1,ROW($D$2:$D$92)),IF($D$2:$D$92<>1,ROW($D$2:$D$92))))

Marcelo Branco · Oct 10, 2013

Hi Aladin,

I think this also works to find the shortest occurence and it's shorter

=SMALL(FREQUENCY(IF($D$2:$D$433=1,ROW($D$2:$D$433)),IF($D$2:$D$433=1,IF($D$3:$D$434<>1,ROW($D$2:$D$433)))),2)

confirmed with Ctrl+Shift+Enter

M.

pgc01 · Oct 10, 2013

Hi

Same idea but with another option for the Min:

=1/MAX(IFERROR(1/FREQUENCY(IF(D2:D92=1,ROW(D2:D92)),IF(D2:D92<>1,ROW(D2:D92))),0))

... confirmed with CSE

Remark: The "using sumproduct" in the title makes no sense to me.
Your objective is to "find shortest and longest occurrence", IMO that should be the title.

Aladin Akyurek · Oct 10, 2013

Marcelo Branco said:
Hi Aladin,

I think this also works to find the shortest occurence and it's shorter

=SMALL(FREQUENCY(IF($D$2:$D$433=1,ROW($D$2:$D$433)),IF($D$2:$D$433=1,IF($D$3:$D$434<>1,ROW($D$2:$D$433)))),2)

confirmed with Ctrl+Shift+Enter

M.

pgc01 said:
Hi

Same idea but with another option for the Min:

=1/MAX(IFERROR(1/FREQUENCY(IF(D2:D92=1,ROW(D2:D92)),IF(D2:D92<>1,ROW(D2:D92))),0))

... confirmed with CSE

Remark: The "using sumproduct" in the title makes no sense to me.
Your objective is to "find shortest and longest occurrence", IMO that should be the title.

The idea is to avoid an unjustified 0 in min case. The efficient alternative suggestions which effect that are of course ok.

rhybeka · Oct 11, 2013

Thanks everybody! You are all rock stars

I hadn't even realized I could use small/large - I have so much to learn! Trying to find the average length of an occurrence is most likely out of my league for the time being! I think I slightly understand the logic behind Aladin's formulas - PGC and Marcelo I'll have to noodle on yours a bit longer.

And PGC you're right about my title - unfortunately I can't go back to change it that I know of.

Aladin Akyurek · Oct 11, 2013

rhybeka said:
Thanks everybody! You are all rock stars I hadn't even realized I could use small/large - I have so much to learn! Trying to find the average length of an occurrence is most likely out of my league for the time being! I think I slightly understand the logic behind Aladin's formulas - PGC and Marcelo I'll have to noodle on yours a bit longer. And PGC you're right about my title - unfortunately I can't go back to change it that I know of.

You are welcome. The kernel idea is the conditional FREQUENCY expression.

Marcelo Branco · Oct 11, 2013

rhybeka said:
Thanks everybody! You are all rock stars I hadn't even realized I could use small/large - I have so much to learn! Trying to find the average length of an occurrence is most likely out of my league for the time being! I think I slightly understand the logic behind Aladin's formulas - PGC and Marcelo I'll have to noodle on yours a bit longer. And PGC you're right about my title - unfortunately I can't go back to change it that I know of.

You are welcome.

By the way:
To calculate the Average all you have to do is take the first formula provided by Aladin and change Min to AVERAGE.
Of course, confirming with Ctrl+Shift+Enter

Is also possible to adapt my formula to calculate the AVG - but for the moment let's stick with Aladin's formula.

M.

using sumproduct to find shortest and longest occurrence

rhybeka

New Member

rhybeka

New Member

Aladin Akyurek

MrExcel MVP

Marcelo Branco

MrExcel MVP

pgc01

MrExcel MVP

Aladin Akyurek

MrExcel MVP

rhybeka

New Member

Aladin Akyurek

MrExcel MVP

Marcelo Branco

MrExcel MVP

Similar threads

Share this page

using sumproduct to find shortest and longest occurrence

New Member

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock