Syntax Trouble with SUMPRODUCT

  • Thread starter Thread starter guilbj2
  • Start date Start date
G

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's score
days with high volumes of calls are weighted equally with days tha
have lower call volumes and this skews the numbers considerable. I
may be helpful to know that some of the cells in these rows/column
will be blank if there is no data for that day.

Someone suggested the formula:

=(SUMPRODUCT(B5:B47,D5:D47)+SUMPRODUCT(C5:C47,E5:E
47))/SUM(B5:B48)

Unfortunately, it does not work. I think there is a syntax error, bu
I can't identify it
 
Another thought seeing it the NG, is word wrap. This bit

=(SUMPRODUCT(B5:B47,D5:D47)+SUMPRODUCT(C5:C47,E5:E
47))/SUM(B5:B48)

should all be on one line. If you cut and paste, it will probably over 2
lines.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You're a gentleman and a scholar sir. I typed in the formula manuall
rather than pasting it and it works like a charm. I really appreciat
your help
 
Great. Sometimes I post without a test, sometimes with. And you always
wonder if you missed a bit.

Bob
 
Back
Top