+ Reply to Thread
Results 1 to 4 of 4

Weighted Average with Multiple Qualifying Criteria

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    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 <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.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,720

    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(....)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Weighted Average with Multiple Qualifying Criteria

    Great thank you so much! That worked like a charm!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Weighted Average with Two Criteria
    By nadrojylloh in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2014, 05:45 PM
  2. Weighted average w/ multiple text criteria excel 2010
    By mischge in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-31-2013, 11:34 AM
  3. [SOLVED] Calculate a weighted average in a single cell based on multiple criteria
    By _Bryan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2013, 04:38 PM
  4. Index and IF formula with multiple qualifying criteria
    By NadaNoodle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-04-2012, 08:53 AM
  5. Weighted Average with criteria
    By Larko in forum Excel General
    Replies: 2
    Last Post: 11-10-2010, 12:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1