Conditional data selection and calculate count, max and means

[nostradamus]

Hi,

I have a bit of a complicated if then computational formulas request - not sure what all goes in this.
Background: I have daily records of Flow of water going through the system (col.B), and corresponding dates are in col.A. Col.C has difference in Flow from previous day (DeltaFlow). Col.D is the Flow Log10 transformed and Col.E is the difference from previous day.
Formula Requests:
J2 = Duration of Rise (Number of consecutive days where flow was greater than previous day (use longest streak; if two or more of same length, then use one with higher difference), 30 days up from selected date (match info found in col.F2 & col.G2)
J3 = Duration of Drop (Number of consecutive days where flow was lesser than previous day (use longest streak; if two or more of same length, then use one with higher difference), 30 days up from selected date (match info found in col.F3 & col.G3)
J4 = Magnitude of Rise (max positive flow change in consecutive days, 30 days up from selected date
J5 = Magnitude of Drop (max negative flow change in consecutive days, 30 days up from selected date
J6 = Rate of rise (Means of all positive difference bet. consecutive daily Log10 flow values (colE), 30 days up from selected date
J7 = Rate of drop (Means of all negative difference bet. consecutive daily Log10 flow values (colE), 30 days up from selected date
J8 = Number of flow reversals (count number of times flow values (from col.B) go from up to down and down to up)

CalculationSteps.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Flow	DeltaFlow	Log10 Flow	Log10 DeltaFlow		days	Selected Dates	Description	Results
2	2/7/2016	138		2.140			30	3/13/2016	Duration of Rise	8
3	2/8/2016	137	-1	2.137	-0.003		30	3/13/2016	Duration of Drop	11
4	2/9/2016	130	-7	2.114	-0.023		30	3/13/2016	Magnitude of Rise	85
5	2/10/2016	127	-3	2.104	-0.010		30	3/13/2016	Magnitude of Drop	12
6	2/11/2016	126	-1	2.100	-0.003		30	3/13/2016	Rate of rise	0.007
7	2/12/2016	127	1	2.104	0.003		30	3/13/2016	Rate of Drop	-0.004
8	2/13/2016	127	0	2.104	0.000		30	3/13/2016	Number of Flow reversals	7
9	2/14/2016	126	-1	2.100	-0.003
10	2/15/2016	126	0	2.100	0.000
11	2/16/2016	124	-2	2.093	-0.007
12	2/17/2016	124	0	2.093	0.000
13	2/18/2016	123	-1	2.090	-0.004
14	2/19/2016	123	0	2.090	0.000
15	2/20/2016	121	-2	2.083	-0.007
16	2/21/2016	123	2	2.090	0.007
17	2/22/2016	122	-1	2.086	-0.004
18	2/23/2016	145	23	2.161	0.075
19	2/24/2016	126	-19	2.100	-0.061
20	2/25/2016	133	7	2.124	0.023
21	2/26/2016	132	-1	2.121	-0.003
22	2/27/2016	131	-1	2.117	-0.003
23	2/28/2016	126	-5	2.100	-0.017
24	2/29/2016	124	-2	2.093	-0.007
25	3/1/2016	123	-1	2.090	-0.004
26	3/2/2016	120	-3	2.079	-0.011
27	3/3/2016	120	0	2.079	0.000
28	3/4/2016	115	-5	2.061	-0.018
29	3/5/2016	115	0	2.061	0.000
30	3/6/2016	114	-1	2.057	-0.004
31	3/7/2016	115	1	2.061	0.004
32	3/8/2016	117	2	2.068	0.007
33	3/9/2016	139	22	2.143	0.075
34	3/10/2016	149	10	2.173	0.030
35	3/11/2016	155	6	2.190	0.017
36	3/12/2016	164	9	2.215	0.025
37	3/13/2016	199	35	2.299	0.084
38	3/14/2016	194	-5	2.288	-0.011
39	3/15/2016	182	-12	2.260	-0.028
40	3/16/2016	173	-9	2.238	-0.022
Sheet1

Cell Formulas
Range	Formula
D2:D40	D2	=LOG10(B2)
C3:C40,E3:E40	C3	=B3-B2
J6	J6	=MEDIAN(IF(E9:E37>=0,E9:E37,""))
J7	J7	=MEDIAN(IF(E9:E37<=0,E9:E37,""))