Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiplying data
Hi,
I have two lists of data. List 1 is a person's name, their age group, and a sum of money. List 2 is a person's name, their age group, and a number. I'd like to match up the person's name and age group in list 1 with the same person's name and age group in list 2, and multiply the corresponding numbers. list 1 (money saved) Jane 5-10 $100 Bob 8-12 $50 Bob 10-12 $150 Mary 8-10 $100 Michael 10-12 $75 list 2 (interest rate) Mary 8-10 .5 Bob 8-12 .7 Results: Mary 8-10 $50 ($100x.5) Bob 8-12 $35 ($50x.7) How would be the best way to do this on a very large dataset? Ideally, it would be good also if this could be done from one dataset. I can put both list 1 and list 2 in the same pivot table style dataset with the following field headings, for example: name age group measurement value Mary 8-10 money saved $75 Mary 8-10 interest rate .5 Bob 8-12 interest rate .7 Bob 8-12 money saved $50 Looking forward to your ideas. Marina *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiplying data
If the first list in in A1:C5 and the 2nd is in A10:C11, then you can enter
this formula in D10 & fill down: =SUMPRODUCT(($A$1:$A$5=A10)*($B$1:$B$5=B10)*($C$1: $C$5*C10)) Bob Umlas Excel MVP "marina madeleine" wrote in message ... Hi, I have two lists of data. List 1 is a person's name, their age group, and a sum of money. List 2 is a person's name, their age group, and a number. I'd like to match up the person's name and age group in list 1 with the same person's name and age group in list 2, and multiply the corresponding numbers. list 1 (money saved) Jane 5-10 $100 Bob 8-12 $50 Bob 10-12 $150 Mary 8-10 $100 Michael 10-12 $75 list 2 (interest rate) Mary 8-10 .5 Bob 8-12 .7 Results: Mary 8-10 $50 ($100x.5) Bob 8-12 $35 ($50x.7) How would be the best way to do this on a very large dataset? Ideally, it would be good also if this could be done from one dataset. I can put both list 1 and list 2 in the same pivot table style dataset with the following field headings, for example: name age group measurement value Mary 8-10 money saved $75 Mary 8-10 interest rate .5 Bob 8-12 interest rate .7 Bob 8-12 money saved $50 Looking forward to your ideas. Marina *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiplying data
Bob,
Thanks for the suggestion for the formula. Would that work even if the two are not matched up at all? For example: - if list 1 is 200 lines and list 2 is only about 20 lines, - the two lists are on the same worksheet in the columns you mentioned, but they are not lined up or matched up in any way by name or age group - not all people and age group in list 2 have a corresponding match in list 1 would that formula still work? I also must admit that the data is currently all on one worksheet in pivot table ready form, basically the second way I mentioned - a dataset with the following headings: name age group measurement value Mary 8-10 money saved $75 Mary 8-10 interest rate .5 Bob 8-12 interest rate .7 Bob 8-12 money saved $50 Are there some other approaches you may suggest as to how to multiply the data from this dataset? Thanks again. Marina *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiplying | Excel Worksheet Functions | |||
Multiplying | Excel Worksheet Functions | |||
Multiplying specific data within a range to get column totals | Excel Worksheet Functions | |||
Multiplying a column of data by a constant | Excel Discussion (Misc queries) | |||
Multiplying in a row | Excel Discussion (Misc queries) |