#DIV/0! Error when Averaging empty cells

Hi everyone, first time forum user here.
I have been stumped by this problem for a couple of weeks now and all the research/books I've read, don't help me.

The problem is:

I get A $DIV/0! result on cells that are empty, when trying to average the contents of those cells.
I need to average cells F5, F6, F7 (on sheet 1) without including a zero value, (if I input 0 into the cells I get rid of the error but my average is affected).

Some cells will be left blank/empty.
The result is calculated in F9 and then is transferred to sheet 2, cell C2.

This process then continues down through other similar groups on sheet 1 and then transferred to relevant cells on sheet 2.
How to overcome the $DIV/0% error in all instances and not affect the overall average?

Regards,
Martin

Try the following:-

=IF(ISERROR(AVERAGE(F5:F7)),0,AVERAGE(F5:F7))

Replace the 0 with "", if you want the result to also be blank. Note if you do set it to "", then further calculations on the results of the cell (F9) may give errors. To correct this make your cells that reference F9 as follows:-

=If(F9="",0,xxxxxx) where xxxxxx is your formula.

Regards

Gary

Thread closed duplicate post