i need some .xls help..

rattlsnak · rattlsnak's Garage

OK, who is good at Excel? I'm usually pretty good at it, but i have a project that has me stumped..

legion · 03-13-2006, 07:59 PM

I was a finance major in college. Had to do some pretty fancy stuff in Excel. I can try to help.

Don Plumley · Don Plumley's Garage

I'll stand in line behind legion. Have made many a huge Excel model in my time....more than I care to remember. Happy to help if I can.

rattlsnak · rattlsnak's Garage

legion, i sent you a PM with my email address...

legion · 03-14-2006, 05:52 AM

Sorry, at 10:00 last night the wife demanded I go to bed.

NICKG · 03-14-2006, 09:24 AM

damn wife OR woohooo!

Z-man · Z-man's Garage

If I were stranded on a deserted island and could choose only one tool to have with me, it would be Microsoft Excel.

I know a bit about the product - work with it everyday. What do you need to know?

-Z.

BlueSkyJaunte · 03-14-2006, 10:50 AM

Embarassed to admit my group of engineering analysts come to me with Excel questions.

"Yes, how can I help you?"

legion · 03-14-2006, 11:06 AM

Quote:

Originally posted by BlueSkyJaunte
Embarassed to admit my group of engineering analysts come to me with Excel questions.

That's the thing. Excel has a lot of built-in functions for a lot of different disciplines. I know finance stuff, but not the engineering stuff.

What discipline are you looking for help with?

rattlsnak · rattlsnak's Garage

basically what i am trying to do is determine where "you" will be 1,5,10 etc,. years from now on a seniority list. I have a listing of @2,000 people with seniority dates and birthday dates. I need a function that will figure that out. i.e.- an employee who is number 500 on the list wants to know what his seniority number will be in five years. I have seniority numbers, names, and birthdays (using 60 as a retirement age for this project) all in seperate columns. So the function will have to determine where "he" is on the list, and then determine how many people above on the list will retire in the number of years he plugs in, and then give him a new seniorty number.. ?

I also have Access, if it can be done easier on that, but would rather Excel.

Z-man · Z-man's Garage

What is the forumula for senority? Is it something like age + years of service subtracted from a number? (I think that's how they do it at my workplace). IF so, supply the numbers here.

-Z.

rattlsnak · rattlsnak's Garage

Seniority is strictly date of hire, nothing else. So someone who is 30 can be senior to someone who is 50 who just started with the company.
Thats the formula section i cant figure out because half of the people who will retire, are junior to the people who are 40ish.
Can it send it to you by email?

vash · vash's Garage

i think i got it.
i made a dummy one, that is very simple. i can email it to you.

basically
name, hired date, dob, seniority, age.

then i had a cell floating on the side with todays date. subtract hired date from todays date, and do the same for age. those cells you have to format to a number and round it to the nearest whole number. that number will be in days. divide by 365 to get years if you want. then use the data button on the toolbar, and sort by decending order, choose the seniority column as your criteria. it will flip everyone from newest hire to oldest hire. and the age will not be affected.

rattlsnak · rattlsnak's Garage

send it..

rattlsnak · rattlsnak's Garage

When you say age isnt affected, im not sure i know what you mean, because age is the determing factor for the retirement.

If I have 10 people on the list, person number 7 could retire before person number 1 because of age, regardless of hire date/seniority.

rattlsnak · rattlsnak's Garage

repost.. deleted

legion · 03-14-2006, 05:48 PM

I'm thinking this one over, and I'm a code monkey for a living, and I can't think of a good Excel solution. I'm assuming that each employee is by row. The thing is, the calculation for one row is dependent on all other rows.

rattlsnak · rattlsnak's Garage

I can send part of it to anyone who wants to try. Let me know..

Don Plumley · Don Plumley's Garage

I was noodling on this for a few minutes. You could set up one sheet with the data and validation calculations. Then another sheet is the data entry/query screen that does a named range lookup on the data set.

Then on the query screen, you have today's rank. The user enter's "X" years from today, this gets added to "today" and the data set is resorted, with those that are too old (time out with age > Y) having a manual count figure that forces them to the bottom of the sort.

Part of me thinks it would be easier to write a quick VB program that does the lookup on an Excel spreadsheet. But the stubborn part of me thinks it can be done in Excel. Think about a giant set of nested IF statements...

Marc - it depends on how automated you want this. If you can do the sort yourself, then it does not look too hard. Or I've totally underestimated this...

rattlsnak · rattlsnak's Garage

ok, here is a sample,, (all the names have been changed!

Sen# Employee # Name D.O.H. Birthday

1 234 tom 7/28/1979 8/28/1949
2 453 jeff 4/27/1980 6/23/1947
3 456 tim 12/1/1980 1/5/1952
4 786 mike 1/22/1981 9/14/1954
5 3465 mark 1/21/1982 12/11/1952
6 478 chris 4/9/1982 10/22/1949
7 877 james 5/24/1982 11/14/1950
8 00898 steve 6/1/1982 5/30/1957

I have them all in seperate columns. (seniority in A, employee in B, name in C, etc) Date of hire revelance, equals seniority order, so that column is really not important, but as you can see number 6 will retire before a few before him.