I need a formula that will take an average RATE weighted from AMOUNT specific to each COLOR of each TYPE. Exhibit 1 below is an example of the kind of data. All the user needs to do is input the product code and the TYPE, COLOR and RATE are populated using vlookup formulas. Exhibit 2 is where I would put the WEIGHTED AVERAGE formulas for each COLOR of each TYPE. I tried (using Green Vegetables for example): =SUMPRODUCT(--(Type=V),--(Color=G),Rate,Amount) but this gives a very small percentage clearly not reflective of the actual weighted average. The reason I tried that is because =SUMPRODUCT(--(Type=V),Rate,Amount) gives an accurate weighted average for all vegetables.
Exhibit 1:
Product Code Amount Type Color Rate Corn 2 V Y 2.07% Apple 2 F R 1.93% Lemon 6 F Y 2.04% Banana 4 F Y 1.77% Cherry 8 F R 2.49% Celery 2 V G 1.67% Tomato 3 V R 1.85% Carrot 4 V O 2.16% Squash 1 V O 1.53% Avacado 2 F G 1.05% Brocoli 1 V G 1.02% Raspberry 10 F R 2.58% Asparagus 6 V G 1.71% Red Onion 2 V R 2.20% Pomegranate 1 F R 2.63% Red Cabbage 1 V R 1.59% Green Grapes 12 F G 1.88% Yellow Pepper 3 V Y 1.34% Eggplant 1 V P 1.54%
Exhibit 2:
Type Color Weighted Average V G <Formula> V R <Formula> V Y <Formula> V O <Formula> V P <Formula> F G <Formula> F R <Formula> F Y <Formula>
P.S. Fruit and Vegetables are not actually what I'm analyzing, it just fits the example.
Bookmarks