Vlookup and Weighted Averages

[head2442]

Need some help here! Two part question here possibly. I am not very efficient with excel yet so please bear with me!
I hope the sample file link works, if not I attached an image where I combined sheet 1 and 2 into one to show an idea of what I am working with.

SAMPLE FILE

1. I need to find the weighted averages for multiple lines. I know to use the =SUMPRODUCT formula /SUM however, the issue I am having is, there are over 2,000 lines.
The only way I know how to do this is to enter the formula manually each time for each group of the same names. And that would take FOREVER to complete. I know there has to be a formula or way where it can find all the matching names, take the weights and rates and provide me the average weight! In the sample file I have more simplified than what I am working with, again I have a sheet with 2,000+ rows.
Is there an easy way to find the weighted average easily with a formula that can automatically find all the lines with the same name, then take the weight and rate and give me the weighted average?

2. If above is possible, then I could just simply create a pivot table and then vlookup the weighted averages. If not, then is there a way to vlookup the names, then provide me with the weighted average going off the weights and rates?


Thank you so much!

 

[Eric W]

Welcome to the MrExcel forum!

Maybe:

Book2
	A	B	C	D	E	F	G	H
1	Name	Weighted Average				Weight	Rate	Helper
2	James	377.5487			John	50	$ 45.00	$ 2,250.00
3	John	33.5122			Mark	24	$ 57.00	1368
4	Mark	62.81818			James	12	$ 45.00	540
5	Mary	33.23448			Redditor	256	$ 585.00	149760
6	Max	118.2921			Mary	234	$ 2.00	468
7	Redditor	174.7709			Max	22	$ 3.00	66
8					John	36	$ 36.00	1296
9					Mark	86	$ 69.00	5934
10					James	33	$ 5,211.00	171963
11					Redditor	679	$ 25.00	16975
12					Mary	35	$ 211.00	7385
13					Max	65	$ 158.00	10270
14					John	78	$ 25.00	1950
15					Mark	22	$ 45.00	990
16					James	458	$ 38.00	17404
17					Redditor	34	$ 77.00	2618
18					Mary	21	$ 85.00	1785
19					Max	2	$ 96.00	192
Sheet2

Cell Formulas
Range	Formula
A2:B7	A2	=LET(rng,$E$2:$H$19,n,SORT(UNIQUE(INDEX(rng,0,1))),CHOOSE({1,2},n,SUMIF(INDEX(rng,0,1),n,INDEX(rng,0,4))/SUMIF(INDEX(rng,0,1),n,INDEX(rng,0,2))))
H2:H19	H2	=F2:F19*G2:G19
Dynamic array formulas.

I added the helper column in column H. I could probably figure out how to include it in the formula, but it would be more complicated and computationally intensive. Hope this helps!