nb-
01-13-2014, 11:44 AM
Lets say I have data for a year that looks like this;
Date
Date
Temp High
Temp Low
01/05/13
19
10
02/05/13
20
11.1
03/05/13
20
12
04/05/13
20
11.5
05/05/13
19.5
11
06/05/13
20
12
07/05/13
20.2
12
08/05/13
21
13
09/05/13
22
12.5
What I want to do is find say the the first date in a range of where the high temperature exceeded 20, and the value it reached.
E.g. on the above data, the first time the high temperature was greater than 20 between the 03/05/13 and 08/05/13 (inclusive)?
The answer I would be looking for would be 07/05/13 and 20.2.
You could simply determine if this happens using a sumproduct and get the values using clunky array formulas, but I keep thinking there must be better and more efficient way of doing it, but I cant think of one. Any ideas?
Date
Date
Temp High
Temp Low
01/05/13
19
10
02/05/13
20
11.1
03/05/13
20
12
04/05/13
20
11.5
05/05/13
19.5
11
06/05/13
20
12
07/05/13
20.2
12
08/05/13
21
13
09/05/13
22
12.5
What I want to do is find say the the first date in a range of where the high temperature exceeded 20, and the value it reached.
E.g. on the above data, the first time the high temperature was greater than 20 between the 03/05/13 and 08/05/13 (inclusive)?
The answer I would be looking for would be 07/05/13 and 20.2.
You could simply determine if this happens using a sumproduct and get the values using clunky array formulas, but I keep thinking there must be better and more efficient way of doing it, but I cant think of one. Any ideas?