HELP: Averaging values if they meet certain criteria

[KellyF]

Hi,

I have two worksheets and I'm trying to enter a function into the second
worksheet based on data in the first. They are based on recruitment activity,
e.g. how long it takes to fill a vacancy, the candidate source, etc.

Column P, "Offered" is simply a date (format d-mm-yy).
Column S, "Time to Fill" is a "Days360" function based of the date the
vacancy was received and the "Offered" date.

In the second workshop I want to create a function that will calculate the
average Time to Fill for any role offered in a particular month.

Please can someone help me! If necessary I can send you the spreadseet to
review.

Thanks!

 

[Pete_UK]

You can use an array* formula like:

=AVERAGE(IF((condition_1)*(condition_2),Sheet1!S$2:S$100))

where condition_1 relates to your roles and condition_2 relates to the
month. I don't know what column you use to record the roles, but
assuming it is column D, then condition_1 would be written as (Sheet1!D
$2$100=A2) where A2 is the cell on your second sheet where you can
enter the role you are interested in. Condition_2 can be written as
(MONTH(Sheet1!P$2$100)=9) if you are interested in September, or you
could put 9 into B2 of the second sheet and then refer to B2.

*As this is an array function then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER rather than the
usual ENTER. If you do this correctly then Exel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not
type these yourself.

You details are a bit scant, but adjust all the cell and sheet
references to suit your particular situation.

Hope this helps.

Pete

 

[stevefromnaki]

this should work:

For an average use a combination of SUMIF(range, condition) / countif(rango,
condition).

This will calculate a sum based on certain criteria, and also a coun of the
occurances of that criteria, hence an average using divided by in the middle
.

Let me know if ya want further explanation.

 

[KellyF]

Hi Pete,

I'm sorry but I've entered this formula

=AVERAGE(IF((Offered,">=1-Jun-06")*(Offered,">=1-Jul-06"),TTF))

and then hit ctrl+shift+enter however it tells me I have an error in it.

Offered is the range of dates and TTF is the range of number values that I
want to average (per month).

What have I don't wrong?