IF Formula with

[millymoor2303]

Hi All,

I need to calculate UK statutory redundancy pay based on following rules.*

The current rules state the maximum that can be paid caps out at 20 years’ service and a max payout of £16,140 (£16,800 for Northern Ireland).
The weekly pay is capped at £538 per week (for Northern Ireland its £560 per week)

The rules also count on following age basis:

0.5 weeks’ pay for each full year of service when under age 22
1.0 weeks’ pay for each full year of service between age 22 – 41
1.5 weeks’ pay for each full year of service from age 41 or older

In addition to above we should count full years so if someone has 3.5 years’ service, only 3 years should be count. There is no rounding up.

It also needs to recognise that if their weekly pay is higher than the weekly statutory cap, then the weekly statutory cap is used for the calculation.

And if they have under 2 years service, they are not entitled to any pay

This is using a spreadsheet that was originally posted on this site that I have adapted. All the information is there, but the formulas used in AE to AH aren't pulling the correct figures through.

I can do this exercise inidividually and check it using the gov.uk calculator but when you need to do 100's of redundancy calculations, it would be much easier to have a formula that works.

Thanks

Milly

[mikeava]

Hi - can you please attach your file, rather than just a screen shot?

[TMS]

Try

Please Login or Register  to view this content.

copied down

[millymoor2303]

Now attached

[millymoor2303]

Hi All,

I need to calculate UK statutory redundancy pay based on following rules.*

The current rules state the maximum that can be paid caps out at 20 years’ service and a max pay out of £16,140 (£16,800 for Northern Ireland).
The weekly pay is capped at £538 per week (for Northern Ireland its £560 per week)

The rules also count on following age basis:

0.5 weeks’ pay for each full year of service when under age 22
1.0 weeks’ pay for each full year of service between age 22 – 41
1.5 weeks’ pay for each full year of service from age 41 or older

In addition to above we should count full years so if someone has 3.5 years’ service, only 3 years should be count. There is no rounding up.

It also needs to recognise that if their weekly pay is higher than the weekly statutory cap, then the weekly statutory cap is used for the calculation.

And if they have under 2 years service, they are not entitled to any pay

This is using a spreadsheet that was originally posted on this site that I have adapted. All the information is there, but the formulas used in AE to AH aren't pulling the correct figures through.

I can do this exercise individually and check it using the gov.uk calculator but when you need to do 100's of redundancy calculations, it would be much easier to have a formula that works.

Thanks

Milly

[TMS]

See post #3