Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average the last 7 numbers on a list | Excel Worksheet Functions | |||
I need help finding an average | Excel Discussion (Misc queries) | |||
finding the median from a list of unsorted numbers | Excel Discussion (Misc queries) | |||
Finding a predetermined Sum from a List of Numbers | Excel Discussion (Misc queries) | |||
Finding the average time | Excel Worksheet Functions |