Max Min Difference from Range from previous Cell Reference

[nostradamus]

Hello all,

I need help with a couple of formulas based on information (cell range/reference) obtained from the cell above.

E2 identifies the number of consecutive days value increased (excluding 0s)
E4 need help with formula: from the identified range of consecutive days of increasing flow in col.B (result in E2), find the difference bet max and min values (you'll need to go one cell above the identified range to catch the starting point) - expected result in F4

E3 identifies the number of consecutive days value decreased (excluding 0s)
E5 need help with formula: from the identified range of consecutive days of decreasing flow in col.B (result in E3), find the difference bet max and min values (you'll need to go one cell above the identified range to catch the starting point) - expected result in F5

Book1.xlsx
	A	B	C	D	E	F
1	Date	Flow	DeltaFlow	Description	Results
2	10/30/2015	13900		Duration of Rise	3
3	10/31/2015	69900	56000	Duration of Drop	9
4	11/1/2015	69900	0	Magnitude of Rise	"please help"	1055
5	11/2/2015	40600	-29300	Magnitude of Drop	"please help"	2100
6	11/3/2015	5950	-34650
7	11/4/2015	3710	-2240
8	11/5/2015	2860	-850
9	11/6/2015	2790	-70
10	11/7/2015	2995	205
11	11/8/2015	2230	-765
12	11/9/2015	2230	0
13	11/10/2015	1700	-530
14	11/11/2015	1340	-360
15	11/12/2015	1220	-120
16	11/13/2015	1090	-130
17	11/14/2015	1020	-70
18	11/15/2015	962	-58
19	11/16/2015	895	-67
20	11/17/2015	1460	565
21	11/18/2015	1930	470
22	11/19/2015	1950	20
23
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MAX(FREQUENCY(IF(C3:C22>-0.99,ROW(C3:C22)),IF(C3:C22<=-0.99,ROW(C3:C22))))
E3	E3	=MAX(FREQUENCY(IF(C3:C22<0.99,ROW(C3:C22)),IF(C3:C22>=0.99,ROW(C3:C22))))
F4	F4	=B22-B19
F5	F5	=ABS(B19-B10)
C3:C22	C3	=B3-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[nostradamus]

just cross-posted the question in excelforum

"Max-Min difference" from cell range

# question is cross-posted in mrexcel forum # https://www.mrexcel.com/board/threads/max-min-difference-from-range-from-previous-cell-reference.1212573/ I need help with a couple of formulas based on information (cell range/reference) obtained from the cell above. E2 identifies the number...

www.excelforum.com