# Find if a value exists in another list and then calculate weighted average

1. ## Find if a value exists in another list and then calculate weighted average

Hello, I'm looking for help for my tricky formula. Thanks in advance.

There are two groups: Group 1 has two columns of ID and weight, and Group 2 has two columns of ID and score. I want to calculate the weighted average of the scores. So the first step is to check if the IDs in Group 1 exists in Group 2, and then do the weighted average calculation for the score.

To make it easy to explain, I attached the formula here. Thank you for your help.

2. ## Re: Find if a value exists in another list and then calculate weighted average

Tried from a few different directions but couldn't come up with anything nonVB that didn't include more than one step.

Here's a two-step solution.

vlookup product.xlsx

3. ## Re: Find if a value exists in another list and then calculate weighted average

Seeming an interesting but tricky one. Still waiting for someone to help

4. ## Re: Find if a value exists in another list and then calculate weighted average

Hi, daffodil11, I appreciate your trying to help me out.

Originally Posted by daffodil11
Tried from a few different directions but couldn't come up with anything nonVB that didn't include more than one step.

Here's a two-step solution.

Attachment 274544

5. ## Re: Find if a value exists in another list and then calculate weighted average

No problem. I hit a brick wall attempting to multiply the arrays against each other.

I was able to the denominator with a SUMPRODUCT but then couldn't go any further.

6. ## Re: Find if a value exists in another list and then calculate weighted average

Hi,

Managed to get it into one array formula (though it's hardly elegant!):

=SUM((SUBTOTAL(9,OFFSET(E2,SMALL(IF(ISNUMBER(MATCH(D2:D11,A2:A5,0)),ROW(D2:D11)-MIN(ROW(D2:D11)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(D2:D11,A2:A5,0))))))),,,))*N(OFFSET(B2,SMALL(IF(ISNUMBER(MATCH(A2:A5,D2:D11,0)),ROW(A2:A5)-MIN(ROW(A2:A5)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,D2:D11,0))))))),,,))))/SUM(SUBTOTAL(9,OFFSET(B2,SMALL(IF(ISNUMBER(MATCH(A2:A5,D2:D11,0)),ROW(A2:A5)-MIN(ROW(A2:A5)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,D2:D11,0))))))),,,)))

Regards

7. ## Re: Find if a value exists in another list and then calculate weighted average

Hi XOR LX, my thumb up and hat off for this advanced formula. Not easy to get this solved. I'm convinced that there is not an easy solution for this problem. So I may just take separate steps to do this. THANK YOU!

Originally Posted by XOR LX
Hi,

Managed to get it into one array formula (though it's hardly elegant!):

=SUM((SUBTOTAL(9,OFFSET(E2,SMALL(IF(ISNUMBER(MATCH(D2:D11,A2:A5,0)),ROW(D2:D11)-MIN(ROW(D2:D11)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(D2:D11,A2:A5,0))))))),,,))*N(OFFSET(B2,SMALL(IF(ISNUMBER(MATCH(A2:A5,D2:D11,0)),ROW(A2:A5)-MIN(ROW(A2:A5)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,D2:D11,0))))))),,,))))/SUM(SUBTOTAL(9,OFFSET(B2,SMALL(IF(ISNUMBER(MATCH(A2:A5,D2:D11,0)),ROW(A2:A5)-MIN(ROW(A2:A5)),""),ROW(INDIRECT("1:"&SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,D2:D11,0))))))),,,)))

Regards

8. ## Re: Find if a value exists in another list and then calculate weighted average

Don't blame you! And you're welcome!

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