Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
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% |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
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) "Confused" wrote in message ... 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% |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
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" wrote: 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) "Confused" wrote in message ... 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% |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
=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) "Confused" wrote in message ... 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" wrote: 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) "Confused" wrote in message ... 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% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |