Excel Formula Issue

[guilbj2]

I'm working on a AHT (average handle time) tracking sheet for a cal
center. When calculating the average time however, I'm having a littl
difficulty. I need to perform this caculation for rows 5-47.

=((B5*D5)+(C5*E5))/SUM(B5:B48)

I need to add the results of (B5*D5)+(C5*E5) + (B6*D6)+(C6*E6).....
and so on right to (B47*D47)+(C47*E47) and then divide by the sum o
b5:b48 to give me an accurate average. The average function will no
work in this situation due to the varied number of calls being take
each day (b5) by each agent. If I take the average of each day'
score, days with high volumes of calls are weighted equally with day
that have lower call volumes and this skews the numbers considerable

 

[Bob Phillips]

Try this

=(SUMPRODUCT(B5:B47,D547)+SUMPRODUCT(C5:C47,E5:E47))/SUM(B5:B48)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[guilbj2]

I'm having a little trouble with the formula you posted. I wasn't aware
of the sumproduct command, but that should help. I'm still in a bit of
a bind though. The formula appears to have a syntax error that I can't
find. I'm getting the "The formula you typed in contains an error"
message. I can't find any errors, but that certainly doesn't mean
much. Would you be able to take a second look and see if there's
anything missing ?

 

[Bob Phillips]

Did you paste it or re-enter it? IF so, check all of the parentheses. I did
a small test and it worked fine in that,

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)