| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pension contribution
Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
You need to put the code into VBA. From worksheet type Alt-F11 Then in VBA menu go to menu Insert - Module. copy my code and paste into module1. then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2) You may have to change the security setting for macros to medium security level from worksheet menu (2003) Tools - Macro - Security - Medium "owen.cxy" wrote: Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
Hi, After attempting to edit the code to my needs, i have problem understanding the code. Maybe you can see what i mean from this URL and understand what are the criterias needed: http://mycpf.cpf.gov.sg/NR/rdonlyres...B/0/AnnexB.pdf columns 3,5,7,9,11,13 is what i need to compute and display in the table. I already have a Age column "C" and Wage column "D". "Joel" wrote: You need to put the code into VBA. From worksheet type Alt-F11 Then in VBA menu go to menu Insert - Module. copy my code and paste into module1. then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2) You may have to change the security setting for macros to medium security level from worksheet menu (2003) Tools - Macro - Security - Medium "owen.cxy" wrote: Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
Some of the formulas could be simplified but it is easier to keep track of the code if it exactly matches the table. check my code to make sure it is right. Could of made some typos. Function contribution(Age, Wage) contribution = 0 If Wage 500 And Wage <= 750 Then Select Case Age Case Is <= 35 contribution = 0.48 * (Wage - 500) Case 35 To 50 contribution = 0.48 * (Wage - 500) Case 50 To 55 contribution = 0.432 * (Wage - 500) Case 55 To 60 contribution = 0.3 * (Wage - 500) Case 60 To 65 contribution = 0.18 * (Wage - 500) Case Is 65 contribution = 0.12 * (Wage - 500) End Select End If If Wage 750 And Wage <= 1200 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1200 And Wage <= 1500 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1500 Then Select Case Age Case Is <= 35 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 35 To 50 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 50 To 55 contribution = (0.18 * 810) + (0.18 * (Wage - 810)) Case 55 To 60 contribution = (0.125 * 562.5) + (0.125 * (Wage - 562.5)) Case 60 To 65 contribution = (0.075 * 337.5) + (0.075 * (Wage - 337.5)) Case Is 65 contribution = (0.05 * 225) + (0.05 * (Wage - 225)) End Select End If End Function "owen.cxy" wrote: Hi, After attempting to edit the code to my needs, i have problem understanding the code. Maybe you can see what i mean from this URL and understand what are the criterias needed: http://mycpf.cpf.gov.sg/NR/rdonlyres...B/0/AnnexB.pdf columns 3,5,7,9,11,13 is what i need to compute and display in the table. I already have a Age column "C" and Wage column "D". "Joel" wrote: You need to put the code into VBA. From worksheet type Alt-F11 Then in VBA menu go to menu Insert - Module. copy my code and paste into module1. then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2) You may have to change the security setting for macros to medium security level from worksheet menu (2003) Tools - Macro - Security - Medium "owen.cxy" wrote: Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
Thank you for your prompt response, another thing i would like to ask is i'm using Excel 2007 and i do not know how to configure the macros, I do not know how to apply this function, do i type "=contribution" into the column that i want it to display to? You quoted this below, what does this mean, does it mean 3 contribution function? "then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2)" Then how does the function find out where to get the age and wage, do i need to input the column name into the function? "Joel" wrote: Some of the formulas could be simplified but it is easier to keep track of the code if it exactly matches the table. check my code to make sure it is right. Could of made some typos. Function contribution(Age, Wage) contribution = 0 If Wage 500 And Wage <= 750 Then Select Case Age Case Is <= 35 contribution = 0.48 * (Wage - 500) Case 35 To 50 contribution = 0.48 * (Wage - 500) Case 50 To 55 contribution = 0.432 * (Wage - 500) Case 55 To 60 contribution = 0.3 * (Wage - 500) Case 60 To 65 contribution = 0.18 * (Wage - 500) Case Is 65 contribution = 0.12 * (Wage - 500) End Select End If If Wage 750 And Wage <= 1200 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1200 And Wage <= 1500 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1500 Then Select Case Age Case Is <= 35 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 35 To 50 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 50 To 55 contribution = (0.18 * 810) + (0.18 * (Wage - 810)) Case 55 To 60 contribution = (0.125 * 562.5) + (0.125 * (Wage - 562.5)) Case 60 To 65 contribution = (0.075 * 337.5) + (0.075 * (Wage - 337.5)) Case Is 65 contribution = (0.05 * 225) + (0.05 * (Wage - 225)) End Select End If End Function "owen.cxy" wrote: Hi, After attempting to edit the code to my needs, i have problem understanding the code. Maybe you can see what i mean from this URL and understand what are the criterias needed: http://mycpf.cpf.gov.sg/NR/rdonlyres...B/0/AnnexB.pdf columns 3,5,7,9,11,13 is what i need to compute and display in the table. I already have a Age column "C" and Wage column "D". "Joel" wrote: You need to put the code into VBA. From worksheet type Alt-F11 Then in VBA menu go to menu Insert - Module. copy my code and paste into module1. then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2) You may have to change the security setting for macros to medium security level from worksheet menu (2003) Tools - Macro - Security - Medium "owen.cxy" wrote: Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pension contribution
The UDF (User Defined function) function works exactly like any other worksheet function except you call it =contribution() instead of a function like =sum(). So the results will appear in the cell where the formula is located and the age will be a cell in column c and the wage will be a cell in colun d. =contribution(C3,D3) "owen.cxy" wrote: Thank you for your prompt response, another thing i would like to ask is i'm using Excel 2007 and i do not know how to configure the macros, I do not know how to apply this function, do i type "=contribution" into the column that i want it to display to? You quoted this below, what does this mean, does it mean 3 contribution function? "then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2)" Then how does the function find out where to get the age and wage, do i need to input the column name into the function? "Joel" wrote: Some of the formulas could be simplified but it is easier to keep track of the code if it exactly matches the table. check my code to make sure it is right. Could of made some typos. Function contribution(Age, Wage) contribution = 0 If Wage 500 And Wage <= 750 Then Select Case Age Case Is <= 35 contribution = 0.48 * (Wage - 500) Case 35 To 50 contribution = 0.48 * (Wage - 500) Case 50 To 55 contribution = 0.432 * (Wage - 500) Case 55 To 60 contribution = 0.3 * (Wage - 500) Case 60 To 65 contribution = 0.18 * (Wage - 500) Case Is 65 contribution = 0.12 * (Wage - 500) End Select End If If Wage 750 And Wage <= 1200 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1200 And Wage <= 1500 Then Select Case Age Case Is <= 35 contribution = 120 + (0.24 * (Wage - 750)) Case 35 To 50 contribution = 120 + (0.24 * (Wage - 750)) Case 50 To 55 contribution = 108 + (0.216 * (Wage - 750)) Case 55 To 60 contribution = 75 + (0.15 * (Wage - 750)) Case 60 To 65 contribution = 45 + (0.09 * (Wage - 750)) Case Is 65 contribution = 30 + (0.06 * (Wage - 750)) End Select End If If Wage 1500 Then Select Case Age Case Is <= 35 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 35 To 50 contribution = (0.2 * 900) + (0.2 * (Wage - 900)) Case 50 To 55 contribution = (0.18 * 810) + (0.18 * (Wage - 810)) Case 55 To 60 contribution = (0.125 * 562.5) + (0.125 * (Wage - 562.5)) Case 60 To 65 contribution = (0.075 * 337.5) + (0.075 * (Wage - 337.5)) Case Is 65 contribution = (0.05 * 225) + (0.05 * (Wage - 225)) End Select End If End Function "owen.cxy" wrote: Hi, After attempting to edit the code to my needs, i have problem understanding the code. Maybe you can see what i mean from this URL and understand what are the criterias needed: http://mycpf.cpf.gov.sg/NR/rdonlyres...B/0/AnnexB.pdf columns 3,5,7,9,11,13 is what i need to compute and display in the table. I already have a Age column "C" and Wage column "D". "Joel" wrote: You need to put the code into VBA. From worksheet type Alt-F11 Then in VBA menu go to menu Insert - Module. copy my code and paste into module1. then in worksheet simply =contribution(Age, Wage) really =contribution(42, 5000) or =contribution(A1, A2) You may have to change the security setting for macros to medium security level from worksheet menu (2003) Tools - Macro - Security - Medium "owen.cxy" wrote: Hi, the code below makes sense. However how do i input in as an excel formula for each cell relative to the person? I only know how to start a formula with an "=" sign infront of a function e.g. =SUM. How do i input these into a cell? "Joel" wrote: I think your descriptions are wrong. See if the UDF code below makes sense Function contribution(Age, Wage) contribution = 0 If Wage 50 Then Select Case Age Case Is <= 35 contribution = 0.05 * (Wage - 50) Case 35 To 50 contribution = 0.02 * (Wage - 50) Case Is 50 contribution = 0.01 * (Wage - 50) End Select End If If Wage 500 Then Select Case Age Case Is <= 35 contribution = contribution + (0.1 * (Wage - 500)) Case 35 To 50 contribution = contribution + (0.05 * (Wage - 500)) Case Is 50 contribution = contribution + (0.02 * (Wage - 500)) End Select End If End Function "owen.cxy" wrote: Hi, Im using MS Excel 2007 and nothing else. I have a (column C of each individuals age) and a (column J of each individuals wage). How do I calculate the contribution of each individual based on their age and salary? Below are the criterias. A) Age: 35 years and below 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 5% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 10% of the individuals wage up to the first $500 + 10% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500) B) Age: (Above 35 years) to (50 years and below) 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 2% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 5% of the individuals wage up to the first $500 + 5% of the individuals additional wage. E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500) C) Age: Above 50 years 1) Wage: less than or equal to $50 *Contribution = $0 2) Wage: more than $50 but less than $500 * Contribution = 1% of the difference between (the individuals wage and $50) 3) Wage: more than $500 * Contribution = 2% of the individuals wage up to the first $500 + 2% of the individuals additional wage. Please help me..Thank you so much |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pension Calculations | Excel Discussion (Misc queries) | |||
| Pension computation | Excel Programming | |||
| Percent Contribution | Excel Discussion (Misc queries) | |||
| How to calculate pension contribution on salary? | Excel Discussion (Misc queries) | |||
| Code Contribution | Excel Programming | |||
Linear Mode
