Weighted Average with Sumproduct and Lookup?

[Urgent Biz]

Hi–I think my problem could be solved by a combination of lookup and sumproduct but I cannot figure it out. I have a group of different omelettes and a few of those omelettes roll up to a more general group (i.e., NY, PA, and NC Omelettes roll into East Coast). I need to do a weighted average of NY/PA/NC Omelettes for East Coast. I need the formula to first look into the Level column C. If 1, find the price in the data sheet not shown here. If 2, go to column A and find OMEL in this case, find all the rows that have OMEL (however many rows) in the Code column D, and do a sumproduct with the Category Mix % and Avg Price for those rows and put the weighted average in the cell. If there is a way to make the formula not circular, that would be great. If there are other ways that would be wonderful as well. Thanks so much!

	A	B	C	D	E	F
1	Item #	Omelettes	Level	Code	Category Mix	Avg Price
2	256	Colorado Omelette	1		25.0%	$6.80
3	378	LA Omelette	1		15.0%	$6.20
4	OMEL	East Coast Omelettes	2		30.0%	$0.00
5	123	NY Omelette	1	OMEL	60.0%	$4.50
6	124	PA Omelette	1	OMEL	15.0%	$6.70
7	125	NC Omelette	1	OMEL	25.0%	$3.90
8	657	Texas Omelette	1		10.0%	$8.60
9	864	Arizona Omelette	1		5.0%	$7.30
10	395	Ohio Omelette	1		15.0%	$5.50

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sergioMabres]

Try with
=IF(D2=1,VLOOKUP(A2,price,2,0),IF(D2=2,SUMPRODUCT(--($E$2:$E$10=A2),$F$2:$F$10*$G$2:$G$10),""))
Where "price" is the range with prices that you omitted for level 1 lines
I hope this helps
Sergio

 

[Urgent Biz]

Try with
=IF(D2=1,VLOOKUP(A2,price,2,0),IF(D2=2,SUMPRODUCT(--($E$2:$E$10=A2),$F$2:$F$10*$G$2:$G$10),""))
Where "price" is the range with prices that you omitted for level 1 lines
I hope this helps
Sergio

Sergio, thanks very much for your response and it works like a charm! However, it did encounter the circular reference issue like I suspected in Column F since it is doing the sumproduct in the same column, but I was able to get around that by creating another column that looks up the price only, and that way the sumproduct can operate independently.

Thanks again.

 

[Aladin Akyurek]

You can replace the star with a comma in the formula.

 

[sergioMabres]

I am always glad to be of any help
Sergio