Getting an average from a range

[Supersmithy]

Hi,

Quite a simple one and I'm sure there must be a simple solution.

I'm trying to do the following;

=AVERAGE(G7/E7,G8/E8,G9/E9,G10/E10,G11/E11,G12/E12,G13/E13)

This works fine when all the cells have numbers in them from which to average, but on occasion any number of the cells can be empty and this returns a DIV, divide by empty error.

How can I overcome this and get just an average from the cell pairings that have numbers in them? I don't want to constantly adjust the range if I can help it.

Thanks

 

[hurgadion]

Hi,
try the array formula (You have to accept it Ctrl+Shift+Enter):

=AVERAGE(IF(ISERROR(G7:G13/H7:H13);"";G7:G13/H7:H13))

Best regards.

 

[Supersmithy]

Fab, got it working a charm.

Thanks