Finding max value in dynamic range

[hedgie]

Here is what I have data wise - Note that I am fudging Column G formulas, that is not the issue. What I want to do is find the Max value of Column G for each range of stock prior to the qty being 0 in column F. So in other words in H2 I would want to see 3735. In h7 I want to see 50, in h9 = 5120 and then in h14 =1850. I would like the other cells in column H to remain blank. Then I can sort to find the max cost for each security during the time period we held it. Any thoughts?

Book1
	A	B	C	D	E	F	G
1	Date	Name	Side	QTY	Value	Total Shares	Total Cost
2	3/1/2021	ABC	B	100	250	100	250
3	4/2/2021	ABC	B	500	1010	600	1260
4	5/1/2021	ABC	B	1000	2100	1600	3360
5	5/15/2021	ABC	B	200	375	1800	3735
6	6/22/2021	ABC	S	1800	3600	0	0
7	5/1/2021	XYZ	B	200	50	200	50
8	4/1/2022	XYZ	S	100	75	100	0
9	3/1/2021	DEF	B	100	1000	100	1000
10	3/15/2021	DEF	B	200	2020	300	3020
11	3/21/2021	DEF	S	100	950	200	0
12	4/18/2021	DEF	B	200	2100	400	5120
13	12/15/2021	DEF	S	400	4100	0	0
14	4/7/2021	DEF	B	200	1850	200	1850
Sheet1

Cell Formulas
Range	Formula
G2:G11,G13:G14	G2	=IF(AND(C2="B",B2=B1),G1+E2,IF(C2="B",E2,0))
F2:F14	F2	=IF(B2=B1,F1+IF(C2="B",D2,-D2),D2)

 

[KRice]

I would consider using a helper column for this...or using VBA. In this example, I've added a helper column (col. I) that contains the logic used to indicate subgroups whose maxima are to be determined. The data table is assumed to be sorted such that each type of stock is grouped together, and then within each stock grouping, the transactions are in ascending data order. Then the subgroups shown in the helper column are defined by either a change in stock or when a 0 is encountered in column F. Those subgroups can be easily examined to confirm that they make sense. Then in column H, the FILTER function is used in conjunction with the helper column I subgroup to extract the Total Cost array for that subgroup and the MAX is taken. Regarding the point mentioned about assumptions, should the transaction in yellow be situated between the transactions in orange? A quick note about the helper column: in this case, it cannot be labeled in I1 as that will produce an error, so I1 should be left blank...the column can, however, be hidden.

MrExcel_20220426.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Name	Side	QTY	Value	Total Shares	Total Cost	Max
2	3/1/2021	ABC	B	100	250	100	250	3735	1
3	4/2/2021	ABC	B	500	1010	600	1260		1
4	5/1/2021	ABC	B	1000	2100	1600	3360		1
5	5/15/2021	ABC	B	200	375	1800	3735		1
6	6/22/2021	ABC	S	1800	3600	0	0		1
7	5/1/2021	XYZ	B	200	50	200	50	50	2
8	4/1/2022	XYZ	S	100	75	100	0		2
9	3/1/2021	DEF	B	100	1000	100	1000	5120	3
10	3/15/2021	DEF	B	200	2020	300	3020		3
11	3/21/2021	DEF	S	100	950	200	0		3
12	4/18/2021	DEF	B	200	2100	400	5120		3
13	12/15/2021	DEF	S	400	4100	0	0		3
14	4/7/2021	DEF	B	200	1850	200	1850	1850	4
Sheet5 (2)

Cell Formulas
Range	Formula
H2:H14	H2	=IF($I2<>$I1,MAX(FILTER($G$2:$G$14,$I$2:$I$14=$I2)),"")
I2:I14	I2	=IF(OR(F1=0,B2<>B1),I1+1,I1)
G2:G11,G13:G14	G2	=IF(AND(C2="B",B2=B1),G1+E2,IF(C2="B",E2,0))
F2:F14	F2	=IF(B2=B1,F1+IF(C2="B",D2,-D2),D2)