I need to calculate the weighted average with 2 criteria - date range and advisor (file is attached)
I need to calculate the weighted average with 2 criteria - date range and advisor (file is attached)
Hi -
I think there is some information missing here. Your download has two sheets called "calculate" and "Raw". The Raw data does not have any Advisor listed by name. Also, I'm not sure what you mean by a weighted average. Usually a weighted average means one piece of data multiplied by some factor then divide by the number of data points. However, you don't mention what you want to use for a weighting factor. Any clarification would be appreciated.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
=SUMPRODUCT(q1 score, q1 count)/SUM(q1 count)
Criteria = date range, advisor
Were trying to get the weighted average of each advisor
Hi -
I assume you want to use SUMPRODUCT because some of your referenced cells may be in separate files? SUMPRODUCT is the only function that can read closed files. Otherwise AVERAGEIFS would probably be the more efficient function. But, the SUMPRODUCT solution looks like this:
=IFERROR(SUMPRODUCT((Raw!$J$2:$J$2185=calculate!A4)*(Raw!$C$2:$C$2185>=calculate!$B$1)*(Raw!$C$2:$C$2185<=calculate!$C$1)*(Raw!$I$2:$I$2185))/SUMPRODUCT((Raw!$J$2:$J$2185=calculate!A4)*(Raw!$C$2:$C$2185>=calculate!$B$1)*(Raw!$C$2:$C$2185<=calculate!$C$1)),"")
First it checks the Advisor name, the next two tests are to collect anything greater than or equal to the start date, and anything less than or equal to the end date. Finally it multiplies the score of each match. The SUMPRODUCT in the denominator is the exact same formula except it doesn't multiply by the score - it's just a count of the number of matches. I noticed for different date ranges that some advisors don't have any data, which will generate an error. That's why I enclosed the whole formula within an IFERROR function that returns "" if there are not matches. "" is ignored in by your AVERAGE formula in cell B15. So if some advisors have no data, you will still get an average of the advisors that do.
I have attached your file with this formula in the table.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks