Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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? |
#14
|
|||
|
|||
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. |
#15
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAverage Function help needed | Excel Worksheet Functions | |||
Desperate Help needed with a function. | Excel Worksheet Functions | |||
special function help needed | Excel Worksheet Functions | |||
IF function help needed | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |