calculating average with blank cells

Hi
I have 4 columns with an average function on the bottom, however not all the columns are used all the time, resulting in an error (#DIV/0!). How do I calculate the average only if the cells are used. If I don't used one of the columns I would like it to stay blank or return 0
thanks
marvin

enter following formula at the bottom of your columns instead of simple AVERAGE function.
suppose your data is in col A range(A1:A50), you should change this range for each column.

=IF(ISERROR(AVERAGE(A1:A50)),"",AVERAGE(A1:A50))

Originally Posted by marvinks

Hi
I have 4 columns with an average function on the bottom, however not all the columns are used all the time, resulting in an error (#DIV/0!). How do I calculate the average only if the cells are used. If I don't used one of the columns I would like it to stay blank or return 0
thanks
marvin

marvinks wrote:
> Hi
> I have 4 columns with an average function on the bottom, however not
> all the columns are used all the time, resulting in an error (#DIV/0!).
> How do I calculate the average only if the cells are used. If I don't
> used one of the columns I would like it to stay blank or return 0
> thanks
> marvin
>
>
> --
> marvinks

Hi marvinks

Try:

=IF(ISERR(AVERAGE(A1:A4)),0,AVERAGE(A1:A4))

Adjust ranges as necessary.

Regards

Steve

thanks everyone, I'm off to the races

Marvin