Add cells and divide total by entries

[mattjackson]

Hello.

Is there a way to do the following:

I have a 'data entry' section in cells A1:A10 (so 10 cells).

Sometimes I enter figures into all 10 cells, sometimes just a few.

I want cell A11 to add up all entries in A1:A10, and then divide the total by how many entries there were in the range.

Is that possible?

Thanks for any help

 

[Joe4]

The AVERAGE function already does that, and will ignore any blank entries.
So you should just be use the formula:
=AVERAGE(A1:A10)

 

[mattjackson]

The AVERAGE function already does that, and will ignore any blank entries.
So you should just be use the formula:
=AVERAGE(A1:A10)

Oh wow, didn't know about that. That's brilliant, thank you.

 

[Joe4]

You are welcome!

 

[mattjackson]

I've just added this into my spreadsheet, works great thanks.

1 query though - before there is any data entered into the range (i.e. while A1:A10 has no entries), the cell gives the message #DIV/0!. Is it possible to stop this and just had it be blank?

I tried (with my limited knowledge!) a workaround using conditional formatting - I created a rule that if the cell contained that exact text, the text was coloured white, i.e. so although it's there, it can't be seen, but this didn't work.

 

[Joe4]

Which version of Excel are you on?
If 2007 or later, you can use:

=IFERROR(AVERAGE(A1:A10),"")

or something like:

=IF(COUNT(A1:A10)>0,AVERAGE(A1:A10),"")