I have a sheet which keeps track of profit and loss. I also have a section far off to the right which tallies Total Win Equity, Peak Win Equity, Drawdown $ & Drawdown %
All of that works well when the sheet has the whole year showing, but often I wish to autofilter to look for profitable systems with various metric. Naturally this figures to the right falls over without having all cells available.
This is how they are calculated and I wonder how it could be handled so the sheet recalculates each time it is filtered.
1) Total Win Equity (CT) is simply a total of the cell above the current + the Profit or Loss (AD) in the row -
2) Peak Win Equity (CU) is the highest figure in Total Win Equity (CT) or Peak Win Equity (CU) -
3) Drawdown $ (CV) is Total (CT) Win Equity less Peak Win Equity (CU) -
4) Drawdown % is the Drawdown $ (CV) divided by Peak Win Equity (CU) -
I am fairly certain that 3 & 4 will work fine when filtered, as they only calculate using the same row.
2 could possibly be handled using SUBTOTAL(104, but I was concerned this may not work correctly as there is no guarantee that there will be consecutive rows after autofiltering and the code is
1 is also a worry for the same reasons as 2; no guarantee of consecutive rows after autofiltering
Any suggestions as to a foolproof way to have them work, regardless of what filtering is done?
cheers
All of that works well when the sheet has the whole year showing, but often I wish to autofilter to look for profitable systems with various metric. Naturally this figures to the right falls over without having all cells available.
This is how they are calculated and I wonder how it could be handled so the sheet recalculates each time it is filtered.
1) Total Win Equity (CT) is simply a total of the cell above the current + the Profit or Loss (AD) in the row -
=CT2+AD32) Peak Win Equity (CU) is the highest figure in Total Win Equity (CT) or Peak Win Equity (CU) -
=MAX(CT3,CU2)3) Drawdown $ (CV) is Total (CT) Win Equity less Peak Win Equity (CU) -
=IF(CT19<N(CU19),CT19-CU18,"")4) Drawdown % is the Drawdown $ (CV) divided by Peak Win Equity (CU) -
=IF(CV19<>"",-CV19/CU19,"")I am fairly certain that 3 & 4 will work fine when filtered, as they only calculate using the same row.
2 could possibly be handled using SUBTOTAL(104, but I was concerned this may not work correctly as there is no guarantee that there will be consecutive rows after autofiltering and the code is
=MAX(CT3,CU2)1 is also a worry for the same reasons as 2; no guarantee of consecutive rows after autofiltering
| 2021 Latest Results June 2021.xlsb | ||||||
|---|---|---|---|---|---|---|
| CT | CU | CV | CW | |||
| 17 | Total Win Equity | Peak Win Equity | Drawdown | Drawdown % | ||
| 18 | 10393.96 | 10393.96 | $ - | 0.00% | ||
| 19 | 10293.96 | 10393.96 | $ (100.00) | 0.96% | ||
| 20 | 10193.96 | 10393.96 | $ (200.00) | 1.92% | ||
| 21 | 10093.96 | 10393.96 | $ (300.00) | 2.89% | ||
| 22 | 11514.96 | 11514.96 | $ - | 0.00% | ||
| 23 | 11414.96 | 11514.96 | $ (100.00) | 0.87% | ||
| 24 | 11314.96 | 11514.96 | $ (200.00) | 1.74% | ||
| 25 | 11214.96 | 11514.96 | $ (300.00) | 2.61% | ||
rc-vdw-place-adapted-2021-01-01 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| CT18 | CT18 | =AD18+10000 |
| CU18 | CU18 | =AD18+10000 |
| CV18,CV20:CV25 | CV18 | =CT18-CU18 |
| CW18:CW25 | CW18 | =IF(CV18<>"",-CV18/CU18,"") |
| CT19:CT25 | CT19 | =CT18+AD19 |
| CU19:CU25 | CU19 | =MAX(CT19,CU18) |
| CV19 | CV19 | =IF(CT19<N(CU19),CT19-CU18,"") |
Any suggestions as to a foolproof way to have them work, regardless of what filtering is done?
cheers
Last edited:
