#DIV/0! error for an array function

Hello,

I have a minor problem that is bugging me.

I have a formula that works fine; however, I get the #DIV/0! error when data does not fit the formula within my array. I'd rather the cell be blank, or show a zero, than show the error. The source of the error is the AVERAGE function, and Excel is trying to divide by a zero number. However, I cannot figure out how to write the formula so that the AVERAGE doesn't show an error result. I messed around with the ISNUMBER function, but I couldn't get it to work properly.

I attached a sample spreadsheet with the problem formula. There are a few #DIV/0! messages on the screen, but the problem is consistent across my calculations. If I can solve one of them, I can solve them all. I highlighted the cell I've been working on, in yellow. The other cells are values, but they behave similarly when populated with formulas. The yellow highlighted cell (E8) is my focus.

Thanks in advance for any advice or help!

Try it like this in E8:

=IF($P$3<$A7,"",IFERROR(AVERAGE(IF(('Data Sheet'!E$1:E$48>A7)*('Data Sheet'!E$1:E$48<=A8)*('Data Sheet'!$C$1:$C$48<>"Purchase")*('Data Sheet'!$D$1:$D$48<>"Correspondent"),'Data Sheet'!$F$1:$F$48)),""))

This is still an array formula, so you need to commit it using CSE. I've made the row ranges absolute, so you should be able to copy this elsewhere.

Hope this helps.

Pete

AliGW and Pete_UK --
Thank you so much for your help. I found my answer with your help!!! Have a great day.
Jeff