Average Array Formula Help

[jls999]

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.

 

[AhoyNC]

Try:

[COLOR=#000000][FONT=Inconsolata]=IFERROR(ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]AVERAGE[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]Orders!$F:$F[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]C$1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]Orders!$F:$F[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]D$1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]/[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]86400[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]Orders!$F:$F[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]Orders!$E:$E[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]),"")[/FONT][/COLOR]

 

[jls999]

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))))