# Thread: Average Array Formula Help Thanks: 0 Likes: 0

1. ## Average Array Formula Help

If the dates in column F is >= the date in cell C1 AND <= the date in cell D1, then show the average of column F minus column E. However, when there are no dates that fall within the range of cell C1 and D1, it gives me a #DIV/O error. How can get rid of this error?

=ArrayFormula(AVERAGE(IF((Orders!\$F:\$F>=C\$1)*(Orders!\$F:\$F<=(D\$1-(1/86400))),Orders!\$F:\$F-Orders!\$E:\$E)))

Thank you.

2. ## Re: Average Array Formula Help

Try:
Code:
`=IFERROR(ArrayFormula(AVERAGE(IF((Orders!\$F:\$F>=C\$1)*(Orders!\$F:\$F<=(D\$1-(1/86400))),Orders!\$F:\$F-Orders!\$E:\$E))),"")`

3. ## Re: Average Array Formula Help

Thank you. It looks like it works but I should have provided the whole formula. I am trying to calculate the formula on a number of columns and then taking the average of all of them. When I try to use your formula on this complete formula, it doesn't seem to be working. Here is my whole formula:

=average(ArrayFormula(AVERAGE(IF((Orders!\$F:\$F>=C\$1)*(Orders!\$F:\$F<=(D\$1-(1/86400))),Orders!\$F:\$F-Orders!\$E:\$E))),ArrayFormula(AVERAGE(IF((Orders!\$X:\$X>=C\$1)*(Orders!\$X:\$X<=(D\$1-(1/86400))),Orders!\$X:\$X-Orders!\$W:\$W))),ArrayFormula(AVERAGE(IF((Orders!\$AG:\$AG>=C\$1)*(Orders!\$AG:\$AG<=(D\$1-(1/86400))),Orders!\$AG:\$AG-Orders!\$AF:\$AF))),ArrayFormula(AVERAGE(IF((Orders!\$AP:\$AP>=C\$1)*(Orders!\$AP:\$AP<=(D\$1-(1/86400))),Orders!\$AP:\$AP-Orders!\$AO:\$AO))),ArrayFormula(AVERAGE(IF((Orders!\$AY:\$AY>=C\$1)*(Orders!\$AY:\$AY<=(D\$1-(1/86400))),Orders!\$AY:\$AY-Orders!\$AX:\$AX))),ArrayFormula(AVERAGE(IF((Orders!\$BH:\$BH>=C\$1)*(Orders!\$BH:\$BH<=(D\$1-(1/86400))),Orders!\$BH:\$BH-Orders!\$BG:\$BG))))