Analyzing time-series of flux data and day-of-year identification

Add a formula ...

Row\Col	A	B	D	E
1			-4.352
2	Day of Year	NEE	Crossover
3	1	0.3502
4	1	0.3473	0	D4: = (B3<D$1) * (B4>D$1) + (B3>D$1) * (B4<D$1)
5	1	0.3436	0
6	1	0.3406	0
7	1	0.3397	0
8	1	0.3393	0
9	1	0.3404	0
10	1	0.3384	0
11	1	0.3377	0
12	1	0.3372	0
13	1	0.3351	0
14	1	0.3356	0
15	1	0.3328	0
16	1	0.3323	0
17	1	0.3411	0
18	1	0.3451	0

... and filter

Row\Col	A	B	D
1			-4.352
2	Day of Year	NEE	Crossover
5163	108	-4.526	1
5165	108	-1.697	1
5251	110	-4.497	1
5252	110	-3.483	1
5350	112	-5.142	1
5352	112	-3.941	1
5395	113	-4.364	1
5408	113	-4.073	1
5410	113	-5.649	1
5411	113	-2.892	1
5597	117	-4.544	1
5598	117	-4.123	1
5841	122	-5.691	1
5842	122	-3.822	1
5843	122	-4.648	1
5844	122	-3.264	1

From your graph, looks like that second point is not the first time the value rises above -4.352... but it's the last?


Day 108, step 25, is where the value is first below that value - but two rows down the value is -1.697 - but that's not where your second blue arrow is pointing.

Day 311, step 28, is the last time the value falls below the line - is that what you are trying to identify? Looks like it's up and down across line all the time - but past this point it oscillates above the line only.


Find first one with this function: {MIN(IF(D2:D17521<H1,ROW(D2:D17521),10^6))} - "array function" Confirm with cntrl+shift+enter

Find last dip with this function: {MAX(IF(D2:D17521<-4.352,ROW(D2:D17521),0))} - "array function" Confirm with cntrl+shift+enter


See attached.

Or, (I noticed the point you found was not the last time, but second to last)

Try this: (you see day 291 is where it was still happening a lot - 311 was an outlier)

example2.png

I used a couple of helper columns that returned TRUE or FALSE.
First helper. Enter in C2 and fill down:

Formula:

Please Login or Register  to view this content.

Second Helper. Enter in D2 and fill down:

Formula:

Please Login or Register  to view this content.

Day of the year:

Formula:

Please Login or Register  to view this content.

Cell with day of the year:

Formula:

Please Login or Register  to view this content.

RESULTS:

	F	G
33	Day	112
34	Cell	A5351