Different Years service working out redundancy calculation

**Webbn111** · 11-26-2014, 12:25 PM

Hi,

I am trying to work out a formula that will work out the amount of statory pay. These are the following reference point

Age 16 - 21 - 0.5 weeks per years service
Age 22 - 40 - 1 week pay per years service
Age 41 and over - 1.5 weeks per years service

Additionally 0 - 2 years service does not give rise to any weeks, eg 10 years service at the age of 25 is 8 weeks pay. I have the following spreadsheet.

Age Range Week Weekly pay Statuary pay

16 21 0.5 464
22 40 1 464
41 100 1.5 464

under 2 years 0

Date of Birth Age Start Date Current date Length of Service (Complete Years) Under 2 years Amount of years for pay Age when started
02/06/1972 42 06/05/1990 26/11/2014 24 2 22 17

Any help is much appreciated

Neal

**Special-K** · 11-26-2014, 12:34 PM

Try

=IF(Service<2,0,LOOKUP(Age,{16,22,41},{0.5,1,1.5}))

You'll have to replace Service and Age with the specific cell references where those values are stored

**ELeGault** · 11-26-2014, 12:44 PM

Before I throw the solution out as it can be convoluted depending on your setup. Do you have a sample of the data set that you are working on and where you would like these results to appear.

I ask because it would be best if you don't already, to have a column that is calculating their Tenure in Years as well as their Birthdate again in years.
With those two the If statement becomes much easier.
And what do you want to display as the results. If I am 29 then I fall under the 22-40, which would give me 1 week per Year. So if I have worked there 3 years it would be 3. Would you want to see the 1 or the 3...

Sample data with desired outcome in (CELL) makes it easier to work with.

=IF(REFDATE=0,0,IF((TODAY()-REFDATE)/365>=41,1.5,IF((TODAY()-REFDATE)/365>=22,1,IF((TODAY()-REFDATE)/365>=16,0.5,0))))
REFDATE - Ref date in the above would be birthday, you can see how this becomes lengthy... it would be simpler if Age was already calculated in a separate field not only for the formula but for the reader of the data to see that they are X age and Make Y amount per year of service with a total of Z so far.