| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
average of group
i have a simple table where column A contains group #s and column B contains
values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
average of group
Assuming your table is in A1:B6, and A9 = the group you want to average, try:
=AVERAGE(IF((A1:A6=A9)*(B1:B6<""),B1:B6)) entered with Cntrl+Shift+Enter (or you'll get #VALUE!). "dkingston" wrote: i have a simple table where column A contains group #s and column B contains values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
average of group
=SUM(SUMIF(A1:A10,{1,3,7},B1:B10))/SUMPRODUCT((A1:A10={1,3,7})*(B1:B10<""))
would give the average for group numbers 1, 3, 7 in column A. -- Regards, Tom Ogilvy "dkingston" wrote: i have a simple table where column A contains group #s and column B contains values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
average of group
i couldn't get this to work. it seems to return the average of the entire
values column. i probably screwed it up tranlating it to the actual rows/columns of my table. thanks for your help. i was able to get the next post to work so my issue is resolved. thanks again. "JMB" wrote: Assuming your table is in A1:B6, and A9 = the group you want to average, try: =AVERAGE(IF((A1:A6=A9)*(B1:B6<""),B1:B6)) entered with Cntrl+Shift+Enter (or you'll get #VALUE!). "dkingston" wrote: i have a simple table where column A contains group #s and column B contains values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
average of group
this worked perfectly!
thanks very much. "Tom Ogilvy" wrote: =SUM(SUMIF(A1:A10,{1,3,7},B1:B10))/SUMPRODUCT((A1:A10={1,3,7})*(B1:B10<"")) would give the average for group numbers 1, 3, 7 in column A. -- Regards, Tom Ogilvy "dkingston" wrote: i have a simple table where column A contains group #s and column B contains values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
average of group
I thought "multiple groups" meant you want you wanted Group1 average, Group2
average, etc. After seeing Tom's interpretation, I guess you might want the average of Group1+Group2, etc. My formula would have to change to =AVERAGE(IF((A1:A6={"JB","SL"})*(B1:B6<""),B1:B6) ) where your data is in A1:B6 and "JB", "SL" were the groups I used to test. Still entered w/CSE. "JMB" wrote: Assuming your table is in A1:B6, and A9 = the group you want to average, try: =AVERAGE(IF((A1:A6=A9)*(B1:B6<""),B1:B6)) entered with Cntrl+Shift+Enter (or you'll get #VALUE!). "dkingston" wrote: i have a simple table where column A contains group #s and column B contains values. i need to find the average of multiple groups within the table and ignore rows where the value column is blank. the # of members within each group may change and i would prefer not to have to sort by group # if that's possible. thanks in advance for your time. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average of group! | Excel Worksheet Functions | |||
| average with out rounding up in a group of cells | Excel Worksheet Functions | |||
| How do I get the average of entries in a group of columns? | Excel Discussion (Misc queries) | |||
| Way to average a group of results from using LOOKUP? | Excel Worksheet Functions | |||
| Average a group, where grouping is Conditional on other col.??? | Excel Discussion (Misc queries) | |||
Linear Mode
