# Weighted average in table with unique ID

1. ## 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. ## 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:
`Please Login or Register  to view this content.`
The formula used to display totals is:
Formula:
`Please Login or Register  to view this content.`
The formula used to display averages is:
Formula:
`Please Login or Register  to view this content.`
Let us know if you have any questions.

3. ## 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:

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

Thanks,
Arthur

6. ## 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:
`Please Login or Register  to view this content.`
The formula for totals and averages is:
Formula:
`Please Login or Register  to view this content.`
Let us know if you have any questions.

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

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