+ Reply to Thread
Results 1 to 1 of 1

weighted average with multiple weighted factors using SUMPRODUCT

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    weighted average with multiple weighted factors using SUMPRODUCT

    Hi all,

    Can't seem to figure this out currently. I have several data tables with analytics for each country and one total for Asia. I am trying to calculate the KPIs for Asia at a total level without calculating them for each item first. Basically, I want the output of the GREEN cells, without using the subtotal per item. I tried using a SUMPRODUCT formula (BLUE cells) to get a weighted average, but for some of the KPIs that obviously won't work since they depend on several factors.

    e.g.

    FP ROS depends on BUY UNT, FP ST%, DOOR COUNT and FP WOS, where FP WOS is the same for all countries. My current formula only 'weighs' it by using BUY UNT and neglects the rest. Thus the results are different. How could I include the other factors as well to make the calculation more accurate?
    Attached Files Attached Files

+ 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] Using Sumproduct to calculate weighted average
    By Excel15 in forum Excel General
    Replies: 3
    Last Post: 03-11-2015, 10:33 AM
  2. [SOLVED] Weighted Average Sumproduct Multiple Criteria with Data in One Column
    By azaremb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-11-2014, 03:14 PM
  3. sumproduct function for weighted average
    By copperdog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2014, 10:18 PM
  4. Weighted Average IF (sumproduct conditional)
    By Leopold2000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2013, 03:27 PM
  5. [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
  6. SUMPRODUCT Issue - Need to calculate weighted average of multiple ranges
    By arcobalt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2012, 04:55 PM
  7. Replies: 6
    Last Post: 10-30-2012, 02:35 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