Calculate Weighted Average within a date range

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.

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.