Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Average with Criteria
hi!
I have a worksheet in which each row is a different job opening with 4 columns = Job, Recruiter, # Days Open, Close Date. I would like to find the average # of days open for each recruiter within a Close Date range. On the worksheet, a recruiter may have 10 jobs within a Close Date range, and I would like to know the average of the ten. I am toying around with using SUMPRODUCT, but can't get it to work right. Any help would be great. Thanks in advance. Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Average with Criteria
Nel ,
Mike R. ha scritto: hi! I have a worksheet in which each row is a different job opening with 4 columns = Job, Recruiter, # Days Open, Close Date. I would like to find the average # of days open for each recruiter within a Close Date range. On the worksheet, a recruiter may have 10 jobs within a Close Date range, and I would like to know the average of the ten. I am toying around with using SUMPRODUCT, but can't get it to work right. Any help would be great. Thanks in advance. Mike Hi Mike, you could try something like this: =SUMPRODUCT(($A$1:$A$20=E4)*($B$1:$B$20<=F4)*($C$1 :$C$20))/SUMPRODUCT(($A$1:$A$20=E4)) whe $A$1:$A$20 is the column for the names; E4 is the cell with the recruiter you want to examin; $B$1:$B$20 is the column for the dates; F4 is the cell for the limit close date; $C$1:$C$20 is the column for the # Days Open. -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Average with Criteria
Try this array formula** :
F2 = recruiter F3 = start date F4 = end date =AVERAGE(IF((Recruiter=F2)*(Close Date=F3)*(Close Date<=F4),# Days Open)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mike R." wrote in message ... hi! I have a worksheet in which each row is a different job opening with 4 columns = Job, Recruiter, # Days Open, Close Date. I would like to find the average # of days open for each recruiter within a Close Date range. On the worksheet, a recruiter may have 10 jobs within a Close Date range, and I would like to know the average of the ten. I am toying around with using SUMPRODUCT, but can't get it to work right. Any help would be great. Thanks in advance. Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Average with Criteria
Hi Mike,
just a small correction (I first wrote the formula directly on Windows Mail, without testing...): =SUMPRODUCT(($A$1:$A$20=E4)*($B$1:$B$20<=F4)*($C$1 :$C$20))/SUMPRODUCT(--($A$1:$A$20=E4)) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy "Franz Verga" ha scritto nel messaggio ... Nel , Mike R. ha scritto: hi! I have a worksheet in which each row is a different job opening with 4 columns = Job, Recruiter, # Days Open, Close Date. I would like to find the average # of days open for each recruiter within a Close Date range. On the worksheet, a recruiter may have 10 jobs within a Close Date range, and I would like to know the average of the ten. I am toying around with using SUMPRODUCT, but can't get it to work right. Any help would be great. Thanks in advance. Mike Hi Mike, you could try something like this: =SUMPRODUCT(($A$1:$A$20=E4)*($B$1:$B$20<=F4)*($C$1 :$C$20))/SUMPRODUCT(($A$1:$A$20=E4)) whe $A$1:$A$20 is the column for the names; E4 is the cell with the recruiter you want to examin; $B$1:$B$20 is the column for the dates; F4 is the cell for the limit close date; $C$1:$C$20 is the column for the # Days Open. -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Average with Criteria
Hi Mike,
Try using AutoFilter coupled with the Subtotal function. You then have a dynamic worksheet where you can select any range of dates. In pre xl2007 in AutoFilter you need to used Custom when setting the filter and in xl2007 you can simply select the required dates. Example of subtotal function is:- =SUBTOTAL(1,B2:B18) Where B2:B18 is the entire range of the unfiltered data. You can get more information on the above in Help but post again if you have problems. Tip when using AutoFilter to make life easy:- Set your column headers down from the top of the worksheet to about row 4 and then Freeze panes below the column headers and place your subtotal function above the headers in the frozen area. You then never loose sight of the column headers or your subtotal results. Regards, OssieMac "Mike R." wrote: hi! I have a worksheet in which each row is a different job opening with 4 columns = Job, Recruiter, # Days Open, Close Date. I would like to find the average # of days open for each recruiter within a Close Date range. On the worksheet, a recruiter may have 10 jobs within a Close Date range, and I would like to know the average of the ten. I am toying around with using SUMPRODUCT, but can't get it to work right. Any help would be great. Thanks in advance. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need help finding an average | Excel Discussion (Misc queries) | |||
Derive a formule for finding the average | Excel Worksheet Functions | |||
Finding a count above average | Excel Worksheet Functions | |||
Finding the average time | Excel Worksheet Functions | |||
Finding the average by dropping the lowest | Excel Worksheet Functions |