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:
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??
• 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 1 Date Time Temprature Probe 1 out of range alarms Tempurature Probe 2 out of range alarms 2 10/12/12 7:30 68 1 0 67 1 0 3 10/12/12 8:00 68 1 0 67 1 0 4 10/12/12 8:30 68 1 0 67 1 0 5 10/12/12 9:00 68 1 0 67 1 0 6 10/12/12 9:30 68 1 0 67 1 0 7 10/12/12 10:00 68 1 0 67 1 0 8 10/12/12 10:30 68 1 0 68 1 0 9 10/12/12 11:00 69 0 1 68 1 0 10 10/12/12 11:30 69 0 1 68 1 0 11 10/12/12 12:00 69 0 1 68 1 0 12 10/16/12 5:30 69 0 1 69 0 1 13 10/16/12 6:00 69 0 1 69 0 1 14 10/16/12 6:30 69 0 1 69 0 1 15 10/16/12 7:00 68 1 0 69 0 1 16 10/16/12 7:30 69 0 1 69 0 1 17 10/16/12 8:00 69 0 1 69 0 1 18 10/16/12 8:30 69 0 1 69 0 1 19 10/16/12 9:00 69 0 1 69 0 1 20 10/16/12 9:30 69 0 1 69 0 1 21 10/16/12 15:00 76 0 1 73 0 0 22 10/16/12 15:30 76 0 1 74 0 0 23 10/16/12 16:00 76 0 1 74 0 0 24 10/16/12 16:30 76 0 1 74 0 0 25 10/16/12 17:00 76 0 1 74 0 0 26 10/16/12 23:00 69 0 1 69 0 1 27 10/16/12 23:30 69 0 1 69 0 1 28 10/17/12 0:00 69 0 1 69 0 1 29 10/17/12 0:30 69 0 1 69 0 1 30 10/17/12 1:00 69 0 1 69 0 1 31 10/17/12 1:30 69 0 1 68 1 0 32 10/17/12 2:00 68 1 0 68 1 0 33 10/17/12 2:30 68 1 0 68 1 0 34 10/17/12 3:00 68 1 0 68 1 0 35 10/17/12 3:30 68 1 0 68 1 0 36 10/17/12 4:00 68 1 0 68 1 0 37 10/17/12 4:30 68 1 0 68 1 0 38 10/17/12 5:00 68 1 0 68 1 0 39 10/17/12 5:30 68 1 0 67 1 0 40 10/17/12 6:00 67 1 0 67 1 0 41 10/17/12 6:30 67 1 0 67 1 0 42 10/17/12 7:00 68 1 0 67 1 0 43 10/17/12 7:30 68 1 0 67 1 0 44 10/17/12 8:00 68 1 0 68 1 0 45 10/17/12 8:30 68 1 0 68 1 0 46 10/17/12 9:00 68 1 0 68 1 0 47 10/17/12 9:30 69 0 1 68 1 0 48 10/17/12 14:30 75 0 0 73 0 0 49 10/17/12 15:00 76 0 1 73 0 0 50 10/17/12 15:30 77 1 0 74 0 0 51 10/17/12 16:00 76 0 1 74 0 0 52 10/17/12 16:30 77 1 0 74 0 0 53 10/17/12 17:00 76 0 1 74 0 0 54 10/17/12 17:30 76 0 1 74 0 0 55 10/18/12 3:00 69 0 1 69 0 1 56 10/18/12 3:30 69 0 1 69 0 1 57 10/18/12 4:00 69 0 1 69 0 1 58 10/18/12 4:30 69 0 1 69 0 1 59 10/18/12 5:00 69 0 1 69 0 1 60 10/18/12 5:30 69 0 1 69 0 1 61 10/18/12 6:00 69 0 1 68 1 0 62 10/18/12 6:30 69 0 1 68 1 0 63 10/18/12 7:00 69 0 1 68 1 0 64 10/18/12 7:30 68 1 0 68 1 0 65 10/18/12 8:00 68 1 0 68 1 0 66 10/18/12 8:30 69 0 1 69 0 1 67 10/18/12 9:00 69 0 1 69 0 1 68 10/18/12 9:30 69 0 1 69 0 1 69 10/18/12 23:30 69 0 1 70 0 0 70 10/19/12 0:00 69 0 1 69 0 1 71 10/19/12 0:30 69 0 1 69 0 1 72 10/19/12 1:00 70 0 0 69 0 1 73 10/19/12 1:30 69 0 1 69 0 1 74 10/19/12 5:30 69 0 1 69 0 1 75 10/19/12 6:00 69 0 1 69 0 1 76 10/19/12 6:30 69 0 1 69 0 1 77 10/19/12 7:00 69 0 1 69 0 1 78 10/19/12 7:30 69 0 1 69 0 1 79 10/19/12 8:00 69 0 1 69 0 1 80 10/19/12 8:30 69 0 1 69 0 1 81 10/19/12 9:00 69 0 1 69 0 1 82 10/19/12 9:30 69 0 1 69 0 1 83 10/20/12 5:00 69 0 1 69 0 1 84 10/20/12 5:30 69 0 1 69 0 1 85 10/20/12 6:00 69 0 1 69 0 1 86 10/20/12 6:30 69 0 1 69 0 1 87 10/20/12 7:00 69 0 1 69 0 1 88 10/20/12 7:30 69 0 1 69 0 1 89 10/20/12 8:00 69 0 1 69 0 1 90 10/20/12 8:30 69 0 1 69 0 1 91 10/20/12 9:00 69 0 1 69 0 1 92 10/20/12 9:30 69 0 1 69 0 1 93 Total 27 62 32 46 94 Overall Time (in hrs) 13.5 31.0 16.0 23.0 95 Occurences 6 8 3 5 96 Shortest Occurrence 97 Longest Occurrence 98 Avg Length of Occurrence
<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>Sheet1
Worksheet Formulas
Cell Formula G1 =D1 H1 =E1 D2 =IF(C2<=68,1,IF(C2>=77,1,0)) E2 =IF(C2=69,1,IF(C2=76,1,0)) D94 =IF(D93/2=0,"",D93/2) E94 =IF(E93/2=0,"",E93/2) F94 =IF(F93/2=0,"",F93/2) D95 =SUMPRODUCT((D1:D92<>1)*(D2:D93=1)) E95 =SUMPRODUCT((E1:E92<>1)*(E2:E93=1))
<THEAD>
</THEAD><TBODY>
</TBODY>
<TBODY>
</TBODY>