Large Function

[,]

I have a spreadsheet with something similar to item below. I need to be
able to find the oldest (largest) item for each person. Any and all
help is appreciated. And, it cannot return an oldest date with a cost
code of 7.




employee Days Old Cost Code
Joe 30 3
Jack 45 7
Joe 21 1
Joe 45 3
Joe 75 7
Jack 21 1
Jack 46 4


How do I return 45 for Joe and 46 for Jack? Well, give me one formula
for either and I can figure out the other.

Thank you for any assistance.

Dave Elliott
(e-mail address removed)

 

[Ron Rosenfeld]

I have a spreadsheet with something similar to item below. I need to be
able to find the oldest (largest) item for each person. Any and all
help is appreciated. And, it cannot return an oldest date with a cost
code of 7.




employee Days Old Cost Code
Joe 30 3
Jack 45 7
Joe 21 1
Joe 45 3
Joe 75 7
Jack 21 1
Jack 46 4


How do I return 45 for Joe and 46 for Jack? Well, give me one formula
for either and I can figure out the other.

Thank you for any assistance.

Dave Elliott
(e-mail address removed)

The *array-entered* formula:

=MAX((employee=F2)*(Cost_Code<>7)*Days_Old)

where F2 contains the employee name of interest.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron