Viewing Thread:
"Microsoft Excel Average Formual Help Please"

I dont know if any of you guys will be able to help me, but you've helped before and its worth a try.

My problem is:

How do you work out the average of a range of cells, ten (A1:A10) with only certain cells with numbers in them.
It divides by ten where as I would only like to divide the actual cells with the numbers in.
E.g. Ten cells with 5,6,1,2,2 in five cells comes to an average of 3.2, but because it divides by 10, the average is incorrect.

Any help is well appreciated.

Cheers.

Im making an Invoice, when an order is made (of a possible ten), if only six items are ordered then the other four cells would have zeros in them, but I cell formatted them cells to appear blank (cell format, custom, 0;_0;;@).

So when I want to make an average of these ten cells (A1:A10) it takes every cell, including the blank cells. This was my problem.

After all your help, I finally worked it out in the late hours of yesterday. Cheers for all your help.

The formula I had to use was:

SUM(M39:M48)/COUNTIF(M39:M48,">0")


Cheers anyways guys.

Are these cells set as 'numbers' or 'general'?? If they are as numbers, I think they are defaulted to a 0 value.

Are the cells blank, or do they have a zero in them?

If they're blank, AVERAGE() should work, I've just checked and it works for me..

cjh wrote:
> Which version of Excel are you using??

Office 2000

What about =SUM(A1:A10)/5? Not really to hot on Excel but I suppose it's worth a try.

Which version of Excel are you using??

cjh wrote:
> Whooo Style! wrote: AVERAGE(A1:A10) will do it..

Thats right, as I'm sure
> Excel will ignore any cells which don't have a value in them. Even though it
> looks in 10 cells, if 5 are blank, it will skip them and divde all the added
> together values how many there actaully were.


Cheers guys for all your comments, but still, none work.

If there are empty cells it still divides by 10. This is my problem, I cant dividre by the number of cells being used (i.e. 5) as the number can change if other cells have numbers put in them. I need a formula or cell format that averages the number of cells that are actually in use out of the ten.

Cheers guys anyway.
If anyone has anymore guesses, please post them.
Cheers.

Whooo Style! wrote: AVERAGE(A1:A10) will do it..

Thats right, as I'm sure Excel will ignore any cells which don't have a value in them. Even though it looks in 10 cells, if 5 are blank, it will skip them and divde all the added together values how many there actaully were.

Actually he didn't, he said someone elses answer was wrong, so there..

And I've checked my answer, it works..

Grix, you have answered a question! You are now officially One Of Us. Here, have an initiation fish.