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]

What version of Excel are you using? You mention, "Max value of Column G for each range of stock prior to the qty being 0 in column F", which only occurs twice in your example. Yet you describe four outputs desired?

 

[hedgie]

What version of Excel are you using? You mention, "Max value of Column G for each range of stock prior to the qty being 0 in column F", which only occurs twice in your example. Yet you describe four outputs desired?

I am on 365 so I assume the latest. Sorry if I was confusing - I want the maximum value for each security for each period we held it. So for stock ABC that is 3750, however for stock DEF it shoudl be 5120 and then 1850. If quantity never reaches Zero that means we still hold it and I still want the max value.

 

[Fluff]

I am on 365

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’)

 

[KRice]

I'm still not clear about your 1st post. Is column F used at all? Take XYZ as an example. There is no value of 0 in column F for XYZ, so why is a result expected? Did you mean to examine only 0's in column G, and then for that particular stock, look back in time to find the maximum total cost associated with it (without crossing any prior 0)?

 

[hedgie]

F is used to track the current position as of a certain date. The purpose is to be able to determine when a position is closed (equal to 0) so that we can then start measuring again if there are additional purchases after that date.

 

[KRice]

Okay, so if there is no 0 in column F for XYZ that suggests the position is still open...so why would you expect a result of 50 in H7?

 

[hedgie]

The buy value for the purchase was 50 - we did sell 1/2 at a profit , but I want to see the total cost of the buys only

 

[KRice]

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.

You seem to be missing the point of my question. I've quoted above what you said in your first post. You mention that when the quantity is 0 in column F, that should trigger an output of the maximum in column G for that particular holding. You then give examples that are not consistent with that description. One of those examples involves holding XYZ: there is no 0 in column F for XYZ, so why would you expect an output of the maximum?

 

[hedgie]

Let me try to explain. The purpose it to know the maximum value we paid in total for a security during a time period in which we held or still hold the position. So is we only bought a stock multiple times and never sold it would be the maximum value in column G. If we bought in different lots and then sold it all then it would be the maximum value of G until the quantity in F is 0. If after selling it all we then purchase it back then I need to know the maximum of this new set of purchases.