Weighted Avg - using values from multiple sheets

[JoelMcbride]

I maintain a spreadsheet for a telephone hotline. We track total
number of calls received (column F) as well as average time to answer
the call (column J). To analyze the month's average time to answer, I
use the following weighted average formula: SUMPRODUCT
(J5:J26,F5:F26)/SUM(F5:26).


I have 2 questions relating to this:


1) Is the above formula correct to determine the average speed to
answer? I've assumed the weights to be the total number of calls
received.


2) I have a different spreadsheet for each month (Jan, Feb, etc.). How
can I properly show the weighted average as a Year-To-Date number?


Thanks for your consideration,


Joel McBride
Reno, Nevada

 

[Harlan Grove]

JoelMcbride wrote...

I maintain a spreadsheet for a telephone hotline. We track total
number of calls received (column F) as well as average time to answer
the call (column J). To analyze the month's average time to answer, I
use the following weighted average formula:

SUMPRODUCT(J5:J26,F5:F26)/SUM(F5:26).

....

Generally best to put formulas on new lines in newsgroup messages, and
if they're particularly long, break them at commas and +-*/ (but not ^)
into multiple lines.

You have a typo in the denominator - should be SUM(F5:F26).

1) Is the above formula correct to determine the average speed to
answer? I've assumed the weights to be the total number of calls
received.

Should be if the col J averages are total answering times from some
other column divided by col F call counts. But if so, better to sum
that other column than use SUMPRODUCT on cols J and F in the numerator.

2) I have a different spreadsheet for each month (Jan, Feb, etc.). How
can I properly show the weighted average as a Year-To-Date number?

It gets ugly trying to make Excel work like a true 3D spreadsheet
(which it isn't). Easiest way to do this is to have a list of worksheet
names (Jan, Feb, etc.) in a range somewhere (I'll assume it's 12 rows
by 1 column) named WSLst, have the YTD month somewhere named YTDMon,
and use a formula like

=SUMPRODUCT(
N(OFFSET(INDIRECT("'"&OFFSET(WSLst,0,0,MATCH(YTDMon,WSLst,0),1)
&"'!J5:J26"),COLUMN(INDIRECT("RC1:RC22",0))-1,0,1,1)),
N(OFFSET(INDIRECT("'"&OFFSET(WSLst,0,0,MATCH(YTDMon,WSLst,0),1)
&"'!F5:F26"),COLUMN(INDIRECT("RC1:RC22",0))-1,0,1,1)))
/SUMPRODUCT(
N(OFFSET(INDIRECT("'"&OFFSET(WSLst,0,0,MATCH(YTDMon,WSLst,0),1)
&"'!F5:F26"),COLUMN(INDIRECT("RC1:RC22",0))-1,0,1,1)))

[And for those who believe I'm exaggerating how cumbersome this is in
Excel, here's a 123 equivalent.

@WEIGHTAVG(@@("Jan:J5.."&YTDMon&":J26"),@@("Jan:F5.."&YTDMon&":F26"))

Excel stinks for 3D.]

 

[JoelMcbride]

Harlan-

Thanks very much for your reply - I appreciate your efforts..