multiplying data

[marina madeleine]

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

 

[Bob Umlas]

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]

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 $50Are there some other approaches you may suggest as to how to multiply
the data from this dataset?

Thanks again.

Marina