First row greater than value with Vlookup?

[ccalkins]

Good evening,
I have come to a roadblock while trying to writing a formula. I have daily climate data from 1/1/1981-12/31/2020 for 7 different sites. I am trying to find the first instance in each year the accumulated degree days is >=150 (column J) and report the Julian Date this happened (Column K). Ideally this would happen in a separate summary sheet. In this example 158 would be the correct result for SAREC 1981. Any help would be greatly appreciated.
Thanks,
Craig
Summary Sheet

 

[Zot]

I'm assuming your data would be from row 2 to 300.

=INDEX(K2:K300,MATCH(TRUE,INDEX(J2:J300>=150,0),))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Further, we cannot copy from an image to test. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ccalkins]

My data goes from Row 2 (1/1/1981) to Row 14,611 (12/31/2020). I was able to use an Xlookup formula but manually putting in the ranges per year i.e. 1981 rows 2-366, 1982 rows 367-731 in Mac numbers but would prefer to have something do it automatically... I have tried inserting my data below.
Thanks,
Craig

Climate Data.xlsx
	A									J	K
2	Date									Accumulated Degree Days	Julian Date
3	1981-01-01									0	1
4	1981-01-02									0	2
5	1981-01-03									0	3
6	1981-01-04									0	4
7	1981-01-05									0	5
8	1981-01-06									0	6
9	1981-01-07									0	7
10	1981-01-08									0	8
11	1981-01-09									0	9
12	1981-01-10									0	10
13	1981-01-11									0	11
14	1981-01-12									0	12
15	1981-01-13									0	13
16	1981-01-14									0	14
17	1981-01-15									0	15
18	1981-01-16									0	16
19	1981-01-17									0	17
20	1981-01-18									0	18
21	1981-01-19									0	19
22	1981-01-20									0	20
23	1981-01-21									0	21
24	1981-01-22									0	22
25	1981-01-23									0	23
26	1981-01-24									0	24
27	1981-01-25									0	25
28	1981-01-26									0	26
29	1981-01-27									0	27
30	1981-01-28									0	28
31	1981-01-29									0	29
32	1981-01-30									0	30
33	1981-01-31									0	31
34	1981-02-01									0	32
35	1981-02-02									0	33
36	1981-02-03									0	34
37	1981-02-04									0	35
38	1981-02-05									0	36
39	1981-02-06									0	37
40	1981-02-07									0	38
41	1981-02-08									0	39
42	1981-02-09									0	40
43	1981-02-10									0	41
44	1981-02-11									0	42
45	1981-02-12									0	43
46	1981-02-13									0	44
47	1981-02-14									0	45
48	1981-02-15									0	46
49	1981-02-16									0	47
50	1981-02-17									0	48
51	1981-02-18									0	49
52	1981-02-19									0	50
53	1981-02-20									0	51
54	1981-02-21									0	52
55	1981-02-22									0	53
56	1981-02-23									0	54
57	1981-02-24									0	55
58	1981-02-25									0	56
59	1981-02-26									0	57
60	1981-02-27									0	58
61	1981-02-28									0	59
62	1981-03-01									0	60
63	1981-03-02									0	61
64	1981-03-03									0	62
65	1981-03-04									0	63
66	1981-03-05									0	64
67	1981-03-06									0	65
68	1981-03-07									0	66
69	1981-03-08									0	67
70	1981-03-09									0	68
71	1981-03-10									0	69
72	1981-03-11									0	70
73	1981-03-12									0	71
74	1981-03-13									0	72
75	1981-03-14									0	73
76	1981-03-15									0	74
77	1981-03-16									0	75
78	1981-03-17									0	76
79	1981-03-18									0	77
80	1981-03-19									0	78
81	1981-03-20									0	79
82	1981-03-21									0	80
83	1981-03-22									0	81
84	1981-03-23									0	82
85	1981-03-24									0	83
86	1981-03-25									0	84
87	1981-03-26									0	85
88	1981-03-27									0	86
89	1981-03-28									0	87
90	1981-03-29									0	88
91	1981-03-30									0	89
92	1981-03-31									0	90
93	1981-04-01									0	91
94	1981-04-02									0	92
95	1981-04-03									1.2	93
96	1981-04-04									1.2	94
97	1981-04-05									1.2	95
98	1981-04-06									1.2	96
99	1981-04-07									1.2	97
100	1981-04-08									1.2	98
101	1981-04-09									1.2	99
102	1981-04-10									1.7	100
103	1981-04-11									1.7	101
104	1981-04-12									2.3	102
105	1981-04-13									4.9	103
106	1981-04-14									4.9	104
107	1981-04-15									4.9	105
108	1981-04-16									6.5	106
109	1981-04-17									9.8	107
110	1981-04-18									13.9	108
111	1981-04-19									18.9	109
112	1981-04-20									18.9	110
113	1981-04-21									18.9	111
114	1981-04-22									18.9	112
115	1981-04-23									18.9	113
116	1981-04-24									19.6	114
117	1981-04-25									24.9	115
118	1981-04-26									31.9	116
119	1981-04-27									38.7	117
120	1981-04-28									42.4	118
121	1981-04-29									46.6	119
122	1981-04-30									49.9	120
123	1981-05-01									51.5	121
124	1981-05-02									58.5	122
125	1981-05-03									66.6	123
126	1981-05-04									66.6	124
127	1981-05-05									66.6	125
128	1981-05-06									66.9	126
129	1981-05-07									66.9	127
130	1981-05-08									66.9	128
131	1981-05-09									66.9	129
132	1981-05-10									66.9	130
133	1981-05-11									67.3	131
134	1981-05-12									67.3	132
135	1981-05-13									67.3	133
136	1981-05-14									67.3	134
137	1981-05-15									68.9	135
138	1981-05-16									72.4	136
139	1981-05-17									72.4	137
140	1981-05-18									72.4	138
141	1981-05-19									72.4	139
142	1981-05-20									72.4	140
143	1981-05-21									74.7	141
144	1981-05-22									76.8	142
145	1981-05-23									79.7	143
146	1981-05-24									79.7	144
147	1981-05-25									81.3	145
148	1981-05-26									85.1	146
149	1981-05-27									91.6	147
150	1981-05-28									97.2	148
151	1981-05-29									102.6	149
152	1981-05-30									102.8	150
153	1981-05-31									108.5	151
154	1981-06-01									114.5	152
155	1981-06-02									119.8	153
156	1981-06-03									125.9	154
157	1981-06-04									133	155
158	1981-06-05									140.3	156
159	1981-06-06									146.4	157
160	1981-06-07									156.6	158
161	1981-06-08									166.6	159
162	1981-06-09									176.6	160
163	1981-06-10									182.6	161
164	1981-06-11									190.3	162
165	1981-06-12									199.9	163
166	1981-06-13									212.5	164
167	1981-06-14									218.3	165
168	1981-06-15									218.9	166
169	1981-06-16									218.9	167
170	1981-06-17									225.8	168
171	1981-06-18									229.4	169
172	1981-06-19									233.7	170
173	1981-06-20									242.1	171
174	1981-06-21									251.2	172
175	1981-06-22									257.4	173
176	1981-06-23									269.2	174
177	1981-06-24									282.2	175
178	1981-06-25									291.8	176
179	1981-06-26									302	177
180	1981-06-27									314.9	178
181	1981-06-28									328.4	179
182	1981-06-29									337.1	180
183	1981-06-30									344.7	181
184	1981-07-01									357.6	182
185	1981-07-02									369.9	183
186	1981-07-03									376.3	184
SAREC - SAREC 42.0917, -104.394

Cell Formulas
Range	Formula
J3:J186	J3	=SUM(I$3:I3)
K3:K186	K3	=$A3-DATE(YEAR($A3),1,1)+1

Summary sheet where I would like the formula to be placed.

Climate Data.xlsx
	A	B	C
1	Year	SAREC	Heart Mountain
2	1981
3	1982
4	1983
5	1984
6	1985
7	1986
8	1987
9	1988
10	1989
11	1990
12	1991
13	1992
14	1993
15	1994
16	1995
17	1996
18	1997
19	1998
20	1999
21	2000
22	2001
23	2002
24	2003
25	2004
26	2005
27	2006
28	2007
29	2008
30	2009
31	2010
32	2011
33	2012
34	2013
35	2014
36	2015
37	2016
38	2017
39	2018
40	2019
41	2020
Summary

 

[Peter_SSs]

Thanks for getting XL2BB going.

Unfortunately, there is no useful data in col J of the main sheet and it will need data to do the job won't it?

Also, there is no sample expected results, so what you actually want is still a bit of a guess.

I cannot see if or how columns B & C in your Summary sheet relate to the main sheet?

 

[ccalkins]

Thanks for getting XL2BB going.

Unfortunately, there is no useful data in col J of the main sheet and it will need data to do the job won't it?

Also, there is no sample expected results, so what you actually want is still a bit of a guess.

I cannot see if or how columns B & C in your Summary sheet relate to the main sheet?

Peter,
Here is a short sample of the entire data set. Column I is calculated as the mean temperature-11.2 (temperature I'm interested in). If this value is less than 0 then with an If formula I have it report as a 0. Column J is a running count of how many degree days there have been in the year up to that date, hence the zeros until it starts warming up in April/May, so the 0 is an actual data point. Some years in the higher elevation areas it will not go above 150 degree days so it may not have a Julian date.
I am wanting the formula (or whatever) to go into the summary sheet so these Julian dates can be extracted and put into a stats program. I have included a screenshot of the expected results. Thanks again,
Craig

Climate Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	ppt (mm)	tmin (degrees C)	tmean (degrees C)	tmax (degrees C)	tdmean (degrees C)	vpdmin (hPa)	vpdmax (hPa)	DD5011.2	Accumulated Degree Days	Julian Date
2	1981-01-01	0	-4.1	4.6	13.2	-2.4	0.61	9.39	0	0	1
3	1981-01-02	0	-7	0.3	7.6	-5	0.31	5.83	0	0	2
4	1981-01-03	0	-6.9	3.9	14.7	-5.7	0.47	12.43	0	0	3
5	1981-01-04	0	-7.1	2.6	12.2	-6	0.43	9.81	0	0	4
6	1981-01-05	0	-4.7	3.5	11.6	-5.6	0.91	9.12	0	0	5
7	1981-01-06	0	-5.6	0.7	6.9	-5.6	0.67	5.62	0	0	6
8	1981-01-07	0	-7.1	0	7.2	-8.6	0.77	6.8	0	0	7
9	1981-01-08	0	-9.5	0	9.4	-9.5	0.63	8.6	0	0	8
10	1981-01-09	0	-9.4	0.4	10.2	-9.2	0.59	8.98	0	0	9
11	1981-01-10	0	-9.7	-3.8	2.2	-8.5	0.32	3.42	0	0	10
12	1981-01-11	0	-10.7	-1.4	7.9	-9.1	0.38	7.17	0	0	11
13	1981-01-12	0	-12	-2.5	7.1	-10.5	0.29	6.66	0	0	12
14	1981-01-13	0	-9.6	2.1	13.8	-14	1.08	13.7	0	0	13
15	1981-01-14	0	-7.4	1.6	10.5	-10.6	0.97	9.63	0	0	14
16	1981-01-15	1.42	-8.6	0.3	9.1	-9.7	0.78	8.13	0	0	15
SAREC - SAREC 42.0917, -104.394

Cell Formulas
Range	Formula
I2:I16	I2	=IF((D2-11.2)<0,0,D2-11.2)
J2:J16	J2	=SUM(I$2:I2)
K2:K16	K2	=$A2-DATE(YEAR($A2),1,1)+1

 

[ccalkins]

Peter,
Here is a short sample of the entire data set. Column I is calculated as the mean temperature-11.2 (temperature I'm interested in). If this value is less than 0 then with an If formula I have it report as a 0. Column J is a running count of how many degree days there have been in the year up to that date, hence the zeros until it starts warming up in April/May, so the 0 is an actual data point. Some years in the higher elevation areas it will not go above 150 degree days so it may not have a Julian date.
I am wanting the formula (or whatever) to go into the summary sheet so these Julian dates can be extracted and put into a stats program. I have included a screenshot of the expected results. Thanks again,
Craig

Climate Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	ppt (mm)	tmin (degrees C)	tmean (degrees C)	tmax (degrees C)	tdmean (degrees C)	vpdmin (hPa)	vpdmax (hPa)	DD5011.2	Accumulated Degree Days	Julian Date
2	1981-01-01	0	-4.1	4.6	13.2	-2.4	0.61	9.39	0	0	1
3	1981-01-02	0	-7	0.3	7.6	-5	0.31	5.83	0	0	2
4	1981-01-03	0	-6.9	3.9	14.7	-5.7	0.47	12.43	0	0	3
5	1981-01-04	0	-7.1	2.6	12.2	-6	0.43	9.81	0	0	4
6	1981-01-05	0	-4.7	3.5	11.6	-5.6	0.91	9.12	0	0	5
7	1981-01-06	0	-5.6	0.7	6.9	-5.6	0.67	5.62	0	0	6
8	1981-01-07	0	-7.1	0	7.2	-8.6	0.77	6.8	0	0	7
9	1981-01-08	0	-9.5	0	9.4	-9.5	0.63	8.6	0	0	8
10	1981-01-09	0	-9.4	0.4	10.2	-9.2	0.59	8.98	0	0	9
11	1981-01-10	0	-9.7	-3.8	2.2	-8.5	0.32	3.42	0	0	10
12	1981-01-11	0	-10.7	-1.4	7.9	-9.1	0.38	7.17	0	0	11
13	1981-01-12	0	-12	-2.5	7.1	-10.5	0.29	6.66	0	0	12
14	1981-01-13	0	-9.6	2.1	13.8	-14	1.08	13.7	0	0	13
15	1981-01-14	0	-7.4	1.6	10.5	-10.6	0.97	9.63	0	0	14
16	1981-01-15	1.42	-8.6	0.3	9.1	-9.7	0.78	8.13	0	0	15
SAREC - SAREC 42.0917, -104.394

Cell Formulas
Range	Formula
I2:I16	I2	=IF((D2-11.2)<0,0,D2-11.2)
J2:J16	J2	=SUM(I$2:I2)
K2:K16	K2	=$A2-DATE(YEAR($A2),1,1)+1

View attachment 45400

I had to copy and paste values to get more of the dataset into the minisheet. Data goes from row 2 to 14,611

Climate Data.xlsx
	A	B	C
1	Date	Accumulated Degree Days	Julian Date
2	1981-01-01	0	1
3	1981-01-02	0	2
4	1981-01-03	0	3
5	1981-01-04	0	4
6	1981-01-05	0	5
7	1981-01-06	0	6
8	1981-01-07	0	7
9	1981-01-08	0	8
10	1981-01-09	0	9
11	1981-01-10	0	10
12	1981-01-11	0	11
13	1981-01-12	0	12
14	1981-01-13	0	13
15	1981-01-14	0	14
16	1981-01-15	0	15
17	1981-01-16	0	16
18	1981-01-17	0	17
19	1981-01-18	0	18
20	1981-01-19	0	19
21	1981-01-20	0	20
22	1981-01-21	0	21
23	1981-01-22	0	22
24	1981-01-23	0	23
25	1981-01-24	0	24
26	1981-01-25	0	25
27	1981-01-26	0	26
28	1981-01-27	0	27
29	1981-01-28	0	28
30	1981-01-29	0	29
31	1981-01-30	0	30
32	1981-01-31	0	31
33	1981-02-01	0	32
34	1981-02-02	0	33
35	1981-02-03	0	34
36	1981-02-04	0	35
37	1981-02-05	0	36
38	1981-02-06	0	37
39	1981-02-07	0	38
40	1981-02-08	0	39
41	1981-02-09	0	40
42	1981-02-10	0	41
43	1981-02-11	0	42
44	1981-02-12	0	43
45	1981-02-13	0	44
46	1981-02-14	0	45
47	1981-02-15	0	46
48	1981-02-16	0	47
49	1981-02-17	0	48
50	1981-02-18	0	49
51	1981-02-19	0	50
52	1981-02-20	0	51
53	1981-02-21	0	52
54	1981-02-22	0	53
55	1981-02-23	0	54
56	1981-02-24	0	55
57	1981-02-25	0	56
58	1981-02-26	0	57
59	1981-02-27	0	58
60	1981-02-28	0	59
61	1981-03-01	0	60
62	1981-03-02	0	61
63	1981-03-03	0	62
64	1981-03-04	0	63
65	1981-03-05	0	64
66	1981-03-06	0	65
67	1981-03-07	0	66
68	1981-03-08	0	67
69	1981-03-09	0	68
70	1981-03-10	0	69
71	1981-03-11	0	70
72	1981-03-12	0	71
73	1981-03-13	0	72
74	1981-03-14	0	73
75	1981-03-15	0	74
76	1981-03-16	0	75
77	1981-03-17	0	76
78	1981-03-18	0	77
79	1981-03-19	0	78
80	1981-03-20	0	79
81	1981-03-21	0	80
82	1981-03-22	0	81
83	1981-03-23	0	82
84	1981-03-24	0	83
85	1981-03-25	0	84
86	1981-03-26	0	85
87	1981-03-27	0	86
88	1981-03-28	0	87
89	1981-03-29	0	88
90	1981-03-30	0	89
91	1981-03-31	0	90
92	1981-04-01	0	91
93	1981-04-02	0	92
94	1981-04-03	1.2	93
95	1981-04-04	1.2	94
96	1981-04-05	1.2	95
97	1981-04-06	1.2	96
98	1981-04-07	1.2	97
99	1981-04-08	1.2	98
100	1981-04-09	1.2	99
101	1981-04-10	1.7	100
102	1981-04-11	1.7	101
103	1981-04-12	2.3	102
104	1981-04-13	4.9	103
105	1981-04-14	4.9	104
106	1981-04-15	4.9	105
107	1981-04-16	6.5	106
108	1981-04-17	9.8	107
109	1981-04-18	13.9	108
110	1981-04-19	18.9	109
111	1981-04-20	18.9	110
112	1981-04-21	18.9	111
113	1981-04-22	18.9	112
114	1981-04-23	18.9	113
115	1981-04-24	19.6	114
116	1981-04-25	24.9	115
117	1981-04-26	31.9	116
118	1981-04-27	38.7	117
119	1981-04-28	42.4	118
120	1981-04-29	46.6	119
121	1981-04-30	49.9	120
122	1981-05-01	51.5	121
123	1981-05-02	58.5	122
124	1981-05-03	66.6	123
125	1981-05-04	66.6	124
126	1981-05-05	66.6	125
127	1981-05-06	66.9	126
128	1981-05-07	66.9	127
129	1981-05-08	66.9	128
130	1981-05-09	66.9	129
131	1981-05-10	66.9	130
132	1981-05-11	67.3	131
133	1981-05-12	67.3	132
134	1981-05-13	67.3	133
135	1981-05-14	67.3	134
136	1981-05-15	68.9	135
137	1981-05-16	72.4	136
138	1981-05-17	72.4	137
139	1981-05-18	72.4	138
140	1981-05-19	72.4	139
141	1981-05-20	72.4	140
142	1981-05-21	74.7	141
143	1981-05-22	76.8	142
144	1981-05-23	79.7	143
145	1981-05-24	79.7	144
146	1981-05-25	81.3	145
147	1981-05-26	85.1	146
148	1981-05-27	91.6	147
149	1981-05-28	97.2	148
150	1981-05-29	102.6	149
151	1981-05-30	102.8	150
152	1981-05-31	108.5	151
153	1981-06-01	114.5	152
154	1981-06-02	119.8	153
155	1981-06-03	125.9	154
156	1981-06-04	133	155
157	1981-06-05	140.3	156
158	1981-06-06	146.4	157
159	1981-06-07	156.6	158
160	1981-06-08	166.6	159
161	1981-06-09	176.6	160
162	1981-06-10	182.6	161
163	1981-06-11	190.3	162
164	1981-06-12	199.9	163
165	1981-06-13	212.5	164
166	1981-06-14	218.3	165
167	1981-06-15	218.9	166
168	1981-06-16	218.9	167
169	1981-06-17	225.8	168
170	1981-06-18	229.4	169
171	1981-06-19	233.7	170
172	1981-06-20	242.1	171
173	1981-06-21	251.2	172
174	1981-06-22	257.4	173
175	1981-06-23	269.2	174
176	1981-06-24	282.2	175
177	1981-06-25	291.8	176
178	1981-06-26	302	177
179	1981-06-27	314.9	178
180	1981-06-28	328.4	179
181	1981-06-29	337.1	180
182	1981-06-30	344.7	181
183	1981-07-01	357.6	182
184	1981-07-02	369.9	183
185	1981-07-03	376.3	184
186	1981-07-04	385	185
187	1981-07-05	396.4	186
188	1981-07-06	407.7	187
189	1981-07-07	422.3	188
190	1981-07-08	435.9	189
191	1981-07-09	443.5	190
192	1981-07-10	455.5	191
193	1981-07-11	469.2	192
194	1981-07-12	486.1	193
195	1981-07-13	497.9	194
196	1981-07-14	509	195
197	1981-07-15	520.2	196
198	1981-07-16	532	197
199	1981-07-17	543.4	198
200	1981-07-18	553.8	199
201	1981-07-19	562.1	200
202	1981-07-20	571.1	201
203	1981-07-21	582.6	202
204	1981-07-22	594.7	203
205	1981-07-23	604.2	204
206	1981-07-24	615.9	205
207	1981-07-25	624.6	206
208	1981-07-26	630	207
209	1981-07-27	630.9	208
210	1981-07-28	636.7	209
211	1981-07-29	646.5	210
212	1981-07-30	657.7	211
213	1981-07-31	671	212
214	1981-08-01	682.8	213
215	1981-08-02	693.3	214
216	1981-08-03	706	215
217	1981-08-04	719	216
218	1981-08-05	731.7	217
219	1981-08-06	741.8	218
220	1981-08-07	748.9	219
221	1981-08-08	755	220
222	1981-08-09	762.4	221
223	1981-08-10	766.4	222
224	1981-08-11	770.6	223
225	1981-08-12	777.3	224
226	1981-08-13	785.4	225
227	1981-08-14	796.1	226
228	1981-08-15	805.2	227
229	1981-08-16	813.6	228
230	1981-08-17	820.7	229
231	1981-08-18	827.7	230
232	1981-08-19	835.7	231
233	1981-08-20	845.2	232
234	1981-08-21	855.7	233
235	1981-08-22	867.3	234
236	1981-08-23	874.9	235
237	1981-08-24	882	236
238	1981-08-25	890.4	237
239	1981-08-26	900.2	238
240	1981-08-27	906.2	239
241	1981-08-28	911.8	240
242	1981-08-29	918.5	241
243	1981-08-30	927.6	242
244	1981-08-31	938.6	243
245	1981-09-01	941.1	244
246	1981-09-02	947.2	245
247	1981-09-03	956.4	246
248	1981-09-04	961.4	247
249	1981-09-05	969.8	248
250	1981-09-06	979	249
251	1981-09-07	979.7	250
252	1981-09-08	982.9	251
253	1981-09-09	988.3	252
254	1981-09-10	996.9	253
255	1981-09-11	1006	254
256	1981-09-12	1013.1	255
257	1981-09-13	1021.9	256
258	1981-09-14	1031	257
259	1981-09-15	1037.9	258
260	1981-09-16	1039.7	259
261	1981-09-17	1039.7	260
262	1981-09-18	1040.5	261
263	1981-09-19	1045.9	262
264	1981-09-20	1054.3	263
265	1981-09-21	1058.5	264
266	1981-09-22	1063.7	265
267	1981-09-23	1067.3	266
268	1981-09-24	1072.3	267
269	1981-09-25	1077.6	268
270	1981-09-26	1081.7	269
271	1981-09-27	1081.7	270
272	1981-09-28	1085	271
273	1981-09-29	1092	272
274	1981-09-30	1098.8	273
275	1981-10-01	1098.8	274
276	1981-10-02	1099.4	275
277	1981-10-03	1105.1	276
278	1981-10-04	1106.7	277
279	1981-10-05	1108.7	278
280	1981-10-06	1108.7	279
281	1981-10-07	1109.2	280
282	1981-10-08	1114	281
283	1981-10-09	1114.9	282
284	1981-10-10	1114.9	283
285	1981-10-11	1118.7	284
286	1981-10-12	1122.3	285
287	1981-10-13	1122.3	286
288	1981-10-14	1122.3	287
289	1981-10-15	1122.3	288
290	1981-10-16	1122.3	289
291	1981-10-17	1122.3	290
292	1981-10-18	1122.3	291
293	1981-10-19	1122.3	292
294	1981-10-20	1124.6	293
295	1981-10-21	1124.6	294
296	1981-10-22	1124.6	295
297	1981-10-23	1124.6	296
298	1981-10-24	1124.6	297
299	1981-10-25	1124.6	298
300	1981-10-26	1124.6	299
301	1981-10-27	1124.6	300
302	1981-10-28	1124.6	301
303	1981-10-29	1125.7	302
304	1981-10-30	1126.6	303
305	1981-10-31	1126.6	304
306	1981-11-01	1126.6	305
307	1981-11-02	1126.6	306
308	1981-11-03	1126.6	307
309	1981-11-04	1126.6	308
310	1981-11-05	1126.6	309
311	1981-11-06	1126.6	310
312	1981-11-07	1126.6	311
313	1981-11-08	1126.6	312
314	1981-11-09	1126.6	313
315	1981-11-10	1126.6	314
316	1981-11-11	1126.6	315
317	1981-11-12	1126.6	316
318	1981-11-13	1126.6	317
319	1981-11-14	1126.6	318
320	1981-11-15	1126.6	319
321	1981-11-16	1126.6	320
322	1981-11-17	1126.6	321
323	1981-11-18	1126.6	322
324	1981-11-19	1126.6	323
325	1981-11-20	1126.6	324
326	1981-11-21	1126.6	325
327	1981-11-22	1126.6	326
328	1981-11-23	1126.6	327
329	1981-11-24	1126.6	328
330	1981-11-25	1126.6	329
331	1981-11-26	1126.6	330
332	1981-11-27	1126.6	331
333	1981-11-28	1126.6	332
334	1981-11-29	1126.6	333
335	1981-11-30	1126.6	334
336	1981-12-01	1126.6	335
337	1981-12-02	1126.6	336
338	1981-12-03	1126.6	337
339	1981-12-04	1126.6	338
340	1981-12-05	1126.6	339
341	1981-12-06	1126.6	340
342	1981-12-07	1126.6	341
343	1981-12-08	1126.6	342
344	1981-12-09	1126.6	343
345	1981-12-10	1126.6	344
346	1981-12-11	1126.6	345
347	1981-12-12	1126.6	346
348	1981-12-13	1126.6	347
349	1981-12-14	1126.6	348
350	1981-12-15	1126.6	349
351	1981-12-16	1126.6	350
352	1981-12-17	1126.6	351
353	1981-12-18	1126.6	352
354	1981-12-19	1126.6	353
355	1981-12-20	1126.6	354
356	1981-12-21	1126.6	355
357	1981-12-22	1126.6	356
358	1981-12-23	1126.6	357
359	1981-12-24	1126.6	358
360	1981-12-25	1126.6	359
361	1981-12-26	1126.6	360
362	1981-12-27	1126.6	361
363	1981-12-28	1126.6	362
364	1981-12-29	1126.6	363
365	1981-12-30	1126.6	364
366	1981-12-31	1126.6	365
367	1982-01-01	0	1
368	1982-01-02	0	2
369	1982-01-03	0	3
370	1982-01-04	0	4
371	1982-01-05	0	5
372	1982-01-06	0	6
373	1982-01-07	0	7
374	1982-01-08	0	8
375	1982-01-09	0	9
376	1982-01-10	0	10
377	1982-01-11	0	11
378	1982-01-12	0	12
379	1982-01-13	0	13
380	1982-01-14	0	14
381	1982-01-15	0	15
382	1982-01-16	0	16
383	1982-01-17	0	17
384	1982-01-18	0	18
385	1982-01-19	0	19
386	1982-01-20	0	20
387	1982-01-21	0	21
388	1982-01-22	0	22
389	1982-01-23	0	23
390	1982-01-24	0	24
391	1982-01-25	0	25
392	1982-01-26	0	26
393	1982-01-27	0	27
394	1982-01-28	0	28
395	1982-01-29	0	29
396	1982-01-30	0	30
397	1982-01-31	0	31
398	1982-02-01	0	32
399	1982-02-02	0	33
400	1982-02-03	0	34
401	1982-02-04	0	35
402	1982-02-05	0	36
403	1982-02-06	0	37
404	1982-02-07	0	38
405	1982-02-08	0	39
406	1982-02-09	0	40
407	1982-02-10	0	41
408	1982-02-11	0	42
409	1982-02-12	0	43
410	1982-02-13	0	44
411	1982-02-14	0	45
412	1982-02-15	0	46
413	1982-02-16	0	47
414	1982-02-17	0	48
415	1982-02-18	0	49
416	1982-02-19	0	50
417	1982-02-20	0	51
418	1982-02-21	0	52
419	1982-02-22	0	53
420	1982-02-23	0	54
421	1982-02-24	0	55
SAREC - SAREC 42.0917, -104.394

 

[Peter_SSs]

Still a bit vague to me but see if this in cell B2 of Summary, & copied down, is any use.
I have assumed that the values shown in columns B:C of the final mini-sheet are actually in columns J:K as in post #4
It will return 0 if there are no values >=150.

=MIN(FILTER('SAREC - SAREC 42.0917, -104.394'!K$2:K$15000,(YEAR('SAREC - SAREC 42.0917, -104.394'!A$2:A$15000)=A2)*('SAREC - SAREC 42.0917, -104.394'!J$2:J$15000>=150),0))

 

[ccalkins]

Still a bit vague to me but see if this in cell B2 of Summary, & copied down, is any use.
I have assumed that the values shown in columns B:C of the final mini-sheet are actually in columns J:K as in post #4
It will return 0 if there are no values >=150.

=MIN(FILTER('SAREC - SAREC 42.0917, -104.394'!K$2:K$15000,(YEAR('SAREC - SAREC 42.0917, -104.394'!A$2:A$15000)=A2)*('SAREC - SAREC 42.0917, -104.394'!J$2:J$15000>=150),0))

Peter,
That did it. Thank you so much!
Craig

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.