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.
Last edited by billj; 10-30-2013 at 12:07 PM.
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
Seeming an interesting but tricky one. Still waiting for someone to help
Hi, daffodil11, I appreciate your trying to help me out.
Originally Posted by daffodil11
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.
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
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!
Don't blame you! And you're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks