+ Reply to Thread
Results 1 to 6 of 6

Weighted average in table with unique ID

  1. #1
    Registered User
    Join Date
    02-21-2017
    Location
    Israel
    MS-Off Ver
    Office 365
    Posts
    5

    Weighted average in table with unique ID

    Hi,

    I have a table with unique identifier, and few columns (Job Run Length 1,2,..; Job Coverage 1,2,...).
    I need to create a new table, that merges all the rows by their unique ID, while summing the Job Run Lengths and presenting the weighted average for the Job Coverage.

    If it's too complicated to do so without coding, I would like to stay with same table, only with weighted average for the similar Job Id's Coverage.
    In the below example, the last 2 rows (in theory there might be more than 2) should either merge to one with Job RL1 total of 11,137, weighted Coverage1 according to (7,281*143+3,856*76)/(7,281+3,856) - same for the next 2 columns, or stay in the same 2 rows only with updated weighted average for Job Coverage 1 in both rows.

    Capture.PNG

    Thank you all

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    Re: Weighted average in table with unique ID

    Welcome to Excel Forum Arthurico.
    Attached is a sample file that shows how to get a list of unique ID's and uses simple numbers to show totals and averages. If you would like us to work with your actual figures, shown in the screen shot, please upload the spreadsheet itself by clicking on GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    The formula used to get unique ID's is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula used to display totals is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula used to display averages is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Weighted average in table with unique ID

    Welcome to the board! My thought was to create a second table that automatically updates and combines your duplicated values with the proper weighting. You can do so by using the following formula in A2 of your auto-table:

    =IFERROR(INDEX('Active Table'!$A$2:$A$6,MATCH(0,COUNTIF($A$1:$A1,'Active Table'!$A$2:$A$6),0)),"")

    This formula should be array-entered with Ctrl + Shift + Enter instead of Enter, then filled down. It will return only unique values. Then, in B2:

    =IF($A2="","",SUMIF('Active Table'!$A$2:$A$6,$A2,'Active Table'!B$2:B$6))

    And C2:

    =IFERROR(IF($A2="","",SUMPRODUCT('Active Table'!B$2:B$6,'Active Table'!C$2:C$6,--('Active Table'!$A$2:$A$6=$A2))/B2),0)

    These should be filled down and can be copied over to D2/E2 for Job RL 2 and so on for other jobs. The attachment should make things much clearer; give it a try, see if it looks plausible:
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,525

    Re: Weighted average in table with unique ID

    You also need to clarify if the merged cells are only for adjoining rows. I assume this is the case, but there is also a b1 higher up your data. The question you posed described the data as unique ids, hence the doubt

    Dav

  5. #5
    Registered User
    Join Date
    02-21-2017
    Location
    Israel
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Weighted average in table with unique ID

    Hi,

    First of all thank you for your support. The answer is no, the Unique ID's can return in multiple rows, not necessarily one after the other. Also the file size (length) is not fixed. It can once contain 200 rows, and the time after 300 rows, hence the formulas should cover the whole range.

    I've tried unsuccessfully to implement the attached above example, so according to the advice I'm attaching the file. The table on the left is the data table, and I also prepared an output table on the right.

    Please help,

    Thanks,
    Arthur
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    Re: Weighted average in table with unique ID

    The attached file populates the 2nd table by actually turning the original data set into a table, which can then be expanded, and changing the formulas in post #2 into structured (table) references as follows.
    The formula for unique ID's is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula for totals and averages is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    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. Default Weighted Average in Pivot Table
    By Robert305 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-07-2015, 10:28 AM
  2. Weighted Average in a Pivot Table
    By Maverick 3672 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-24-2013, 08:51 AM
  3. unique lists, sumproduct, weighted average
    By mattmuphy in forum Excel General
    Replies: 3
    Last Post: 04-23-2012, 04:00 PM
  4. [SOLVED] Weighted Average in Pivot Table
    By sinspawn56 in forum Excel General
    Replies: 2
    Last Post: 04-05-2012, 05:26 PM
  5. Weighted Average in a Pivot Table
    By rmikulas in forum Excel General
    Replies: 2
    Last Post: 07-14-2010, 05:08 PM
  6. Replies: 2
    Last Post: 12-15-2009, 10:13 AM
  7. [SOLVED] weighted average in pivot table
    By nasser in forum Excel General
    Replies: 3
    Last Post: 01-18-2006, 12:40 PM

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