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.

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

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.

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" <Beertje@discussions.microsoft.com> wrote in message
news:683EECBA-8B0B-4380-8B73-B81722601250@microsoft.com...
> 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.
>

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:

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

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" <Beertje@discussions.microsoft.com> wrote in message
> news:683EECBA-8B0B-4380-8B73-B81722601250@microsoft.com...
> > 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.
> >
>
>
>

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" <Beertje@discussions.microsoft.com> wrote in message
> > news:683EECBA-8B0B-4380-8B73-B81722601250@microsoft.com...
> > > 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.
> > >
> >
> >
> >

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" <Beertje@discussions.microsoft.com> wrote in message
news:72584C05-2AAC-4CDA-B933-3E3FDA8BF984@microsoft.com...
> 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" <Beertje@discussions.microsoft.com> wrote in message
> > > news:683EECBA-8B0B-4380-8B73-B81722601250@microsoft.com...
> > > > 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.
> > > >
> > >
> > >
> > >

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" <tpolak@poczta.onet.pl> wrote in message
news:1129706195.487094.282350@f14g2000cwb.googlegroups.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(A4>51,2,IF(A4>41,1.5,1))
>
>
> Tomek Polak
> www.vba.blog.onet.pl
>

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

--
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:u0jAG7I1FHA.732@TK2MSFTNGP10.phx.gbl...
> 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" <tpolak@poczta.onet.pl> wrote in message
> news:1129706195.487094.282350@f14g2000cwb.googlegroups.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(A4>51,2,IF(A4>41,1.5,1))
>>
>>
>> Tomek Polak
>> www.vba.blog.onet.pl
>>
>
>

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

--
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:OMt3V%23I1FHA.736@tk2msftngp13.phx.gbl...
> Oops! Two linewraps after "years," shouldn't be there
>
> --
> Kind regards,
>
> Niek Otten
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:u0jAG7I1FHA.732@TK2MSFTNGP10.phx.gbl...
>> 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" <tpolak@poczta.onet.pl> wrote in message
>> news:1129706195.487094.282350@f14g2000cwb.googlegroups.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(A4>51,2,IF(A4>41,1.5,1))
>>>
>>>
>>> Tomek Polak
>>> www.vba.blog.onet.pl
>>>
>>
>>
>
>

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?

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:

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

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.