Hi All, I am creating a backtesting spreadsheet and I am trying to work out a formula. I have a column [Column Q] that I can input (D, 4hr, 1hr) timeframes into. I am trying to work out for the "D" eg. Daily timeframe, what percentage of them were Wining trades. I also have a column [Column W] which is "Win/Loss Per Trade ($)". I have been trying quite a few different formulas without any luck. This is an example formula that isn't working but might give you an idea as to what I am trying to do. I only want it to count [Column Q] with "D" in it if that trade had a Win eg.[Column W] >0 & put into % form. Formula =(COUNTIF(Q$9:Q$1008,"D")/COUNTA(Q$9:Q$1008))+COUNTIF(W$9:W$1008,">0") . Would appreciate any help. Thanks
| Main - 2 target back - forward - live trading & testing spreadsheet.ods | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Q | R | S | T | U | V | W | |||
| 3 | Timeframe | ||||||||
| 4 | D | 4hr | 1hr | ||||||
| 5 | Win | Loss | Win | Loss | Win | Loss | Total $ gain | ||
| 6 | 60% | $13,062.50 | |||||||
| 7 | |||||||||
| 8 | Timeframe (D, 4hr, 1hr) | Win/Loss Per Trade ($) | |||||||
| 9 | D | $13,062.50 | |||||||
| 10 | D | $0.00 | |||||||
| 11 | 4hr | $0.00 | |||||||
| 12 | 4hr | $0.00 | |||||||
| 13 | D | $0.00 | |||||||
| 14 | $0.00 | ||||||||
| 15 | $0.00 | ||||||||
| 16 | $0.00 | ||||||||
| 17 | $0.00 | ||||||||
| 18 | $0.00 | ||||||||
| 19 | $0.00 | ||||||||
| 20 | $0.00 | ||||||||
| 21 | $0.00 | ||||||||
| 22 | $0.00 | ||||||||
| 23 | $0.00 | ||||||||
| 24 | $0.00 | ||||||||
| 25 | $0.00 | ||||||||
| 26 | $0.00 | ||||||||
| 27 | $0.00 | ||||||||
| 28 | $0.00 | ||||||||
| 29 | $0.00 | ||||||||
| 30 | $0.00 | ||||||||
| 31 | $0.00 | ||||||||
| 32 | $0.00 | ||||||||
| 33 | $0.00 | ||||||||
| 34 | $0.00 | ||||||||
MEWNIE_Strategy_(4hr_TF) | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q6 | Q6 | =(COUNTIF(Q$9:Q$1008,"D")/COUNTA(Q$9:Q$1008))*AND(COUNTIF(W$9:W$1008,">0")) |
| W6 | W6 | =SUM(W9:W1008) |
| W9:W34 | W9 | =J9+M9 |
