Finding Average with Criteria

[Guest]

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

 

[Franz Verga]

Nel post:[email protected],

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))

where:

$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

 

[T. Valko]

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)

 

[Franz Verga]

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

 

[Guest]

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