I am trying to use this formula =IFERROR(AVERAGE(Y8:Y1007),"") in cell F5 in my Backtesting Spreadsheet but it doesn't seem to work. I am wanting it to give me the average of column Y to show me the average Risk/Reward Per Trade. It seems to not be working due to the #DIV/01 error in the other cells that is created from that formula. Just wandering if anyone has a work around for this or is my formula missing something?

Thanks

Krisco

Cell Formulas | ||
---|---|---|

Range | Formula | |

B5 | B5 | =IFERROR(AVERAGEIF(M8:M1007,">0")+AVERAGEIF(P8:P1007,">0"),"") |

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 | =IFERROR(AVERAGE(Y8:Y1007),"") |

G5 | G5 | =I5/(J5+I5) |

H5 | H5 | =I5+J5 |

I5 | I5 | =COUNTIF(S8:S1007,">0") |

J5 | J5 | =COUNTIF(S8:S1007,"<0") |

K5 | K5 | =COUNTIFS(B8:B1007,"Long",S8:S1007,">0",B8:B1007,"<>") |

L5 | L5 | =COUNTIFS(B8:B1007,"Short",S8:S1007,">0",B8:B1007,"<>") |

N5 | N5 | =100%-M5 |

P5,R5,T5 | P5 | =IFERROR(COUNTIFS($R8:$R1007,P3,$S8:$S1007,">0")/COUNTIF($R8:$R1007,P3),NA()) |

Q5,S5,U5 | Q5 | =1-P5 |

V5 | V5 | =SUM(S8:S1007) |

W5 | W5 | =V5/X$5 |

Z5 | Z5 | =MIN(U8:U1007) |

AA5 | AA5 | =MIN(W8:W1007) |

I8 | I8 | =(J8-H8)/J8 |

L8:L50 | L8 | =(K8-J8)*M$5 |

M8:M50 | M8 | =((K8-J8)/J8)*M$5 |

O8:O50 | O8 | =(N8-J8)*N$5 |

P8:P50 | P8 | =((N8-J8)/J8)*N$5 |

Q8 | Q8 | =(X5*Y$5)/I8 |

S8,S12:S50 | S8 | =IF(AB8="S",(H8-J8))+L8+O8 |

T8 | T8 | =(X5+S8) |

U8:U50 | U8 | =(S8/X$5) |

V8:V50 | V8 | =(T8-X$5)/X$5 |

W8:W50 | W8 | =(T8/MAX($T$5:U8)-1) |

X8 | X8 | =IFERROR(X5*Y$5,"") |

Y8:Y50 | Y8 | =(AC8-J8)/(J8-H8) |

T9:T50 | T9 | =(T8+S9) |

X9:X50 | X9 | =IFERROR(T9*Y$5,"") |