Weighted Avg - using values from multiple sheets

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

0
2/23/2005 5:41:50 PM
excel 39879 articles. 2 followers. Follow

2 Replies
460 Views

Similar Articles

[PageSpeed] 46

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.]

0
hrlngrv (1990)
2/23/2005 8:36:30 PM
Harlan-

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

0
3/1/2005 5:41:34 PM
Reply:

Similar Artilces:

Multiple copies of just one email
I'm using Outlook 2002 on Windows XP professional and I keep receiving a new copy of one email message every time I open Outlook or it performs a timed send/receive. All other email messages have been fine, i.e. just one copy. Would this be a problem with my computer that I can fix, a problem with the sender's computer that she can fix, or is something else the matter? I only get one copy of all other messages from the same sender. If anyone else is having this problem, I received an answer in the Office-Outlook-General Questions Discussion Group. I was able to log into my ...

Using installer Cleaner
Sorry, my initial post mentionned mistaken Registry cleaner. I meant Installer Cleaner. Installer Cleaner is a neat tool when the standard uninstall process fails. I am wondering if there is a way to track changes made to the registry while removing a given entry with this Installer Cleaner? -- John Doue If you mean the windows installer cleanup utility, read the caveats before using the tool; which in any case only removes the installer entries, and not the actual program "John Doue" <notwobe@yahoo.com> wrote in message news:ORyraR2zKHA.6112@TK2MSFTNGP0...

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

Order entity
The "Order" entity in CRM has a number of status's (Active, Fulfilled, Cancelled, Submitted and Invoiced). From the Actions menu on the Order form it's possible to Cancel and Fulfill an Active order - and doing so changes the Order status as you would expect. However: 1) Invoicing the order (which creates an Invoice record) doesn't change the status of the order to Invoiced 2) I can't see how to change the status to Submitted. I expect it's possible to do both of the above programatically. However is it possible to do it through the CRM UI? On Jun 14, 9:10=A...

Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your d
Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your daughter will love. http://www.shoesbootjeans.com http://www.shoesbootjeans.com/Replica_Mens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Womens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/newarrivals.html http://www.shoesbootjeans.com/hotproducts.html Used clothing stores are full of ite...

Changing the fields the LOOK FOR facility uses
Hi, Ive had a good look round the system and dont think this is possible, just wondered if anybody else has any ideas. The issue is as follows :- If I go to the default cases view (All Cases), I can see all cases against all accounts. I would like to be able to Look For all or part of an account name (or potentially other fields) to quickly filter this list. Is it possible to alter or add the fields that the Look For facilty will use to return results? or Can an advanced find use take arguments? Cheers Dan Hi, Go to Settings - Customization, and customize the Case enti...

Sum based on range values
I need to sum a column A, but only if values in an adjacent column B>0. Therefore the example below should sum to 10 (only including the first 3 rows). A B 7 5 0 4 3 9 0 0 0 0 0 0 8 0 0 0 0 0 Ideally this should be a formula in a cell, not a macro. Any ideas? It doesn't sound complicated, but I can't get my head round it. TIA Ian Thanks Julie. I knew it had to be straightforward. Just a case of knowing which function to use. Ian "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message news:%23$anRGw$EHA.3836@tk2msftngp13.phx.gbl... > Hi > > us...

Newbie struggling
Hi For coursework I have to create an excel workbook that takes totals from invoices and adds them all up - pretty simple for some but not me! I have created the invoice template and the user enters the necessary data to complete and print it out - what I need is a way of taking four or five sets of data from that invoice sheet (e.g. invoice number, customer name etc etc) and writing them to another sheet so that I can add them up. The problem I am having is how to get that data into the next blank row in the second sheet - currently it only overwrites the first set - how do I get the pointe...

Formatting 0 values to show blank cells
I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value. For printing purposes I need the 0 to not show in the cell (blank cell). I can do this by using the accounting format, but a dash (-) still shows in the cell. The sheet is protected to protect the formula. How can I protect AND not show anything in the cell WHILE keeping the value at "0"? You can use set the custom format to ;;""; See Worksheet and Excel table basics > Formatting numbers in Excel help file for details. "RLD" wrote: > I am using the =...

mail merge multiple lines in an e-mail
How can I merge multiple lines from an excel document into one e-mail ? So the e-mails should be dynamical. Hi Guy, There's probably an Excel vba solution for this, but there's also a Word mailmerge/vba solution using Word's Catalogue/Directory Mailmerge facility (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at: http://www.wopr.com/index.php?showtopic=731107 or http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip Do read the tutorial before trying to use the mailmerge document included with i...

sum several sheets and different columns
Hi all, Any ideas of how to get this formula not so ugly: =SUM(V5U!R5+V5U!S5)+(V5U!AA5+V5U!AB5)+(V5U!AJ5+V5U!AK5)+(V5U!AS5+V5U!AT5)+(V5U!BB5+V5U!BC5)+(V5U!BK5+V5U!BL5)+V6U!BM5+V7U!BM5+V8U!BM5+(V9U!I5+V9U!J5) I have one week in every worksheet and trying to get a summary of month in an easy way. Thanks in advance //Thoma -- Message posted from http://www.ExcelForum.com If you are just looking for neatness you could: Define local named ranges V5U!MyRange1 refers to =V5U!$R$5:$S$5,V5U!$AA$5:$AB$5,V5U!$AJ$5:$AK$5,V5U!$AS$5:$AT$5,V5U!$BB$5:$BC$5,V5U!$BK$5:$BL$5 V9U!MyRange2 refers to =V9...

Forward multiple email messages at the same time in Outlook
How do I forward multiple email messages at the same time in Outlook? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/outlook/201005/1 Insert the individual e-mail items into one new mail message and send that message. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Shawn76 via OfficeKB.com asked: | How do I forward multiple email messages at the same time in Outlook?...

how to sort cells with sheet reference
i an unable to sort the data in cells containing a reference to anothe sheet. it seems as though excel is treating this reference as absolut and not relative. is there any way to do this, such that thes equations adjust to the sorting? any thoughts would be appreciated. thanks -- vba_neophyt ----------------------------------------------------------------------- vba_neophyte's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2418 View this thread: http://www.excelforum.com/showthread.php?threadid=37804 one way would be to copy and paste specail values only - a...

Triming the fat off my sheets...
Ok I have a spreadsheet with 100 items and another one with 150. The two sheets match up exept for the 50 odd balls. How do I get the the first sheet to line up with the second on one sheet. Hi not quite sure but try: http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany Neal wrote: > Ok I have a spreadsheet with 100 items and another one with 150. The > two sheets match up exept for the 50 odd balls. How do I get the the > first sheet to line up with the second on one sheet. ...

When using Terminal Server
When remote users open Outlook from a Terminal Server session, they only see a small portion of the messages in their inbox (and other folders) that are on the Exchange Server, yet when they log in at the main office, they see all messages. What controls the two different views and how is it changed. Thanks in advance for any help, Bob Showalter, Packer International ...

how do I tell excell 2003 to print sheets back to front?
In excel 97 I could tell printer to start printing from the back page to the front so the job would come off of the printer in the right order. I cannot find that command in excell 2003. Am I overlooking it, or have they changed the command convention to an obscure, hard to understand, jargon. FILE | PRINT... | Click on the PROPERTIES button | PAGE ORDER, i.e. back to front. "goochiepepper" wrote: > In excel 97 I could tell printer to start printing from the back page to the > front so the job would come off of the printer in the right order. I cannot > find that co...

Does anyone still use vbAdvance?
I want to customize my DllRegisterServer function and vbAdvance (http://vb.mvps.org/tools/vbAdvance/) sounds like it would be perfect. Unfortunately after downloading and installing, I try to register the Custom Registration example project "DllRegisterServerSample.dll" with regsvr32 and I get an error x0..05. I traced the problem to the code that starts the required vb stuff, in MRuntimeInit, line 106: hMod = GetModuleHandle("mydll.dll") .... pGetClass = GetProcAddress(hMod, "DllGetClassObject") If pGetClass Then CopyMemory pCall, Init...

Deleting Rows Automatically using a Text File List
I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------...

Maximum value to use autofiler feature in excel
Hi, does anybody know the maximum value to use autofilter in ms excel? I heard my friend told me that excel could filter only select data is less than 1000. He told me that he has 4000 row and use autofilter, an expected record is more than a thousand but the record display only 1000 maximum. I want to test like him too but I don't have any file that contain alot many thousand of record. Does anybody have any idea? Hi see: http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany "Roath Kanel" <012633023@mobitel.com.kh> schrie...

VALUE error message
I would like I5 to equal F5+G5-h5. I get a value error message. one or more of the cells F5,G5 or H5 probably have text in them, either a number entered/pasted as text, or a "blank" made by pressing the space bar (and inserting a space character). For the first problem, copy an empty cell, select F5:H5 and choose Edit/Paste Special, selecting the Values and Add radio buttons, then click OK. This will coerce any "text" numbers to real numbers. For the latter, use the del key or Edit/Clear/Contents rather than the spacebar to clear the cell. Note that SUM(F5:G5,-H5...

Formula Error
I have a list of codes in F$19:F$1500. I was attempting to use the following code to assign a number to each unique value in the list and repeat the previously assigned number if it is duplicated. This works for the first 15-20 numbers, but returns an error (#n/a) afterwards as if the code stopped working. I have tried EVERYTHING... Same result each time. =IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G $18:G18)+1) Any clues? My guess... =countif() treats everything as text. So the text string ="1" (or '1) will be counted as a match as well as the num...

if the cell value exceeds the limit to have a dialog box appear
Is there a way that, if the cell value exceeds a certain limit, a dialog box would appear? Thanks for any assistance. Dave Check Data Validation: Select the cells Data>Data Validation Allow: (check out the number options: whole number, decimal, etc) Does that help? *********** Regards, Ron "Dschro" wrote: > Is there a way that, if the cell value exceeds a certain limit, a dialog box > would appear? > > Thanks for any assistance. > > Dave > Ron, Here's the scenario -- on an Expense Report: If an employee was to fill in any amount in the &...

Publisher 2003
Hi, I'm trying to create a membershipcard with publisher. I used a mail merge to create all the cards with different names and picuters. On the screen everything looks fine. I want to have multiple cards on one page with a total of eight per page. When looking at the print preview every card shows the same name. I read about the bug with the preview so I printed some pages. Now I get a page full of cards with the same name and then a page with the second name in all the cards and so on. What am I doing wrong? Maarten Maarten <rijt@dse.nl> was very recently heard to utter: ...

can't save files in subfolders using Word #2
I'm running Word on a Mac running OS X. When I go to save a file in Word, it only gives me the option to save in the root files like "Documents" or "Applications." How do I save the file into a subfolder? On a PC, I would double click to open the folders until I got to the folder I needed. That doesn't work here. Can someone help? I'm sure this is something obvious I'm missing. Thanks - Taylor In article <1163740041.258317.226140@b28g2000cwb.googlegroups.com>, taylorgriffin@gmail.com wrote: > I'm sure this is something obvious I'...

using find and replace it's making me look in another file to upd
I have used the find and replace function several times in the spreadsheet to update the fromulas monthly. For some reason when I use it this month it wants me to look in another file to "update values"? There is not another file and there is no way for me to use the find and replace. ...