Formula to read highest fall in funds

[ddub25]

I have a column that displays a series of wins and losses (a portion of this is shown below). I have plotted my results using a Line Graph, but would like to know the biggest drop in funds of possibly over 1000 row entries. Is there a formula that is able read a series of numbers in order and be able to notice the biggest drawdown?

I can hover over my Line Graph and pick the points where I see the biggest drops and make a rough calc. myself, but this is tedious and inaccurate.

-14.00
115.00
101.50
163.00
-81.50
14.50
-56.00
7.00
29.50
-11.00
8.50

Dan

 

[MrKowz]

Based on the data you provided, what would you expect to have as the return?

 

[Aladin Akyurek]

Care to define what a drop means and what that value is for the sample you posted?

 

[ddub25]

Sorry, not explained very well.

I've added the Bankroll To Date column to the right, this adds each value from the first column keeping a running total. -14.00 + 115 = 101, 101 + 101.50 = 202.50 etc...

-14.00____-14.00
115.00___101.00
101.50___202.50
163.00___365.50
-81.50____284.00
14.50_____298.50
-56.00____242.50
7.00______249.50
29.50____279.00
-11.00____268.00
8.50_____276.50


In this example the biggest draw down (looking at the column to the right) was from 365.50 to 242.50, which was a drop of 123.

The formula needs to somehow assess each cell in the total column and look at the values in the rows below to find a number that is lower than itself and the lowest. It would need to find the biggest, negative difference to provide the biggest draw down experienced.

As I say, 123 was the biggest drop I found by manually looking on my Line Graph that I've outputted results to.

Dan

 

[Aladin Akyurek]

Sorry, not explained very well.

I've added the Bankroll To Date column to the right, this adds each value from the first column keeping a running total. -14.00 + 115 = 101, 101 + 101.50 = 202.50 etc...

-14.00____-14.00
115.00___101.00
101.50___202.50
163.00___365.50
-81.50____284.00
14.50_____298.50
-56.00____242.50
7.00______249.50
29.50____279.00
-11.00____268.00
8.50_____276.50


In this example the biggest draw down (looking at the column to the right) was from 365.50 to 242.50, which was a drop of 123.

The formula needs to somehow assess each cell in the total column and look at the values in the rows below to find a number that is lower than itself and the lowest. It would need to find the biggest, negative difference to provide the biggest draw down experienced.

As I say, 123 was the biggest drop I found by manually looking on my Line Graph that I've outputted results to.

Dan

Let A2:B12 house the data you posted.

D2: Largest Drop

E2, control+shift+enter, not just enter:

=MAX(MAX(B2:B12)-IF(ROW(B2:B12)-ROW(B2)+1 > 
  MATCH(MAX(B2:B12),B2:B12,0),B2:B12,9.99999999999999E+307))

This would yield the desired result of 123.

D3, At Value

E3, control+shift+enter, not just enter:

=INDEX(B2:B12,MATCH(E2,MAX(B2:B12)-IF(ROW(B2:B12)-ROW(B2)+1 > 
   MATCH(MAX(B2:B12),B2:B12,0),B2:B12,9.99999999999999E+307),0))

This reports 242.5 as the value of interest.

If so desired, these can be put into a single formula.

 

[MrKowz]

Aladin, there are times when you astound me, and there are times when you confuse me. This is one of those times where I am both astounded and confused. I wish I understood how you can come up with these things!

 

[ddub25]

Aladin,

Thanks. That is exactly what I want. I did a test with exact formula you gave and it worked perfectly, but when I try to adapt it to my full table it returns, "##################..."

The formula I have adapted yours to, is below:

=MAX(MAX(E28:E327)-IF(ROW(E28:E327)-ROW(E28)+1 >MATCH(MAX(E28:E327),E28:E327,0),E28:E327,9.99999999999999E+307))

I have used control+shift+enter to enclose with {}, but for some reason, it does not work.

I've also tried entering the formula in a different place in the worksheet obviously making sure I keep the same references, but on this occasion it returns -1E+308 (as if I had not put "{}" around it even though I had).

What am I doing wrong?

Dan

 

[Aladin Akyurek]

Aladin,

Thanks. That is exactly what I want. I did a test with exact formula you gave and it worked perfectly, but when I try to adapt it to my full table it returns, "##################..."

The formula I have adapted yours to, is below:

=MAX(MAX(E28:E327)-IF(ROW(E28:E327)-ROW(E28)+1 >MATCH(MAX(E28:E327),E28:E327,0),E28:E327,9.99999999999999E+307))

I have used control+shift+enter to enclose with {}, but for some reason, it does not work.

I've also tried entering the formula in a different place in the worksheet obviously making sure I keep the same references, but on this occasion it returns -1E+308 (as if I had not put "{}" around it even though I had).

What am I doing wrong?

Dan

Does E28:E327 have empty/blank records after the last running total?

 

[ddub25]

The last running total is in E327 and there aren't any empty/blank records within the range.

 

[Aladin Akyurek]

The last running total is in E327 and there aren't any empty/blank records within the range.

As it appears that the largest draw-down needs to be determined from the largest draw-downs each record downwards undergoes, an additional range is created...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64 align=right>-14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>-14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>-115</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>115</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>101</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-101.5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>101.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>202.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-40</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>163</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>365.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>-81.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>284</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>41.5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>14.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>298.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>56</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>-56</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>242.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-7</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>249.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-18.5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>29.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>279</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>268</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-8.5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>8.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>276.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-1E+308</TD></TR></TBODY></TABLE>

The data is in D28:D38. The next range, E28:E38, is a rolling sum
created with:

E28, just enter and copy down:

=SUM(E27,D28)

The largest drops must be calculated for each value (i) from i to any other value j after i.

The range in F28:F38 calculates the largest drop that occurs for each value in E28:E38...

F28, control+shift+enter, not just enter, and copy down:

=MAX(E28-IF(ROW(PL)-MIN(ROW(PL))+1>MATCH(E28,PL,0),PL,BigNum))

where PL stands for the range in E28:E38 and BigNum for 9.99999999999999E+307.

Finally,

=MAX(DrawDown)

where DrawDown stands for F28:F38, yields the desired result.