sumproduct

[Balan]

I want the weighted average of selected items in an array. The items should
be identifiable by a criteria. For example if column A5:A12 has characters
A,B,A,C,A,D,E,E and columns B5:B12 and C5:C12 have some values , I want the
weighted average of columns B and C using A as the criteria. How to do it ?

 

[bj]

I am not sure what you want
do you want the average of the value in B and C only for the rows which have
A in column A?
do you want the average of column B and column C separate or total?
you might try
=average(if(A5:A12="A",B5:B12)) entered as an array: control-shift-enter
and
=average(if(A5:A12="A",c5:c12)) also entered as an array

 

[squenson via OfficeKB.com]

Not sure this is what you need, but I have posted at
http://www.mediafire.com/?dfjwy5ngmxp a sample file that does what I have
understood from your description. If this is not what you need, let us know!

 

[Aladin Akyurek]

Assuming that:

F5: A

and B5:B12 are the weights...

=SUMPRODUCT(--(A5:A12=F5),B5:B12,C5:C12)/SUMIF(A5:A12,F5,B5:B12)

 

[Balan]

Thanks to all of you for the response. I am sorry, I used the word "Weighted
Average" which appears to have confused the issue. I have solved my problem
using "Sumproduct" function. Thanks once again.