Spreadsheet to calculate statutory redundancy weeks entitlement

[Mrs_Z]

Hi All!

So this is my first day on this website and of course, my first post! I apologise in advance with how long winded my post is but wanted to make sure i give all details required! Would REALLY REALLY appreciate anyones help with this as i have been struggling over this for over a week now :(

I am not AT ALL an expert in excel (i would actually go as far as to question even my basic skills!) so i am finding this task increasingly difficult to do. I would appreciate any help at all (in layman terms please lol) that can help me get to where i need to be.

I need to create a spreadsheet which calculates statutory redundancy entitlements with both weeks and payment. The basic principles are

Individuals are entitled to receive

• half a week’s pay for each full year they were under 22
• one week’s pay for each full year they were 22 or older, but under 41
• one and half week’s pay for each full year they were 41 or older

Weekly pay is capped at £538 and length of service is capped at 20 years so the maximum statutory redundancy award for any individual is £16,140. The minimum years of service is 2 years to be entitled to a redundancy payment.

There is also a statutory notice period to take into consideration but I am okay with this bit and dont think i need any help with this formula.

I have actually managed to create all the relevant formulas (using this wonderful site and lots of copy and pasting!) and have the attached what i have so far. The only problem i am finding is that the redundancy entitlement (column J) is not aligning with the government website in every case. After lots of messing around, I think i have found what the problem is. Using Row 4 (John Doe) as the example point, the government has calculated the redundancy entitlement based on the age of the employee when he first started (as they have to be full years of service within that age year). So;

Age Entitlement

38 1 weeks pay
39 1 weeks pay
40 1 weeks pay
41 1.5 weeks pay
42 1.5 weeks pay
= 6.0 weeks pay


However i believe the formula within the spreadsheet is basing it on the age of the employee but working backwards


Age Entitlement

43 1.5 weeks pay
42 1.5 weeks pay
41 1.5 weeks pay
40 1 weeks pay
39 1 weeks pay
= 6.5 weeks pay

Obviously the spreadsheet is wrong as he hasn't completed a full year whilst being 43.

Can anyone tell me what i need to change in the formula for column J to take the above into consideration??

Thanking you hugely and eagerly awaiting any responses

[Limor_OP]

Hi Mrs_Z


So if I understand correct you want to see in J4 (for example) the value 6 and not 6.5 ?

I just subtracted 1 from C4 in your wonderful formula:

=IF(E4<2,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&MIN(E4,20)))+(C4-1)-MIN(E4,20),{0,22,41;0.5,1,1.5})))


Nice file for someone who says he doesnt have developed excel skills

[Mrs_Z]

Oh wow - how did you make that look so easy??!!!

So just so I understand the logic behind your amendment, you have taken a year off the age so it only calculates the years of age where full years of service have been complete? Is that right? Would that cause an issue with someone whose birthday falls on the termination date? I'm guessing not but my tired brain is fuzzy...!

I cant believe it was so simple yet so complicated lol

I cant thank you enough

And i cant take any credit at all - im just so happy i live in a era of google!

[Limor_OP]

Hi,

it's exactly like you say,
your formula made a calculation on the ages 39-43 (see screenshot 1) if you press F9 you can view the values stem from your formula. By subtracting one the array became 38-42
(see screenshot 2).

The current age(43 for example) will never be a full year , once it completes a year it will turn one year older and become 44.
So I think that the formula will cover for you all scenarios, ages, and dates.

Good Luck!

[Mrs_Z]

I just cant thank you enough for all your help!! I had no idea about the F9 function either.... You are amazing, thank you so so much!!

[Limor_OP]

You're welcome! it's my pleasure.

Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
Thank you.

[James955i]

Just wanted to say thank you for this, it is going to be a huge help. I would just caution that the date of dismissal appears to be linked to 'today' rather than a future date, if you are planning redundancies for a few months into the future the figures may not account for further accrued complete years service.