excel formula question

dangerousmouse · 24 Jun 2009 at 14:51

im pretty sure i have done this at some point in the past, but cannot remember how!

what i want is simple and straight forward.
i have a column of data, but there are spaces in it. i am wanting to caltulate the average of all the cells containing data, and for this to not include the empty cells then the total is divided.

eg
a1:a6 would divide by 6, but if one if them is empty i wont get the true average.

hopefully this makes sense, and someone can help.

thanks
DM

div0 · 24 Jun 2009 at 14:56

=COUNTBLANK(a1:a6)

or

=COUNTA(a1:a6)

the first can be used to tell you how many cells in the column are blank, the second tells you how many in the column aren't blank.

xxalxx · 24 Jun 2009 at 15:03

=SUM(A:A)/COUNTA(A:A)

xxalxx · 24 Jun 2009 at 15:06

div0 said:
=COUNTBLANK(a1:a6)

or

=COUNTA(a1:a6)

the first can be used to tell you how many cells in the column are blank, the second tells you how many in the column aren't blank.

bah your brute is lvl10 ... got spanked

(Obviously not in the excel sense but in the Fight MyBrute sense div0)

Malakite · 24 Jun 2009 at 15:11

not sure if i have understood you right but just using =Average in excel wont count blank cells towards the total.

dangerousmouse · 24 Jun 2009 at 15:14

Cheers guys.

That's about covered it. I had used =average but wasn't 100% sure it it did/didn't count blank cells.