using sumproduct to find shortest and longest occurrence

[rhybeka]

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:

=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??

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>

 

[rhybeka]

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]

Looks like you want...

1. Control+shift+enter, not just enter

=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

=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]

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]

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]

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.

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]

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]

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]

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.