Hi
I would use a helper sheet. P.e. on sheet Mainboard the user enters
BirthDate, FutureAge and Salary (my advice is define the sells with entered
values as names). Based on BirthDate, CurrentAge is calculated. It will be
easier with further calculations, when FutureAge and CurrentAge are in
format yy.mm - in this case you can calculate CurrentAge as:
=DATEDIF(BirthDate,TODAY(),"Y")+DATEDIF(BirthDate, TODAY(),"YM")/100
On sheet Matrix you have a table
Age, AmountA, AmountB, AmountC
Column Age contains ages in format yy.mm from 35.00 to 75.00, i.e.
35.00
35.01
35.02
....
35.11
35.12
36.00
36.01
etc.
Into cell in row 2 of coumn AmountA enter formula like this
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.1,0.09,0.07,0.06,0)*Salary, 0)
For AmountB
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.08,0.07,0.05,0.04,0)*Salary ,0)
For AmountC
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.06,0.05,0.04,0.03,0)*Salary ,0)
, and copy formulas down for entire table.
Define named values
AmountA=SUM(Matrix!$B:$B)
AmountB=SUM(Matrix!$C:$C)
AmountC=SUM(Matrix!$D:$D)
On sheet Mainboard, enter formular returning amounts into some cells on your
choice. You can hide the sheet Matrix, when you want.
It's done!
Arvi Laanemets
"jackoat" wrote in
message ...
Hi Anne / Arvi
Thank you for your help. I have solved the problem by using many nested
if statements. Anyway, i will also try both your suggested soultions. By
the way, I have another problem. 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 functions can I use? The user would have to enter his
date of birth and the future age in question.
Hope you are able to help me this time as well. 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=392055