Advanced Question (that I really can't describe!)

[S Davis]

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!

 

[Die_Another_Day]

How about a mixture of "Indirect" and "Large"?
=INDIRECT("A" & MATCH(LARGE(B:B,1),B:B,0))
then change out the 1 for 2 to get the next person down and so on?

HTH

Die_Another_Day

 

[Ron Rosenfeld]

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

 

[S Davis]

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

 

[Die_Another_Day]

Well this will work with a couple of rules:
1.) It's an array formula so press Ctrl+Shift+Enter after typing
2.) It doesn't seem to work with generic ranges like A:A so use your
exact ranges
3.) Here it is...
=INDIRECT("A" & MATCH(LARGE((C1:C39="Active")*(B1:B39),1),B1:B39,0))
'Many thanks to Daily Dose of Excel for the MaxIf Idea that I copied
into "LargeIf"
HTH

Die_Another_Day

 

[Die_Another_Day]

I forgot one other thing to make this work: The min cell in all ranges
must be "1" for the "Indirect/Match" function to work properly. Maybe
Bob, Tom or some other genius can figure out how to get around that
limitation.

HTH

Die_Another_Day

 

[Ron Rosenfeld]

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

Perhaps you can work with the following:

If you download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

you could use the VSORT function.

Assume you have two named ranges: Position and Age

=VSORT(Position:Age,Age)

will return an array, sorted descending by Age, of the positions and age of
each entry.

A formula in some column of the type:

=IF(ROWS($1:1)<=RetireeCount,INDEX(VSORT(Position:Age,Age),
ROWS($1:1)+(COLUMNS($A:A)*RetireeCount-RetireeCount),1),"")

will return the name of the position with the oldest age.

If you copy/drag the formula down, it will return the position with the 2nd
oldest age down to the nth oldest age.

If you copy/drag across, it will eliminate those that were in the previous
(year's) column. (You could label each column with the appropriate year).

If the Retiree Count changes each year, you'll have to modify the argument a
bit to take that into account.

The above would be dynamic, and would not require filtering or sorting.


--ron