Aggregate Max function not working correctly

[honkin]

I have a sheet which keeps a tally of profit, drawdown and the like and I am trying to do a simple calculation, but the answer seems way off.

In column CU, drawdown % is calculated. At present there are only 59,200 rows and row 22 is the first row of data.

This is the formula to work out the maximum drawdown. It is really just taking a maximum of the column

=AGGREGATE(4,5,$CU$22:CU300000)

The response showing is 1724.57%, yet when I examine the CU column, the largest number in there is only 711.71%. How is it possible that the Max function of Aggregate can get this so wrong? Even if I change the formula to reflect just to the last current row of data, 59200, the answer is the same and wrong.

The formula in CU is a simple one to calculate the drawdown % from the drawdown $ amount in CT and the peak $ amount in CS

=IF(CT22<>"",-CT23/CS23,"")

Here is a sample

2022 Latest Results.xlsb
	CR	CS	CT	CU
20	DRAWDOWN - WIN
21	Total Win Equity	Peak Win Equity	Drawdown	Drawdown %
22	11,372.98	11,372.98	$ -	0.00%
23	11,272.98	11,372.98	$ (100.00)	0.88%
24	11,172.98	11,372.98	$ (200.00)	1.76%
25	11,072.98	11,372.98	$ (300.00)	2.64%
26	10,972.98	11,372.98	$ (400.00)	3.52%
27	10,872.98	11,372.98	$ (500.00)	4.40%
28	10,772.98	11,372.98	$ (600.00)	5.28%
29	10,672.98	11,372.98	$ (700.00)	6.15%
30	10,572.98	11,372.98	$ (800.00)	7.03%
31	10,472.98	11,372.98	$ (900.00)	7.91%
32	10,372.98	11,372.98	$ (1,000.00)	8.79%
33	10,272.98	11,372.98	$ (1,100.00)	9.67%
34	10,305.32	11,372.98	$ (1,067.66)	9.39%
35	10,205.32	11,372.98	$ (1,167.66)	10.27%
36	10,105.32	11,372.98	$ (1,267.66)	11.15%
37	10,005.32	11,372.98	$ (1,367.66)	12.03%
38	9,905.32	11,372.98	$ (1,467.66)	12.90%
39	9,805.32	11,372.98	$ (1,567.66)	13.78%
40	9,705.32	11,372.98	$ (1,667.66)	14.66%
41	9,605.32	11,372.98	$ (1,767.66)	15.54%
42	9,505.32	11,372.98	$ (1,867.66)	16.42%
43	9,405.32	11,372.98	$ (1,967.66)	17.30%
44	9,305.32	11,372.98	$ (2,067.66)	18.18%
45	9,205.32	11,372.98	$ (2,167.66)	19.06%
46	9,105.32	11,372.98	$ (2,267.66)	19.94%
47	9,005.32	11,372.98	$ (2,367.66)	20.82%
48	8,905.32	11,372.98	$ (2,467.66)	21.70%
49	9,199.32	11,372.98	$ (2,173.66)	19.11%
50	9,099.32	11,372.98	$ (2,273.66)	19.99%
51	8,999.32	11,372.98	$ (2,373.66)	20.87%
52	8,899.32	11,372.98	$ (2,473.66)	21.75%
53	8,799.32	11,372.98	$ (2,573.66)	22.63%
54	8,699.32	11,372.98	$ (2,673.66)	23.51%
55	8,599.32	11,372.98	$ (2,773.66)	24.39%
56	8,657.14	11,372.98	$ (2,715.84)	23.88%
57	8,557.14	11,372.98	$ (2,815.84)	24.76%
58	8,457.14	11,372.98	$ (2,915.84)	25.64%
59	8,357.14	11,372.98	$ (3,015.84)	26.52%
60	8,257.14	11,372.98	$ (3,115.84)	27.40%
61	8,157.14	11,372.98	$ (3,215.84)	28.28%
62	8,057.14	11,372.98	$ (3,315.84)	29.16%
63	7,957.14	11,372.98	$ (3,415.84)	30.03%
64	7,857.14	11,372.98	$ (3,515.84)	30.91%
65	8,083.52	11,372.98	$ (3,289.46)	28.92%
66	7,983.52	11,372.98	$ (3,389.46)	29.80%
67	7,883.52	11,372.98	$ (3,489.46)	30.68%
68	7,783.52	11,372.98	$ (3,589.46)	31.56%
69	7,683.52	11,372.98	$ (3,689.46)	32.44%
70	7,958.90	11,372.98	$ (3,414.08)	30.02%
71	7,858.90	11,372.98	$ (3,514.08)	30.90%
72	7,758.90	11,372.98	$ (3,614.08)	31.78%
73	7,658.90	11,372.98	$ (3,714.08)	32.66%
74	7,558.90	11,372.98	$ (3,814.08)	33.54%
75	7,458.90	11,372.98	$ (3,914.08)	34.42%
76	7,592.18	11,372.98	$ (3,780.80)	33.24%
77	7,492.18	11,372.98	$ (3,880.80)	34.12%
78	7,392.18	11,372.98	$ (3,980.80)	35.00%
rc-vdw-place-adapted-2021-01-01

Cell Formulas
Range	Formula
CR22:CR78	CR22	=AGGREGATE(9,5,$AD$21:AD22)+$CR$4
CS22:CS78	CS22	=AGGREGATE(4,5,$CR$21:CR22)
CT22:CT78	CT22	=CR22-CS22
CU22:CU78	CU22	=IF(CT22<>"",-CT22/CS22,"")

 

[bebo021999]

Have you tried to test again with normal MAX function:
=MAX(CU22:CU3000)
Or
=AGGREGATE(14,6,$CU$22:CU300000,1)
?

 

[honkin]

cheers bebo021999

Both still give the same answer, yet it simply does not exist in that column, so am unsure as to why

 

[Peter_SSs]

yet it simply does not exist in that column,

I suspect that it does.

If you select the whole of column C by clicking its heading label, what shows as Max in the status bar?

Try your formula (or selection/status bar method) with cutting the range in half, then quarters, eighths etc to try to track down what row the large result is emanating from.

 

[honkin]

I suspect that it does.

If you select the whole of column C by clicking its heading label, what shows as Max in the status bar?
View attachment 68943

Try your formula (or selection/status bar method) with cutting the range in half, then quarters, eighths etc to try to track down what row the large result is emanating from.

Howdy Peter_SSs

Yeah, you ended up being right. To check initially, I was simply clicking the dropdown autofilter arrow on the column. For whatever reason, that only shows up to 711.71. Can say I've experienced it behaving like that before.

So I manually highlighted the start of column CU and went down in chunks until I could see all the entries above 711.71. There are hundreds of them, so not sure why they don't all show.

At least I know the formula is actually working correctly, as it was doing my head in.

cheers

 

[Peter_SSs]

For whatever reason, that only shows up to 711.71. Can say I've experienced it behaving like that before.

You have a large range so the behaviour is most likely related to this:

Limits to AutoFilter Dropdown Lists​

In Excel 2003, and earlier versions, an AutoFilter dropdown list will only show 1000 entries. As a result, in a large database, the AutoFilter dropdown may not show all the items in the column. (This limit was raised to 10,000 in Excel 2007.)

Note that although the drop-down list has a limit on how much shows when you click the drop-down, it does not stop you filtering for other values, it is just that you cannot click the other values in that list.