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!

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!

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!