Finding an average of a list that could have 0 to 10 numbers in it

[Shu of AZ]

My list, see example below may have a number in each row or it may not. I
trying to write a formula that would average the numbers no matter how many
there were. Example if there were 4 cells filled each with a number, the
formula would divide the sum by 4. if there were 5, the formula would
recognize there are 5 and would divide the sum by 5 and so on. Thanks

List Header
0
0
0
51.71
0
52.79
0
50.56
0
0

????? This is the average ( which would be 51.68 in this case )of how ever
many actual numbers are in the list from 0 to 10 numbers possible. What
would be the formula?

 

[Pete_UK]

Try this array* formula:

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

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it
rather than the usual ENTER. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete

 

[Shu of AZ]

works like a champ. Thanks again

Try this array* formula:

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

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it
rather than the usual ENTER. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete

works like a champ. Thanks again