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.

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.

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.

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.

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.

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.