Weighted Average, Dynamic Range, Conditional Dashboard

Hello,

I am working on a fun little dashboarding project and am looking to the community for some advice. The drivers are simply a start date and an end date.

For example: 1/1/2014 - 3/31/2014

To keep it simple I am only looking at 4 fields in the referneced data set. They are Date, Net Calls, Average Handle Time and Agent.

So records look as follows:

DATE AGENT NET CALLS AVERAGE HANDLE TIME
1/1/2014 Agent 1 30 Net Calls 2.00 Minutes
1/1/2014 Agent 2 40 Net Calls 8.00 Minutes
1/2/2014 Agent 2 05 Net Calls 25.00 Minutes
...etc

I am stuck on the Average Handle Time problem as this needs to be weighted on net call volume contribution by agent conditional on dates being equal to or greater than the start date and less than or equal to the end date.

I have attached a workbook for reference. Thank you for any advice you can provide.

-miacg

I think I solved it. Anyone who can check the results to verify that would be helpful as well. I'd like to do it all in one formula without helper cells but this seems to work. Anyone with a better way please feel free to post but I will mark as [SOLVED]

In order to weight the calls by agent within the conditional date range, I used the following formula adjacent to the data set in column E:

=IF(AND(B2=B2,A2<=Dashboard!$J$3,A2>=Dashboard!$I$3),C2/SUMIFS(C:C,B:B,B2,A:A,">="&Dashboard!$I$3,A:A,"<="&Dashboard!$J$3),"")

Then I simply multiplied the weight by the existing handle time in Column F.

I then used the following forumla to populate the Dashboard.

=SUMIFS('Data Set'!F:F,'Data Set'!A:A,">="&Dashboard!$I$3,'Data Set'!A:A,"<="&Dashboard!$J$3,'Data Set'!B:B,"="&Dashboard!B9)

Attached please find an updated workbook with my solution.

Thanks,

miacg