EXCEL formula help please

[redlinezak]

I am trying to work out an average based upon how many cells are populated but struggling.

Say i have 12 columns A-L (lets say for all the 200's i have seen that month) and in column M i want to work out the monthly average. The formula is =SUM(A1:A12)/12. I want to change the 12 in that formula for the number of cells with data in it. So if i am only in May then it would divide A1:A12 by 5 and the number would automatically update each time a new months data was entered (Any cell without a number in would be blank rather than 0 or anything)
I am trying something like =SUM(A1:A12)/countif....... but i cannot get the formula correct.

Any help would be great.

Thanks

[zeppelin101]

If some cells if your range have no values in them, it will calculate the average from the cells which have values in, i.e. if only 5 cells are populated it will calculate sum(5 cells)/5, or if 10 cells are populated it will calculate sum(10 cells)/10.

Alternatively, use aggregate function.

Something like:

=AGGREGATE(1,7,A1:A12)

Where the 1 sets it to find the average of a range and 7 sets it to ignore hidden rows and error values.

The Aggregate function is awesome for calculating statistics of samples/populations which are likely to be riddled with error values or empty rows

[redlinezak]

Will look into that thanks. I have also managed to get something working using this formula after a bit more trial and error.
=SUM(A1:A12)/COUNTIF(A1:A12,">0").
The last thing i am trying to do now is remove all the #VALUE! and #DIV/0! errors but the formulas are getting way beyond me now.

[zeppelin101]

If you use the aggregate function, it will ignore any error values for you so you don't need to worry about those.

But, you can use IFERROR to turn any errors into blank cells. Formula is IFERROR(calculation,"") where the "" will make the cell appear empty if the calculation cannot be completed.

[redlinezak]

OK tried the aggregate function but not getting a result. were the numbers 1 and 7 the actual numbers i needed to enter?
My current 12 columns only have 1 number in at present and the aggregate formula returns the entry #NAME!.

Sorry for this but im very much still learning excel

[zeppelin101]

Can I see the sheet? Or at least a sample from it. I'll drop you a PM with my email address.

[redlinezak]

Just worked out that excel 2007 does not have the aggregate function.
On phone to IT to upgrade me to the 21st century software.

Thanks for your help

[zeppelin101]

Aye that'll do it!