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

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?

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

Try this array* formula:

=AVERAGE(IF(A1:A100,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

On Jan 19, 1:07*am, Shu of AZ
wrote:
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?

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

works like a champ. Thanks again

"Pete_UK" wrote:

Try this array* formula:

=AVERAGE(IF(A1:A100,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

On Jan 19, 1:07 am, Shu of AZ
wrote:
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?

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

Glad to hear it - thanks for feeding back.

Pete

"Shu of AZ" wrote in message
...
works like a champ. Thanks again

"Pete_UK" wrote:

Try this array* formula:

=AVERAGE(IF(A1:A100,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

On Jan 19, 1:07 am, Shu of AZ
wrote:
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?