I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

Unfortunately I am in Excel XP (2002). Any solution for that version.


"Barb Reinhardt" wrote:

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

I tried the equation and get a "#value" result. Looking at it more closely,
the "IF" statements are not completed. Only the first of the two arugments
are stated. I would assume that if the argument is not met you would want
some kind of message. What do you think?

"Barb Reinhardt" wrote:

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

Did you remember to enter it as an array formula?

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

kwh wrote:

I tried the equation and get a "#value" result. Looking at it more closely,
the "IF" statements are not completed. Only the first of the two arugments
are stated. I would assume that if the argument is not met you would want
some kind of message. What do you think?

"Barb Reinhardt" wrote:

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

--

Dave Peterson

I get a #DIV/0! result. Is the averaging complicating the issue? In the
end, like the auto filter, I would like a list of the five most recent dates
of each employees. I could manage the average another way. Any thoughts?

"Dave Peterson" wrote:

Did you remember to enter it as an array formula?

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

kwh wrote:

I tried the equation and get a "#value" result. Looking at it more closely,
the "IF" statements are not completed. Only the first of the two arugments
are stated. I would assume that if the argument is not met you would want
some kind of message. What do you think?

"Barb Reinhardt" wrote:

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

--

Dave Peterson

Share the formula that you used.

And double check to see if your data matches what you used in your formula.

kwh wrote:

I get a #DIV/0! result. Is the averaging complicating the issue? In the
end, like the auto filter, I would like a list of the five most recent dates
of each employees. I could manage the average another way. Any thoughts?

"Dave Peterson" wrote:

Did you remember to enter it as an array formula?

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

kwh wrote:

I tried the equation and get a "#value" result. Looking at it more closely,
the "IF" statements are not completed. Only the first of the two arugments
are stated. I would assume that if the argument is not met you would want
some kind of message. What do you think?

"Barb Reinhardt" wrote:

In Excel 2003

A1:A10 are the names
B1:B10 are the dates
C1:C10 are the scores

=AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10)))

Commit with CTRL SHIFT ENTER.
--
HTH,
Barb Reinhardt




"kwh" wrote:

I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five
most recent dates for each employee and average the scores for that filtered
data.

Is that possible in advanced filtering? If so, how is it done?

--

Dave Peterson


--

Dave Peterson