I need a formula to calculate redundancy payments in a table. The redundancy
payment due to each employee depends on his or her age and length of service
(up to twenty years). This determines the number of weeks pay due. I want to
calculate, automatically, the number of weeks pay due.

To calculate the number of weeks pay due, one should use the following
amounts €“

0.5 week's pay for each full year of service where age during year less than
22
1.0 week's pay for each full year of service where age during year is 22 or
above, but less than 41

(I have, within the spreadsheet I am using, a cell which already has
calculated the age - can you, in the answer, use A1 to refer to this cell,
thanks).
1.5 weeks' pay for each full year of service where age during year is 41+
I believe this can be done, by a single cell formula using if, greater than,
less than etc., but I cant get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

Try
=IF(A1<22,A1*0.5,IF(A1<41,A1-21+10.5,A1-40+30.5))

This is based on the fact that for A1=21 it will be 10.5, for A1=41 it will
be 10.5+20...

"Red_Goldfish" wrote:

I need a formula to calculate redundancy payments in a table. The redundancy
payment due to each employee depends on his or her age and length of service
(up to twenty years). This determines the number of weeks pay due. I want to
calculate, automatically, the number of weeks pay due.

To calculate the number of weeks pay due, one should use the following
amounts €“

0.5 week's pay for each full year of service where age during year less than
22
1.0 week's pay for each full year of service where age during year is 22 or
above, but less than 41

(I have, within the spreadsheet I am using, a cell which already has
calculated the age - can you, in the answer, use A1 to refer to this cell,
thanks).
1.5 weeks' pay for each full year of service where age during year is 41+
I believe this can be done, by a single cell formula using if, greater than,
less than etc., but I cant get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

As I understand your description, A1 contains the employee's PRESENT age.
But that doesn't help you; you need his age ON JANUARY 1, because if he's 22
now but was 21 on Jan 1, he should get half a week's pay for this year. And
I'll assume for the rest of this explanation that the age you refer to is his
age on Jan 1 of 2009, rather than of some other year; you can use any year
but the subsequent calculations have to know which one. The best formula
that I can think of off-hand for calculating this value is
"=2009-YEAR(<birthday-1)-1"; that gives you an age of 0 for anyone born last
year, unless he was born on Jan 1 in which case it gives you an age of 1 this
year.

Second, in addition to the employee's age on 2009-01-01 you need another
datum: the number of years he's been working for you. Otherwise you don't
know whether to pay him for the year 1986; using his age you can tell that he
turned 41 that year, but you still don't want to give him that week's salary
if he didn't come to work for your company until 1991. I'll assumed B1
contains the number of calendar years during which he worked for your
company. Understand that this isn't the number of full years he's worked; if
he started work November of last year, your calculation is to pay him for
both 2008 and 2009.

Third: If an employee was born February 1, 1945, then he's 64 now but was 63
at the beginning of this year (so A1 contains 63). But if he first came to
work for your company on June 1, 1986, does he get a week's pay for that
year, or a week and a half? Because during part of that year he was 40 years
old, so he ought to get a week's pay; but he was already 41 by the time he
came to work for you, so maybe he'll argue (in court) that he should get a
week and a half for that year, because he was never 40 while working for
you. Just a thought; I'll continue as though he is to get just a week's pay
for that year.

Ok, so we have in A1 the employee's age as of Jan 1 this year, and in B1 the
number of years during which he was an employee. The obvious way to do this
calculation is to figure out how many of those years he was 22 or less, and
how many between 23 and 40, and how many 41 or greater, and multiply each of
the figures by 0.5, 1.0 and 1.5 respectively; the sum is the number of weeks'
salary he gets paid. But it turns out the formula is simpler if you do it
this way: Give him half a week's salary for all the years in which he's
worked. Give him another half week's salary for all the years during which
his age was 23 or greater. Give him another half week's salary for all the
years in which his age was 41 or greater. That formula looks like this:

=B1+MAX(MIN(A1-22,B1),0)+MAX(MIN(A1-40,B1),0)

--- "Red_Goldfish" wrote:
I need a formula to calculate redundancy payments in a table. The redundancy
payment due to each employee depends on his or her age and length of service
(up to twenty years). This determines the number of weeks pay due. I want to
calculate, automatically, the number of weeks pay due.

To calculate the number of weeks pay due, one should use the following
amounts €“

0.5 week's pay for each full year of service where age during year less than
22 1.0 week's pay for each full year of service where age during year is 22 or
above, but less than 41

(I have, within the spreadsheet I am using, a cell which already has
calculated the age - can you, in the answer, use A1 to refer to this cell,
thanks). 1.5 weeks' pay for each full year of service where age during year is
41+ I believe this can be done, by a single cell formula using if, greater than,
less than etc., but I cant get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

I forgot something important. That formula gives you one
week's pay for each segment of the calculation instead of
half a week; you need to divide the result by 2 to get the
result you're after:

=(B1+MAX(MIN(A1-22,B1),0)+MAX(MIN(A1-40,B1),0))/2

--- "Bob Bridges" wrote:
As I understand your description, A1 contains the employee's
PRESENT age. But that doesn't help you; you need his age ON
JANUARY 1, because if he's 22 now but was 21 on Jan 1, he
should get half a week's pay for this year. And I'll assume for
the rest of this explanation that the age you refer to is his age
on Jan 1 of 2009, rather than of some other year; you can use
any year but the subsequent calculations have to know which
one. The best formula that I can think of off-hand for
calculating this value is "=2009-YEAR(<birthday-1)-1"; that
gives you an age of 0 for anyone born last year, unless he was
born on Jan 1 in which case it gives you an age of 1 this year.

Second, in addition to the employee's age on 2009-01-01 you
need another datum: the number of years he's been working
for you. Otherwise you don't know whether to pay him for the
year 1986; using his age you can tell that he turned 41 that
year, but you still don't want to give him that week's salary if
he didn't come to work for your company until 1991. I'll
assume B1 contains the number of calendar years during
which he worked for your company. Understand that this isn't
the number of full years he's worked; if he started work
November of last year, your calculation is to pay him for both
2008 and 2009.

Third: If an employee was born February 1, 1945, then he's
64 now but was 63 at the beginning of this year (so A1
contains 63). But if he first came to work for your company
on June 1, 1986, does he get a week's pay for that year, or
a week and a half? Because during part of that year he was
40 years old, so he ought to get a week's pay; but he was
already 41 by the time he came to work for you, so maybe
he'll argue (in court) that he should get a week and a half
for that year, because he was never 40 while working for
you. Just a thought; I'll continue as though he is to get just
a week's pay for that year.

Ok, so we have in A1 the employee's age as of Jan 1 this
year, and in B1 the number of years during which he was
an employee. The obvious way to do this calculation is to
figure out how many of those years he was 22 or less, and
how many between 23 and 40, and how many 41 or
greater, and multiply each of the figures by 0.5, 1.0 and
1.5 respectively; the sum is the number of weeks' salary
he gets paid. But it turns out the formula is simpler if you
do it this way: Give him half a week's salary for all the
years in which he's worked. Give him another half week's
salary for all the years during which his age was 23 or
greater. Give him another half week's salary for all the
years in which his age was 41 or greater. That formula
looks like this:

=B1+MAX(MIN(A1-22,B1),0)+MAX(MIN(A1-40,B1),0)

--- "Red_Goldfish" wrote:
I need a formula to calculate redundancy payments in a table. The redundancy
payment due to each employee depends on his or her age and length of service
(up to twenty years). This determines the number of weeks pay due. I want to
calculate, automatically, the number of weeks pay due.

To calculate the number of weeks pay due, one should use the following
amounts €“

0.5 week's pay for each full year of service where age during year less than
22 1.0 week's pay for each full year of service where age during year is 22 or
above, but less than 41

(I have, within the spreadsheet I am using, a cell which already has
calculated the age - can you, in the answer, use A1 to refer to this cell,
thanks). 1.5 weeks' pay for each full year of service where age during year is
41+ I believe this can be done, by a single cell formula using if, greater than,
less than etc., but I cant get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

Hi,

I'm not sure I understand, it seems to me that you need to know the age when
they started and there current age. And you need to assume that there were
no breaks in service. The only thing we know is their age, in cell A1.

That doesn't help because they may be 45 and they may have started when they
were 44. So we need to know their start date so we can calculate their years
of service.

What about rehires?

I don't believe it is possible to answer this without that info.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Red_Goldfish" wrote:

I need a formula to calculate redundancy payments in a table. The redundancy
payment due to each employee depends on his or her age and length of service
(up to twenty years). This determines the number of weeks pay due. I want to
calculate, automatically, the number of weeks pay due.

To calculate the number of weeks pay due, one should use the following
amounts €“

0.5 week's pay for each full year of service where age during year less than
22
1.0 week's pay for each full year of service where age during year is 22 or
above, but less than 41

(I have, within the spreadsheet I am using, a cell which already has
calculated the age - can you, in the answer, use A1 to refer to this cell,
thanks).
1.5 weeks' pay for each full year of service where age during year is 41+
I believe this can be done, by a single cell formula using if, greater than,
less than etc., but I cant get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

Shane Devenshire is right, Red_Goldfish; I ignored the issue of rehires in my
solution, just assuming the service was unbroken, but unless you're paying
your employees only for the most recent term of service you'll probably have
to take that into account too. It can still be done, but you may need to
work harder at calculating the value in B2 that I used in my post.

--- "Shane Devenshire" wrote:
...it seems to me that you need to know the age when
they started and there current age. And you need to
assume that there were no breaks in service. The only
thing we know is their age, in cell A1.