+ Reply to Thread
Results 1 to 3 of 3

Weighted Average with (=sumproduct and =sumif)

  1. #1
    Registered User
    Join Date
    12-09-2013
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Weighted Average with (=sumproduct and =sumif)

    I have a group of 1000 records of survey responses that include ratings to an array of areas For each rating I want to show the total count of the records in the sample that responded with that rating, however then in then have an average column that incorporates the records weight (each record with the "Latest Decision" = "Withdrawl" representing 13.0176 and records with the "Latest Decision" = "IE" counting representing 4.2760 when drawing conclusions about the entire population since not all individuals completed the survey. I have various demographic categories that I wish to include as criteria in the average columns for each rating. You can get an idea of what I am trying to do as I have calculated for the average with an =averageif but cannot quite figure out the correct use of =sumproduct and =sumif to write a formula for each cell and the necessary criteria for the average column.

    Here is a small sample file.
    Example.xlsx
    Last edited by wagstaffjh; 09-22-2014 at 12:30 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Weighted Average with (=sumproduct and =sumif)

    You can use formulas to convert your values to the desired weighted values, and label the data as desired, then use a pivot table to find the averages for the groups. Here is an example: note that "Withdrawl" is actually spelled "Withdrawal"

    Example with Pivot Table.xlsx
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    12-09-2013
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Weighted Average with (=sumproduct and =sumif)

    Awesome, thanks.

+ 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] The Variable WEIGHTED AVERAGE and SUMPRODUCT PUZZLE
    By omega0010 in forum Excel General
    Replies: 22
    Last Post: 08-10-2014, 04:15 PM
  2. Weighted Average IF (sumproduct conditional)
    By Leopold2000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2013, 03:27 PM
  3. [SOLVED] SUMIF with weighted average
    By pansovic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2012, 06:29 PM
  4. [SOLVED] Help with IF and SUMPRODUCT to calculate weighted average??
    By consulttk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2012, 03:56 PM
  5. Dollar Weighted Average Sumproduct Formula:
    By pipsturbo in forum Excel General
    Replies: 6
    Last Post: 12-07-2009, 07:13 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