Not quite the average problem with the average function

Hi, I'm averaging values (1,2,3 or 4) from 3 contiguous cells in a row; I'd like to be able to ignore blank cells and where all three are blank, to return zero. This is the problem as when all 3 cells are blank, using the simple function AVERAGE it returns the error message not divisible by zero (#DIV/0!).

I would then like to average the averages at the bottom of the column which i do with following:

I'm able to ignore zero values (see below)

=AVERAGE(IF(G6:G15<>0,G6:G15))

This ignores zero fine but when I have the error message it doesn't work of course.

I wondered if there was a way of ignoring both blank and zero when using the average function.

I'm getting all mixed up going around and around in circles solving one part of the problem but not the other.

Hi Barry,

Excel should not be including true blanks in your averages so that shouldn't be a problem. With your initial averages, I'd suggest

Please Login or Register  to view this content.

In place of the double quotes you could also use text (e.g. "No Values") which, as text, also would not be averaged.
Your Average of the Averages formula should then work. Let us know if there is more to this problem than I assumed.

ChemistB

Hi to you both - first of all thanks for th espeedy reply but most of all for the solutions, both of which work; so now I have a new problem - which one shall I use.

Many thanks - I can't believe how many hours I've worked on this. I feel very humble.

Barry