On 2002-08-21 16:39, PaddyD wrote:
Hi Mudface,
If Ian's formula doesn't work (unlikely after all that effort!

) I have a question - you say lenght of service is indicated in the c:u columns - how? Text / number entry in the appropriate column? Only one entry per row?
Paddy
Paddy,
I can send the book if need be (just don't push it OK

).
They are numbers, the table looks like this:
B2:U18=
{0,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20;18,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;19,2,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;20,2,3,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;21,2,3,4,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;22,2,3,4,5,6,0,0,0,0,0,0,0,0,0,0,0,0,0,0;23,3,4,5,6,7,8,0,0,0,0,0,0,0,0,0,0,0,0,0;24,4,5,6,7,8,9,10,0,0,0,0,0,0,0,0,0,0,0,0;25,4,6,7,8,9,10,11,12,0,0,0,0,0,0,0,0,0,0,0;26,4,6,8,9,10,11,12,13,14,0,0,0,0,0,0,0,0,0,0;27,4,6,8,10,11,12,13,14,15,16,0,0,0,0,0,0,0,0,0;28,4,6,8,10,12,13,14,15,16,17,18,0,0,0,0,0,0,0,0;29,4,6,8,10,12,14,15,16,17,18,19,20,0,0,0,0,0,0,0;30,4,6,8,10,12,14,16,17,18,19,20,21,22,0,0,0,0,0,0;31,4,6,8,10,12,14,16,18,19,20,21,22,23,24,0,0,0,0,0;32,4,6,8,10,12,14,16,18,20,21,22,23,24,25,26,0,0,0,0;33,4,6,8,10,12,14,16,18,20,22,23,24,25,26,27,28,0,0,0;34,4,6,8,10,12,14,16,18,20,22,24,25,26,27,28,29,30,0,0}
B19:U30=
{35,4,6,8,10,12,14,16,18,20,22,24,26,27,28,29,30,31,32,0;36,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30,31,32,33,34;37,4,6,8,10,12,14,16,18,20,22,24,26,28,30,31,32,33,34,35;38,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,33,34,35,36;39,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,35,36,37;40,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,37,38;41,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,76,79;42,4.5,6.5,8.5,10.5,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,80.5;43,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41;44,5,7.5,9.5,11.5,13.5,15.5,17.5,19.5,21.5,23.5,25.5,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5;45,5,7.5,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42;46,5,7.5,10,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5}
B31:U41=
{46,5,7.5,10,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5;47,5,7.5,10,12.5,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43;48,5,7.5,10,12.5,15,17.5,19.5,21.5,23.5,25.5,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5,43.5;49,5,7.5,10,12.5,15,17.5,20,22,24,26,28,30,32,34,36,38,40,42,44;50,5,7.5,10,12.5,15,17.5,20,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5,44.5;51,5,7.5,10,12.5,15,17.5,20,22.5,25,27,29,31,33,35,37,39,41,43,45;52,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5,43.5,45.5;53,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32,34,36,38,40,42,44,46;54,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,34.5,36.5,38.5,40.5,42.5,44.5,46.5;55,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37,39,41,43,45,47;56,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,39.5,41.5,43.5,45.5,47.5;57,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42,44,46,48}
B42:U52=
{58,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,44.5,46.5,48.5;59,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47,49;60,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,49.5;61,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;62,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;63,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;64,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;65,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;66,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;67,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;68,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50}.
Also, it is highly likely that the formula is wrong BUT itwil only be a Find/Replace on one of them that should getting working.
Mudface,
I've just taken this:
How much is a redundancy payment?
This depends on age and length of service and is calculated on the basis of complete years of service with the employer up to a maximum of 20 years.
The amount of the payment is:
one and a half weeks' pay for those years employed between the ages of 42 and 64
one weeks' pay for those years employed between the ages of 22 and 41
a half weeks' pay for those years employed between the ages of 18 and 21.
from UK law,
http://www.acas.org.uk/q_a/q_a9.html#2
so it looks like your company pay more than the legal requirement hence the reason for the table (IT HAD BETTER BE

).