Either/Or to help calculate peak equity

[honkin]

I have a sheet which keep track of P&L for horse racing and am just struggling to be able to accurately calculate what is known as Peak Equity. It is basically just the highest the bank has been

The formula I have is a simple one and it is located in column DA

=AGGREGATE(4,5,$CZ$21:CZ22)

This formula just gives me the Max of column CZ, which is calculating the current bank. Everything works fine except when the first row is a loser. The reason for this is the starting bank of 10,000, which is in CR4. The formula in CZ correctly adds that value to the current profit, so it is totalling profit and starting bank. The value in DA should never be below the starting bank, as it is showing max.

2022 Latest Results.xlsb
	CZ	DA	DB	DC
20	DRAWDOWN - LAY WIN
21	Total Win Equity	Peak Win Equity	Drawdown	Drawdown %
22	8,599.00	8,599.00	$ -	0.00%
23	8,697.00	8,697.00
24	8,795.00	8,795.00
25	8,893.00	8,893.00
26	8,991.00	8,991.00
27	9,089.00	9,089.00
28	9,187.00	9,187.00
29	9,285.00	9,285.00
30	9,383.00	9,383.00
31	9,481.00	9,481.00
32	9,579.00	9,579.00
33	9,677.00	9,677.00
34	9,644.00	9,677.00	$ (33.00)	0.34%
rc-vdw-place-adapted-2021-01-01

Cell Formulas
Range	Formula
CZ22:CZ34	CZ22	=AGGREGATE(9,5,$AF$21:AF22)+$CR$4
DA22:DA34	DA22	=AGGREGATE(4,5,$CZ$21:CZ22)
DB22	DB22	=CZ22-DA22
DC22:DC34	DC22	=IF(DB22<>"",-DB22/DA22,"")
DB23:DB34	DB23	=IF(CZ23<N(DA23),CZ23-DA22,"")

So how is it possible for that calculation in DA to have an either or statement, where if the value in CZ is >= CR4, then use that value, but if the value in CZ is below CR4, then use CR4

Does that make sense?

The bottom line is the value in DA can never be below the starting bank in CR4

Thanks in advance

 

[KRice]

Based on your description, I would suggest replacing the DA22 formula with:

=MAX(MAX(CZ$22:CZ22),$CR$4)

...and pull down. Note the internal MAX function considers the range beginning at $CZ$22, not $CZ$21 (which is a column heading). MAX in this case should work fine and is simpler to follow than AGGREGATE(4...
So the internal MAX function determines the maximum value achieved in the CZ column, and then that value is fed into another comparison with $CR$4 (starting bank) to return the maximum of those two values.

 

[honkin]

Based on your description, I would suggest replacing the DA22 formula with:

=MAX(MAX(CZ$22:CZ22),$CR$4)

...and pull down. Note the internal MAX function considers the range beginning at $CZ$22, not $CZ$21 (which is a column heading). MAX in this case should work fine and is simpler to follow than AGGREGATE(4...
So the internal MAX function determines the maximum value achieved in the CZ column, and then that value is fed into another comparison with $CR$4 (starting bank) to return the maximum of those two values.

cheers KRice and thanks for your reply

The downside of this is it needs to work on autofiltered data as well, which was why the original aggregate function in DA22 had 4,5 in it.

I have tried different combos of your formula, but using aggregate, but to no avail. Well, I can get a response using the following

=MAX(AGGREGATE(4,5,CZ$22:CZ22),$CR$4)

But the internal Max function will always be looking at all rows, which could end up with incorrect data in DA, if that makes sense. It certainly gives the response of 10,000 in DA22, which is correct, but I think the point is still valid.

Any thoughts on the same thing, but to work on filtered data?

 

[KRice]

Oh...that explains why AGGREGATE was used. Is your point really valid about autofiltering potentially causing incorrect results in DA if you go with

=MAX(AGGREGATE(4,5,CZ$22:CZ22),$CR$4)

I don't see an issue there. The internal AGGREGATE function is still working as it did originally and it returns just one value. The outer MAX function then compares AGGREGATE's max to $CR$4...so the MAX function is not looking at any filtered rows. It looks only at two values: one that ignores the filtered rows (due to AGGREGATE) and $CR$4. I haven't confirmed AGGREGATE will work that way with autofilter and the 4,5 options, but you may want to investigate if you are not certain.

 

[honkin]

Oh...that explains why AGGREGATE was used. Is your point really valid about autofiltering potentially causing incorrect results in DA if you go with

=MAX(AGGREGATE(4,5,CZ$22:CZ22),$CR$4)

I don't see an issue there. The internal AGGREGATE function is still working as it did originally and it returns just one value. The outer MAX function then compares AGGREGATE's max to $CR$4...so the MAX function is not looking at any filtered rows. It looks only at two values: one that ignores the filtered rows (due to AGGREGATE) and $CR$4. I haven't confirmed AGGREGATE will work that way with autofilter and the 4,5 options, but you may want to investigate if you are not certain.

Cheers KRice. Kind of makes sense. I thought it would be doing Max of the entire CZ column rather than just the Max of the result of the Aggregate function and $CR$4

Thanks for your help.

 

[KRice]

You're welcome. Post back if you encounter any surprises, but I think MAX(AGGREGATE should work fine.