# Weighted Average with Sumproduct and Lookup?

#### Urgent Biz

##### New Member
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>

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

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.

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

I am always glad to be of any help
Sergio

Replies
7
Views
866
Replies
1
Views
613
L
Replies
7
Views
485
Legacy 428781
L
Replies
1
Views
506
Replies
14
Views
2K

1,202,917
Messages
6,052,548
Members
444,591
Latest member
fauxlidae

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back