# Weighted Average with Multiple Qualifying Criteria

1. ## Weighted Average with Multiple Qualifying Criteria

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 V R V Y V O V P F G F R F Y

P.S. Fruit and Vegetables are not actually what I'm analyzing, it just fits the example.  Register To Reply

2. ## Re: Weighted Average with Multiple Qualifying Criteria

For weighted averages, use a formula that is SUM/COUNT

=SUMPRODUCT(--(Type=V),--(Color=G),Rate,Amount)/SUMPRODUCT(--(Type=V),--(Color=G),Amount)

or use

=SUMIFS(...)/COUNTIFS(....)  Register To Reply

3. ## Re: Weighted Average with Multiple Qualifying Criteria

Great thank you so much! That worked like a charm!  Register To Reply

4. ## Re: Weighted Average with Multiple Qualifying Criteria

f1 = new value

Or F2=b2*e2 and drag down

After that a pivot table

row = type
value = new value  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 