Hi Everyone,
I am just trying to work out this formula for my Backtesting Spreadsheet. I am wanting to work out the Total % of Wins (Long)[K5] & the Total % of Wins (Short)[L5]. One of the formulas that I have tried using for Total % of Wins (Short)[L5] is: =IF(V8:V50)">0",COUNTIF(B8:B50,"Short")/COUNTA(B8:B50). This formula keeps coming up with error messages. Any help would be appreciated. Thanks
I am just trying to work out this formula for my Backtesting Spreadsheet. I am wanting to work out the Total % of Wins (Long)[K5] & the Total % of Wins (Short)[L5]. One of the formulas that I have tried using for Total % of Wins (Short)[L5] is: =IF(V8:V50)">0",COUNTIF(B8:B50,"Short")/COUNTA(B8:B50). This formula keeps coming up with error messages. Any help would be appreciated. Thanks
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5 | B5 | =AVERAGE(M8:M1007)+AVERAGE(P8:P1007) |
| C5 | C5 | =AVERAGE(I8:I1007) |
| D5 | D5 | =COUNTIF(C8:C1007,"Bull")/COUNTA(C8:C1007) |
| E5 | E5 | =COUNTIF(C8:C1007,"Bear")/COUNTA(C8:C1007) |
| F5 | F5 | =B5/C5 |
| H5 | H5 | =I5+J5 |
| I5 | I5 | =COUNTIF(V8:V1007,">0") |
| J5 | J5 | =COUNTIF(V8:V1007,"<0") |
| K5 | K5 | =COUNTIF(B8:B1007,"Long")/COUNTA(B8:B1007) |
| L5 | L5 | =COUNTIF(B8:B1007,"Short")/COUNTA(B8:B1007) |
| N5 | N5 | =100%-M5 |
| P5,R5,T5 | P5 | =IFERROR(COUNTIFS($R8:$R1007,P3,$V8:$V1007,">0")/COUNTIF($R8:$R1007,P3),NA()) |
| Q5,S5,U5 | Q5 | =1-P5 |
| V5 | V5 | =SUM(V8:V1007) |
| I8 | I8 | =(J8-H8)/J8 |
| L8:L57 | L8 | =(K8-J8)*M$5 |
| M8:M57 | M8 | =((K8-J8)/J8)*M$5 |
| O8:O57 | O8 | =(N8-J8)*N$5 |
| P8:P57 | P8 | =((N8-J8)/J8)*N$5 |
| Q8 | Q8 | =(X5*Y$5)/I8 |
| V8:V57 | V8 | =IF(AC8="S",(H8-J8))+L8+O8 |
