Calculating a Salary Grade

Hi

I am trying to calculate the grade for each of the employees and their basic salary. The grade will be calculated on how many years that they have been working at the company, bellow are the grades and years of service

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 want to do it calculate each employees salary Grade based on how long they have been working in the company.

I know this have to be done with an IF statement but I cant seem to get my head around it.

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

Do you have to use an IF? What I would do is calculate the amount of days between their start date and today, then divide that by 365 to get the time in years. From there you can just do it manually.

Edit: I notice you've already got the years on the spreadsheet. So I'm guessing you have to use an IF as this is some sort of homework as above.

This seems to work. Might not be the best way. I'm no expert.

=IF(AND(P2>=0,P2<2),"G1",IF(AND(P2>=2,P2<6),"G2",IF(AND(P2>6,P2<10),"G3",IF(AND(P2>10),"G4","grade unknown"))))

P2 will need replacing with the column and cell reference (how long)

Do you have to use an IF? What I would do is calculate the amount of days between their start date and today, then divide that by 365 to get the time in years. From there you can just do it manually.

 

Edit: I notice you've already got the years on the spreadsheet. So I'm guessing you have to use an IF as this is some sort of homework as above.

Yes I dont have to put that put I did that to make things easier, it would be better if its not their but its not a restriction of such

- - - Updated - - -

This seems to work. Might not be the best way. I'm no expert.

 

=IF(AND(P2>=0,P2<2),"G1",IF(AND(P2>=2,P2<6),"G2",IF(AND(P2>6,P2<10),"G3",IF(AND(P2>10),"G4","grade unknown"))))

 

P2 will need replacing with the column and cell reference (how long)

I will try this, thank you so much

This seems to work. Might not be the best way. I'm no expert.

 

=IF(AND(P2>=0,P2<2),"G1",IF(AND(P2>=2,P2<6),"G2",IF(AND(P2>6,P2<10),"G3",IF(AND(P2>10),"G4","grade unknown"))))

 

P2 will need replacing with the column and cell reference (how long)

I've tried this, all the years bellow 5 works but nothing above works, its just comes as an error, I change the "P"'s but it didn't work

Works fine here. Check the formula doesn't have any extra spaces between the IF. The forum does weird things with the formatting.

Perfect man, it works perfectly, it was in fact a space with an IF statement. Thanks again, if its fine with you if I had more questions I would post in here?