Difficult function: help needed.

B

Beertje

In need a function which calculates an overall weighing factor based on
person age and the years the person is working at a company.
For each working year factor 1.
For all working years while older then 41 an addtional factor 0.5.
For all working years while older then 51 an additional factor 0.5.

Thanks for any help.
 
T

topola

To be able to use this function easily in each case paste this command
into a Visual Basic Project, Macro Module:

Function Factor(Years)
If Years > 51 Then
Factor = 2
ElseIf Years > 41 Then
Factor = 1.5
Else
Factor = 1
End If
End Function

Therafter you will find this function in Insert > Function > User
Defined Functions

Alternatively you can use a function in cell:
=IF(A4>51,2,IF(A4>41,1.5,1))


Tomek Polak
www.vba.blog.onet.pl
 
B

Beertje

Thanks Tomek, but I probably did not explain the problem very well.

Suppose the person is 54 year, and works 20 years at the company.
The factor is then the total of:
20 years times factor 1 plus
13 years time factor 0.5 for years worked older then or equal to 41 plus
3 years time factor 0.5 for years worked while older then or equal to 51.

This results in factor 28

Beertje.
 
B

Bob Phillips

Beertje,

Try this formula

=(years_with*1)+(MAX(0,age-51)*0.5)+(MAX(age-41,0)*0.5)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
P

Paul Sheppard

Beertje said:
In need a function which calculates an overall weighing factor based on
person age and the years the person is working at a company.
For each working year factor 1.
For all working years while older then 41 an addtional factor 0.5.
For all working years while older then 51 an additional factor 0.5.

Thanks for any help.
Click to expand...

Hi Beertje

If Age is in column A, Years Worked in column C, then in column c use
this formula
=IF(A2<=41,B2,IF(AND(A2>=41,A2<=51),SUM(B2*1.5),B2*2))
Click to expand...
 
S

Stefi

You don't mention what data you have so I supposed the existence of only the
two basically necessary data: A1: date of birth, B1: date of joining the
company

In C2 this formula calculates the factor
=1*TODAY()-B2+0,5*IF(B2>DATE(YEAR(A2)+41;MONTH(A2);DAY(A2));B2-DATE(YEAR(A2)+41;MONTH(A2);DAY(A2));0)+0,5*IF(B2>DATE(YEAR(A2)+51;MONTH(A2);DAY(A2));B2-DATE(YEAR(A2)+51;MONTH(A2);DAY(A2));0)

Regards,
Stefi


„Beertje†ezt írta:
 
B

Beertje

Using this formula for a person at age 61, working 16 years the result is 31.
However the right answer is:
16 times factor 1, plus:
16 times factor 0.5 for worked years older then or equal to age 41, plus:
11 times factor 0.5 for worked years older then or equal to age 51.
Results: 29.5

Or a person age 16 working 1 year gets with this formula factor 3 while it
should be 1.5

Beertje.
 
B

Beertje

typo:
A person age 46 working 1 year gets with this formula factor 3.5 while it
should be 1.5
 
B

Bob Phillips

This formula works with your latest examples

=(years_with*1)+(MIN(years_with,(MAX(0,age-51+1)))*0.5)+(MIN(years_with,MAX(
age-41+1,0))*0.5)

but you seem to have changed the rules lightly from the first example.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
N

Niek Otten

Paste this function:

Function OntslagVergoedingsFactor(age As Long, years As Long)
Dim Years51 As Long
Dim Years41 As Long
Years51 = Application.WorksheetFunction.Min(years,
Application.WorksheetFunction.Max(0, age - 51))
Years41 = Application.WorksheetFunction.Min(years,
Application.WorksheetFunction.Max(0, age - 41))
OntslagVergoedingsFactor = years + Years41 * 0.5 + Years51 * 0.5
End Function
 
T

topola

Data:
Age = 61, Years_With = 16, Lev1= 41, Lev2 = 51.

You wrote:
a) 16 times factor 0.5 for worked years older then or equal to age 41,

b) 11 times factor 0.5 for worked years older then or equal to age 51.
Beertje, does it mean that above Lev1 = 41 you take Years_With to
multiple by the factor 0.5 while above Lev2 = 51 you take
(Age+1-Lev2)=61+1-51=11 to multiple by 0.5? Is my understanding
correct?
 
S

Stefi

Sorry, my first attempt was wrong, this is tested for all your examples:

=1*VALUE(TEXT(TODAY()-B2;"yy"))+0,5*MIN(VALUE(TEXT(TODAY()-DATE(YEAR(A2)+41;MONTH(A2);DAY(A2));"yy"));VALUE(TEXT(TODAY()-B2;"yy")))+0,5*MIN(VALUE(TEXT(TODAY()-DATE(YEAR(A2)+51;MONTH(A2);DAY(A2));"yy"));VALUE(TEXT(TODAY()-B2;"yy")))


Regards,
Stefi

„Beertje†ezt írta:
 
B

Beertje

Thanks all I got it. The one from Niek is the most simple one.

thanks and regards

Beertje.
 
Top