Calculating promotion date

[ashenperera48]

Hi

 

I am trying to calculate the promotion date that each employee will gain promotion to the next grade

 

G1: Less than 2 years service / Salary £12582

G2: 2 – 5 years / Salary £16647

G3: 6 – 10 years / Salary £20145

G4: 10 years or more / Salary £25687

 

 

What I wanted to do is to find out the date which each employee will gain the next promotion to a different grade

 

I have attached the document itself that I have been working on, any help would be greatly appreciated

 

Thank you.

SS Data File Student Copy 2015-16.xlsx

[featured_spectre]

Looks like homework, especially with that file name. Probably not going to get help with this I'm afraid.

[steveking]

Look at the document properties - it shows the student's name, the school name, and even the teacher's name who created the task.

 

If this isn't homework from a student I'd also be amazed!

 

Ashen, if you are going to cheat you need to put way more effort into it. Frankly it's probably easier just to write the few formulas you need and do the homework yourself!

[CAWJames]

I hope this someone's blatant attempt at homework, or this is a major data breach

[FN-GM]

Lets says its homework, why wouldn't people help with it? He isn't asking for anyone to do it for him! Everyone has to learn. If your niece, nephew son or daughter asked for help would you refuse them? We are supposed to be in the education sector after all...

 

If I actually knew what I was doing in Excel I would help. But im afraid I don't.

Edited November 11, 2015 by FN-GM

[ashenperera48]

This is work that I need to practice, so technically is it homework. But as FN-GM said I just need some help solving the issue that I have, I thought this forum could help me with that.

[steveking]

How do you think you should be doing it? I guess vlookup, but you could also be wanting to use a sequence of IFs as the salary has been done (but this isn't anywhere as neat)

[elsiegee40]

He had help with the previous homework assignment http://www.edugeek.net/showthread.php?t=161458

 

Ashen - The problem is that, were we in a classroom with you, we could help you to solve it without actually doing it for you. Online, we end up giving you the solution and you learn nothing

 

If you don't understand you should go back to your teacher as it is likely that others in the class have the same problem

Edited November 12, 2015 by elsiegee40

[Edu-IT]

Ashen - The problem is that, were we in a classroom with you, we could help you to solve it without actually doing it for you. Online, we end up giving you the solution and you learn nothing

Not always the case. Sometimes people can look at how something is done, properly, and then deconstruct it to see what each part does. I personally find that helpful sometimes.

[simonw]

Ashen - before you start anything, you need to correct the formulae. I won't be offering anymore help, as to be given this kind of work, you must be of an aptitude to be able to solve it off your own back.

[CyberNerd]

He had help with the previous homework assignment Calculating a Salary Grade

 

Ashen - The problem is that, were we in a classroom with you, we could help you to solve it without actually doing it for you. Online, we end up giving you the solution and you learn nothing

 

If you don't understand you should go back to your teacher as it is likely that others in the class have the same problem

 

I don't think that's really how it works these days. I Often see students email, chat or forum post to teachers in our school. In fact online is the ONLY way homework is set, and naturally students ask teachers for help - online and teachers help them. The fact that @ashenperera48 is asking online is testement to this

[elsiegee40]

In that case, maybe someone has the time and skill to explain without answering it for him... However, I still think it's better to admit to your teacher that you have a problem.

[ashenperera48]

I don't need the answer but at least if someone can explain to me how it works, that would be good enough

[CyberNerd]

I don't need the answer but at least if someone can explain to me how it works, that would be good enough

 

Start by looking at the information that you have. ie: the employee start date. From that you also know the promotion dates. You also know the current grade.

From those 3 pieces of information you can calculate the date of next promotion. If you need to, add extra columns and "show the working" like in maths. Then refine it.

[simonw]

Sort the formulae first tho'

[pcstru]

There seems to be an assumption which is unstated, that everyone starts on G1 (otherwise you would need to know the date of the last promotion). If that is the case then you are really just wanting to look up the number of years from the start date to the next band. That should be pretty simple to do with vlookup or hlookup if you are vertically challenged. Then, since excel is pretty good at date arithmetic, you just add that number of years to the start date. There is probably a function for that similar to the one you used to calculate the years worked (you could add 365* that number, but that will not deal with leap years).

[ashenperera48]

Sort the formulae first tho'

 

What's wrong with the formulae @simonw?

Edited November 12, 2015 by elsiegee40

[TechMonkey]

Definitely research Vlookups. It will make your formula much cleaner. You may want to check with your teacher (or depending what you were learning about most recently) that is what they want you to do though. If you have been learning about IF statements, then they may want you to solve this with IF statements.

 

You will need to think about what you can add to the grade and salary table to allow you to look up the information you need. Once that is done it should be plain sailing.

 

EDIT: sorry missed the promotion bit. Once you have worked out the VLOOKUPS process you will then be able to find the promotion date by finding out how to work out VLOOKUP+1. Your only issue is what you do with anyone who is at the top level.

 

Hope that helps

Edited November 12, 2015 by TechMonkey

[simonw]

Change the start date, and you'll see. Experiment with a few changes and you shall see eventually. Test, Test, Test..........

[ashenperera48]

Definitely research Vlookups. It will make your formula much cleaner. You may want to check with your teacher (or depending what you were learning about most recently) that is what they want you to do though. If you have been learning about IF statements, then they may want you to solve this with IF statements.

 

You will need to think about what you can add to the grade and salary table to allow you to look up the information you need. Once that is done it should be plain sailing.

 

EDIT: sorry missed the promotion bit. Once you have worked out the VLOOKUPS process you will then be able to find the promotion date by finding out how to work out VLOOKUP+1. Your only issue is what you do with anyone who is at the top level.

 

Hope that helps

 

Thank you, my teacher wants me to experiment with different options such as IF statements and lookup functions, I would like to use an IF statement in this case

[ashenperera48]

Change the start date, and you'll see. Experiment with a few changes and you shall see eventually. Test, Test, Test..........

 

For some reason when ever I change the start date for the 1st person, "years worked" cannot go over 6, however it works for others

[ashenperera48]

There seems to be an assumption which is unstated, that everyone starts on G1 (otherwise you would need to know the date of the last promotion). If that is the case then you are really just wanting to look up the number of years from the start date to the next band. That should be pretty simple to do with vlookup or hlookup if you are vertically challenged. Then, since excel is pretty good at date arithmetic, you just add that number of years to the start date. There is probably a function for that similar to the one you used to calculate the years worked (you could add 365* that number, but that will not deal with leap years).

 

Thanks.

Do you suggest using Lookup functions to IF statements as I understand that it will be much easier with Lookups?

[pcstru]

IF's, used as worksheet functions (those that you use in a cell) get quite messy and difficult to deal with when you have a lot of options to sift through. A lookup will be straight forward, (although some people seem to find them tricky to understand - ask if you want an explanation). Lookups also tend to be elegant in that the functional aspect of the solution is well decoupled (separated) from the data. If your criteria for promotion changes you only need someone to alter the data in lookup table you are using, with an IF statement, you need to go in and alter the actual code and then copy that code around the cells.

[TechMonkey]

If you used lookups then you could use IFs to capture the error I was talking about earlier. This is where a teacher set task and real world diverge. As pcstru says, IFs get messy quickly so should only be used in simple formulas. That isn't a hard and fast rule, but generally a good guideline. Once you start embedding IF within IF within IF within IF ad infinitum you get issues and should look at other options.

[ashenperera48]

Once you start embedding IF within IF within IF within IF ad infinitum you get issues and should look at other options.

 

Maybe this is why for the first person, cannot have more than 6 years of working