Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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 S Davis wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
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 Die_Another_Day wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Question (that I really can't describe!)
On 5 Jul 2006 14:01:26 -0700, "S Davis" wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trouble with advanced filter | Excel Discussion (Misc queries) | |||
Problem using Advanced filter | Excel Discussion (Misc queries) | |||
Hiding advanced filter | Excel Discussion (Misc queries) | |||
Historical Excel question statistical capabilities | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |