Some really good responses guys, thanks

I'm trying to stay away from anything involving filtering as this is
supposed to be a hands-off, automatic model.

The bit about removing them for next year - I don't know who will
retire and who won't. that's the clue, as this is a forecasting tool.
Many other areas of the model essentially put out a number, in my
example 2, of retirees, but there is no way to associate which
employees those 2 are going to be. This problem then is a way of
associating the oldest age with the likelylihood to retire. Obviously
Ive simplified the question somewhat, but I need this basic step to
progress any further.

I've thought abuot it a bit more and think a different approach may
work quite well - is there a way to rank all of my data without
manually sorting it? A formula that will simply place a number from 1 -
5000 and rank each dataset by age, so that the oldest turns up a 1 and
the youngest a 5000. Then I would need to have a formula that can look
at my number from the model and say, ok, in the next year the top 2
employees are no longer considered as active and so it will now knock
off the next 2 employees instead (or 3, or however many that happens to
be).

Failing that, the "indirect(match(large" formula is definitely
something I can work with, but is there a way to incorporate that into
an array to only include those employees currently listed with a status
as 'active'? Assume their status is in column C. This may in fact be
the optimal solution...

(This is to prevent digging out the oldest employees in my database who
are still listed but have been retired for 30 years and are therefore
over 100 years old in the books)

Thanks for sticking with me


Ron Rosenfeld wrote:
On 5 Jul 2006 10:49:01 -0700, "S Davis" wrote:

I have a complex problem that it seems excel is not designed to do. I'm
hoping there is a workaround that you could help me with.

I have a workbook which is predicting future trends from historical
data. Without getting into too much detail, essentially this model has
now told me that there will be 15 people retiring within the next year.
Since these 15 people could come from any area, I need to select only
those who are the oldest from the data I am working with and list their
position somehow, or count them as 'retired'. This process would then
begin again for the next successive year and hopefully ignore those 15
from the previous year.

Assume for now that I have the positions listed in column A and ages in
column B. Something like this:

Driver 56
Garbageman 59
Street Performer 62
CEO 60
Gymnast 57
...
etc.

Assume my model has told me that 2 people will retire. By simply
looking at the data I can see quite quickly that the Street Performer
and CEO are most likely to retire based off of their age... however, in
real life I am working with a listing of 5000 bits of data and can't
just eyeball it. So is there a way to analyse that data and note or
display the retirees as:

1 Street Performer
1 CEO

and then have them removed for the next year (where the next year would
select the two oldest as Garbageman and Gymnast)...?

It's a bit of an odd question as the answer requires some creativity.
Does it sound possible within excel?

Thanks a ton!

There are a variety of approaches one could use for determining the "n" oldest
people where "n" is the number going to retire.

You could use the LARGE function; you could sort your table by age; you could
use an advanced filter.

I'd probably use one of the latter two with large numbers of employees.

But I don't understand the bit about "remove for next year". What if they
don't retire? When would they again be in the "most likely to retire group"?

If they do retire, but remain on the list, you could add a column with, for
example, an "R" to indicate that they are retired, and use the Advanced Filter
or Auto Filter to get filter them out, also.


--ron