Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to solve this problem?
Hi Let's say an employer pays a worker an additional amount - a certain % of his salary EVERY SINGLE MONTH into three different accounts, A, B and C according to his age. For example, for age 35-45, rate A = 10%, rate B= 8% and rate C=6% age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% age 55 - 65, A= 7%, B = 5 %, C= 4% age 65 - 75 A= 6%, B=4%, C= 3% I am trying to calculate the total amount that an employee would have received from his current age to a specified age. So, how much TOTAL additional amount would the employee have accumulated in each of the three accounts (i.e. total A, total B, total C) from his current age at 47 years 0 month to the specified age at 57 years 3 months? How should I go about it? What Excel functions can I use? The user would have to enter his date of birth and the future age in question. Anyone out there who can help, please! Thanks!! Regards Jack -- jackoat ------------------------------------------------------------------------ jackoat's Profile: http://www.excelforum.com/member.php...o&userid=25800 View this thread: http://www.excelforum.com/showthread...hreadid=392135 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to solve this problem?
Your table is ambiguous.
you show two sets of rates for ages 45, 55 and 65 Since resolution appears to be at the month level, when is an employee considered to be a year older. Always use the first of the month, last of the month, round up for dates 16-31 and down for 1 - 15 - what is the method???? -- Regards, Tom Ogilvy "jackoat" wrote in message ... Hi Let's say an employer pays a worker an additional amount - a certain % of his salary EVERY SINGLE MONTH into three different accounts, A, B and C according to his age. For example, for age 35-45, rate A = 10%, rate B= 8% and rate C=6% age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% age 55 - 65, A= 7%, B = 5 %, C= 4% age 65 - 75 A= 6%, B=4%, C= 3% I am trying to calculate the total amount that an employee would have received from his current age to a specified age. So, how much TOTAL additional amount would the employee have accumulated in each of the three accounts (i.e. total A, total B, total C) from his current age at 47 years 0 month to the specified age at 57 years 3 months? How should I go about it? What Excel functions can I use? The user would have to enter his date of birth and the future age in question. Anyone out there who can help, please! Thanks!! Regards Jack -- jackoat ------------------------------------------------------------------------ jackoat's Profile: http://www.excelforum.com/member.php...o&userid=25800 View this thread: http://www.excelforum.com/showthread...hreadid=392135 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to solve this problem?
Hi Tom Sorry for the ambiguity. Referring to my rates again, let's call the Line 1, Line 2, Line 3 and Line 4 for easy reference for age 35-45, rate A = 10%, rate B= 8% and rate C=6% (L1) age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% (L2) age 55 - 65, A= 7%, B = 5 %, C= 4% (L3 age 65 - 75 A= 6%, B=4%, C= 3% (L4) What I'm trying to mean is the day the employee turns 45, the new rat (L2)applies. (i.e. A=9%, B=7%, C=5%). Similarly, when employee i exactly 55 years old, L3 applies and finally L4 kicks in on his 65t birthday. So now, can the problem be solved? Thanks for your help. Regards Jac -- jackoa ----------------------------------------------------------------------- jackoat's Profile: http://www.excelforum.com/member.php...fo&userid=2580 View this thread: http://www.excelforum.com/showthread.php?threadid=39213 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to solve this problem?
Make a blank sheet the active sheet.
change db = True for a verbose printout leave db = False just for the results. Lightly tested. Sub AA() Dim ar(1 To 4) Dim dt As Date, dtE As Date, dtS As Date Dim dtStart As Date, dtEnd As Date Dim d As Long, dtBirth As Date Dim monPay As Double Dim db As Boolean ' debug print flag db = False ar(1) = Array(0.1, 0.08, 0.06) ar(2) = Array(0.09, 0.07, 0.05) ar(3) = Array(0.07, 0.05, 0.04) ar(4) = Array(0.06, 0.04, 0.03) dtStart = #1/1/1972# dtEnd = Date dtBirth = #10/17/1942# monPay = 1000 d = Day(DateSerial(Year(dtEnd), Month(dtEnd) + 1, 0)) dtE = DateSerial(Year(dtEnd), Month(dtEnd), d) d = Day(DateSerial(Year(dtStart), Month(dtStart) + 1, 0)) dtS = DateSerial(Year(dtStart), Month(dtStart), d) dt = dtS k = 2 If db Then Cells.Clear Range("A1:G1") = Array("Date", "Birth Date", _ "AGE", "Rate Line", "A", "B", "C") End If Do While dt <= dtE age = Evaluate("DateDif(" & _ CLng(dtBirth) & "," & CLng(dt) _ & ",""y"")") idex = Int((age - 35) / 10) + 1 If idex 0 and idex < 5 Then a = a + monPay * ar(idex)(0) b = b + monPay * ar(idex)(1) c = c + monPay * ar(idex)(2) End If If db Then Debug.Print Format(dt, "mmm dd, yyyy"), _ Format(dtBirth, "mmm dd, yyyy"), age, idex Cells(k, 1) = Format(dt, "mmm dd, yyyy") Cells(k, 2) = Format(dtBirth, "mmm dd, yyyy") Cells(k, 3) = age If idex 0 and idex < 5 Then Cells(k, 4) = "L" & idex Cells(k, 5) = monPay * ar(idex)(0) Cells(k, 6) = monPay * ar(idex)(1) Cells(k, 7) = monPay * ar(idex)(2) End If End If d = Day(DateSerial(Year(dt), Month(dt) + 2, 0)) dt = DateSerial(Year(dt), Month(dt) + 1, d) k = k + 1 Loop msg = "A: " & Format(a, "$ #,##0.00") & vbNewLine _ & "B: " & Format(b, "$ #,##0.00") & vbNewLine _ & "C: " & Format(c, "$ #,##0.00") MsgBox msg If db Then Cells(k, 1) = msg Debug.Print msg End If End Sub -- Regards, Tom Ogilvy "jackoat" wrote in message ... Hi Tom Sorry for the ambiguity. Referring to my rates again, let's call them Line 1, Line 2, Line 3 and Line 4 for easy reference for age 35-45, rate A = 10%, rate B= 8% and rate C=6% (L1) age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% (L2) age 55 - 65, A= 7%, B = 5 %, C= 4% (L3) age 65 - 75 A= 6%, B=4%, C= 3% (L4) What I'm trying to mean is the day the employee turns 45, the new rate (L2)applies. (i.e. A=9%, B=7%, C=5%). Similarly, when employee is exactly 55 years old, L3 applies and finally L4 kicks in on his 65th birthday. So now, can the problem be solved? Thanks for your help. Regards Jack -- jackoat ------------------------------------------------------------------------ jackoat's Profile: http://www.excelforum.com/member.php...o&userid=25800 View this thread: http://www.excelforum.com/showthread...hreadid=392135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I solve this problem? Please help me | Excel Discussion (Misc queries) | |||
Please solve this problem. | Excel Worksheet Functions | |||
please solve the problem | Excel Discussion (Misc queries) | |||
Can someone solve a problem for me? | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |