Difficult function: help needed.
 

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.

Difficult function: help needed.
 

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(A451,2,IF(A441,1.5,1))


Tomek Polak
www.vba.blog.onet.pl

Difficult function: help needed.
 

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.

Difficult function: help needed.
 

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)

"Beertje" wrote in message
...
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.

Difficult function: help needed.
 

Beertje Wrote:
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.

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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=477426

Difficult function: help needed.
 

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(B2DATE(YEAR(A2)+41;MONTH(A2);DAY(A2));B 2-DATE(YEAR(A2)+41;MONTH(A2);DAY(A2));0)+0,5*IF(B2D ATE(YEAR(A2)+51;MONTH(A2);DAY(A2));B2-DATE(YEAR(A2)+51;MONTH(A2);DAY(A2));0)

Regards,
Stefi


€žBeertje€� ezt Ã*rta:

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.

Difficult function: help needed.
 

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.

"Bob Phillips" wrote:

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)

"Beertje" wrote in message
...
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.

Difficult function: help needed.
 

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


"Beertje" wrote:

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



Beertje.

"Bob Phillips" wrote:

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)

"Beertje" wrote in message
...
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.

Difficult function: help needed.
 

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)

"Beertje" wrote in message
...
typo:
A person age 46 working 1 year gets with this formula factor 3.5 while it
should be 1.5


"Beertje" wrote:

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



Beertje.

"Bob Phillips" wrote:

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)

"Beertje" wrote in message
...
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.

Difficult function: help needed.
 

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


--
Kind regards,

Niek Otten

"topola" wrote in message
oups.com...
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(A451,2,IF(A441,1.5,1))


Tomek Polak
www.vba.blog.onet.pl

Difficult function: help needed.
 

Oops! Two linewraps after "years," shouldn't be there

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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


--
Kind regards,

Niek Otten

"topola" wrote in message
oups.com...
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(A451,2,IF(A441,1.5,1))


Tomek Polak
www.vba.blog.onet.pl

Difficult function: help needed.
 

And if you need fractions of years (and ages) use Double instead of Long

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
Oops! Two linewraps after "years," shouldn't be there

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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


--
Kind regards,

Niek Otten

"topola" wrote in message
oups.com...
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(A451,2,IF(A441,1.5,1))


Tomek Polak
www.vba.blog.onet.pl

Difficult function: help needed.
 

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?

Difficult function: help needed.
 

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(T EXT(TODAY()-B2;"yy")))+0,5*MIN(VALUE(TEXT(TODAY()-DATE(YEAR(A2)+51;MONTH(A2);DAY(A2));"yy"));VALUE(T EXT(TODAY()-B2;"yy")))


Regards,
Stefi

€žBeertje€� ezt Ã*rta:

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.

Difficult function: help needed.
 

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

thanks and regards

Beertje.

"Beertje" wrote:

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.