sumproduct

[Guest]

I have a list names, dollar amount and %.
I need to calculate the weighted average % for each name, and then rank them
in order. Would I use the sumproduct formula?

X 100 5.6%
Y 200 4.0%
Z 300 4.4%
X 400 5.0%
Z 500 6.0%
Z 1000 6.65%
X 2000 6.4%

 

[Bob Phillips]

Just add a new column to multiply the weight by the percentage, and then
rank that new column.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Maybe I didn't explain my question correctly.
I have a big table with a list of names, amount and %. The information will
change all the time and new names can be added. For example,

First Table
A B C

X $10 5.0%
Y $20 5.5%
Z $30 6.0%
X $40 6.5%
X $50 6.3%
Y $60 6.2%
Z $70 5.2%
C $25 3.5%
Etc…..

Now, I have another table that will group all this together.

Second Table
Name $
%
X =sumif(First Table!a1:a8,Second Table!A1,First Table!
????B1:B8) ?
Y =sumif(First Table!a1:a8,Second Table!A2,First Table!
???B1:B8) ?
Z =sumif(First Table!a1:a8,Second Table!A3,First Table!B1:B8)
?
C =sumif(First Table!a1:a8,Second Table!A4,First Table!B1:B8)
?

To calculate the $ total for X,Y, and Z, I am using the sumif function,
which works.
To calculate a weight average for X,Y,Z, I need a formula that will say,
perform the weighted average for X if (from the first table), if cell A1
(from second table) equals to X.
And I need to do the same for Y, Z, C.

I typically use the sumproduct function to find the weighted average of the
entire table, but here, I just want the weighted average of all the Xs, Yx,
etc..

 

[Bob Phillips]

=SUMPRODUCT(--('First Table'!$A$1:$A$8='Second Table'!A1),'First
Table'!$B$1:$B$8,'First Table'!$C$1:$C$8)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)