calculations - table, if, or, >, < etc etc

[Red_Goldfish]

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 can’t get beyond stage 1! I would then multiply the
calculated figure (number of weeks pay due) against the number of years
service.

 

[Sheeloo]

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...

 

[Bob Bridges]

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)

 

[Bob Bridges]

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

 

[Shane Devenshire]

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.

 

[Bob Bridges]

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.