Forum Discussion
Does anyone know a formula to find the longest losing run in the database
Afternoon all,
I don’t know if anyone can help me? I have an excel spreadsheet with over 19000 results in a column. I have previously looked down individual systems to find the drawdown, but this is quite a lot of +/- to scan through. Does anyone know a formula to find the longest losing run in the database (COL C -TOTAL COLUMN)?
Thanks in anticipation Dave
ROW 1 is 16/08/2014 COLUMN C is TOTAL POINTS BOTTOM ROW CURRENTLY IS 19327
| DATE | POINTS | TOTAL |
| 16/08/2014 | 0.95 | 0.95 |
| 23/08/2014 | -1.00 | -0.05 |
| 23/08/2014 | 1.06 | 1.01 |
| 23/08/2014 | 1.89 | 2.89 |
| 23/08/2014 | -1.00 | 1.89 |
| 23/08/2014 | 0.92 | 2.81 |
| 24/08/2014 | -3.12 | -0.31 |
| 24/08/2014 | -1.00 | -1.31 |
| 29/08/2014 | -1.00 | -2.31 |
| 29/08/2014 | -1.00 | -3.31 |
| 30/08/2014 | -1.00 | -4.31 |
| 30/08/2014 | -3.33 | -7.63 |
| 30/08/2014 | 0.95 | -6.68 |
| 30/08/2014 | -1.00 | -7.68 |
| 30/08/2014 | -1.00 | -8.68 |
| 30/08/2014 | 0.95 | -7.73 |
| 31/08/2014 | -2.86 | -10.60 |
| 31/08/2014 | -1.00 | -11.60 |
| 31/08/2014 | 0.95 | -10.65 |
| 12/09/2014 | -1.00 | -11.65 |
| 12/09/2014 | -1.00 | -12.65 |
| 13/09/2014 | -1.00 | -13.65 |
| 13/09/2014 | -1.00 | -14.65 |
| 13/09/2014 | -1.00 | -15.65 |
| 13/09/2014 | -1.00 | -16.65 |
| 13/09/2014 | -2.61 | -19.25 |
| 13/09/2014 | 0.95 | -18.30 |
| 13/09/2014 | -1.00 | -19.30 |
| 13/09/2014 | -1.00 | -20.30 |
| 13/09/2014 | 1.40 | -18.90 |
| 14/09/2014 | 1.20 | -17.70 |
| 14/09/2014 | -1.00 | -18.70 |
| 14/09/2014 | 0.64 | -18.06 |
| 14/09/2014 | -1.00 | -19.06 |
| 14/09/2014 | 0.95 | -18.11 |
| 14/09/2014 | 0.95 | -17.16 |
| 15/09/2014 | -1.00 | -18.16 |
| 19/09/2014 | -1.00 | -19.16 |
| 20/09/2014 | -1.00 | -20.16 |
| 20/09/2014 | 3.45 | -16.71 |
| 20/09/2014 | 3.94 | -12.77 |
| 20/09/2014 | 1.79 | -10.98 |
| 20/09/2014 | 3.94 | -7.04 |
| 20/09/2014 | 0.95 | -6.09 |
| 20/09/2014 | 0.95 | -5.14 |
| 20/09/2014 | 0.92 | -4.22 |
| 20/09/2014 | 3.94 | -0.28 |
| 20/09/2014 | -1.00 | -1.28 |
| 20/09/2014 | -1.00 | -2.28 |
| 21/09/2014 | 4.43 | 2.16 |
| 21/09/2014 | -1.00 | 1.16 |
| 21/09/2014 | 1.79 | 2.95 |
| 21/09/2014 | 0.95 | 3.90 |
| 21/09/2014 | 1.79 | 5.69 |
| 23/09/2014 | -1.00 | 4.69 |
| 23/09/2014 | 0.59 | 5.27 |
| 23/09/2014 | -1.00 | 4.27 |
| 23/09/2014 | -1.00 | 3.27 |
| 23/09/2014 | -1.00 | 2.27 |
| 24/09/2014 | -1.00 | 1.27 |
| 24/09/2014 | -1.00 | 0.27 |
| 24/09/2014 | 0.81 | 1.08 |
| 24/09/2014 | 2.08 | 3.17 |
| 24/09/2014 | -1.00 | 2.17 |
| 24/09/2014 | 1.15 | 3.32 |
| 24/09/2014 | 1.99 | 5.31 |
| 24/09/2014 | 0.66 | 5.97 |
| 24/09/2014 | 0.95 | 6.92 |
| 25/09/2014 | 0.35 | 7.27 |
| 25/09/2014 | -3.64 | 3.64 |
| 26/09/2014 | -1.00 | 2.64 |
| 26/09/2014 | -1.00 | 1.64 |
| 27/09/2014 | -1.00 | 0.64 |
| 27/09/2014 | -1.00 | -0.36 |
| 27/09/2014 | 2.38 | 2.01 |
| 27/09/2014 | -1.00 | 1.01 |
| 27/09/2014 | -1.00 | 0.01 |
| 27/09/2014 | -1.00 | -0.99 |
| 27/09/2014 | -1.00 | -1.99 |
| 27/09/2014 | 2.38 | 0.39 |
| 27/09/2014 | 0.95 | 1.34 |
| 27/09/2014 | -1.00 | 0.34 |
| 27/09/2014 | -1.00 | -0.66 |
| 27/09/2014 | 2.38 | 1.72 |
| 27/09/2014 | 0.96 | 2.68 |
| 27/09/2014 | 0.52 | 3.19 |
Hello harwood66,
Assuming a loss equates to negative points and assuming your table starts in A1, then in D3 place the following:
=IF(C3<C2,D2+1,0)
Copy the formula down to the bottom of your list. Next, copy row D (Ctrl + C) and paste values (Ctrl + V, Ctrl, V). Finally, sort column D from largest to smallest. At the top you will see longest losing streak.
3 Replies
Hello harwood66,
Assuming a loss equates to negative points and assuming your table starts in A1, then in D3 place the following:
=IF(C3<C2,D2+1,0)
Copy the formula down to the bottom of your list. Next, copy row D (Ctrl + C) and paste values (Ctrl + V, Ctrl, V). Finally, sort column D from largest to smallest. At the top you will see longest losing streak.
