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