weighted avg. of groups in table
 

i have a table with group #s in column A and values in column B.
i am using the formula below to calculate the average value (ignoring nulls)
of the relevant group on each row.
if i add a weighting in C1:C10 how do i calculate the weighted average of
each group?

=SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<""))

thanks in advance for your time.
DK

weighted avg. of groups in table
 

=SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10,B1:B10)/SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10)

--
Regards,
Tom Ogilvy


"dkingston" wrote:

i have a table with group #s in column A and values in column B.
i am using the formula below to calculate the average value (ignoring nulls)
of the relevant group on each row.
if i add a weighting in C1:C10 how do i calculate the weighted average of
each group?

=SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<""))

thanks in advance for your time.
DK

weighted avg. of groups in table
 

this worked perfectly.
thanks for this and for the earlier formula.
you are an excel rock-star!

"Tom Ogilvy" wrote:

=SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10,B1:B10)/SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10)

--
Regards,
Tom Ogilvy


"dkingston" wrote:

i have a table with group #s in column A and values in column B.
i am using the formula below to calculate the average value (ignoring nulls)
of the relevant group on each row.
if i add a weighting in C1:C10 how do i calculate the weighted average of
each group?

=SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<""))

thanks in advance for your time.
DK