P
Penny
I have tried various avenues to resolve this problem, to no avail, so I'm
hoping that someone on this list may be able to provide assistance.
I need to create a formula that picks up a percentage based on two
variables, age and years of service. I have tried various functions,
including LOOKUP, VLOOKUP, INDEX, AND, OFFSET and MATCH functions, I have
tried nesting formulae within VLOOKUP and HLOOKUP functions, but I cannot
get it to work. I have raised this with commercial Excel trainers, and they
have been unable to give me an answer.
Below is a sample table of what I mean, and an example of what the result
should be based on specified information. What I need to be able to do is
calculate a pension. The pension is a percentage of the person's final
salary. That percentage is determined by their age and their years of
service.
Any help that can be provide would be greatly appreciated.
Yours sincerely,
Penny
Canberra
Years service
20
21
22
23
24
25
50
1.00%
2.00%
3.00%
4.00%
5.00%
6.00%
Age
51
2.00%
3.00%
4.00%
5.00%
6.00%
7.00%
52
3.00%
4.00%
5.00%
6.00%
7.00%
8.00%
53
4.00%
5.00%
6.00%
7.00%
8.00%
9.00%
54
5.00%
6.00%
7.00%
8.00%
9.00%
10.00%
55
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%
Pension for someone aged 52 with 22 years service and final salary
$10,000:$10,000 * 5% = $500
hoping that someone on this list may be able to provide assistance.
I need to create a formula that picks up a percentage based on two
variables, age and years of service. I have tried various functions,
including LOOKUP, VLOOKUP, INDEX, AND, OFFSET and MATCH functions, I have
tried nesting formulae within VLOOKUP and HLOOKUP functions, but I cannot
get it to work. I have raised this with commercial Excel trainers, and they
have been unable to give me an answer.
Below is a sample table of what I mean, and an example of what the result
should be based on specified information. What I need to be able to do is
calculate a pension. The pension is a percentage of the person's final
salary. That percentage is determined by their age and their years of
service.
Any help that can be provide would be greatly appreciated.
Yours sincerely,
Penny
Canberra
Years service
20
21
22
23
24
25
50
1.00%
2.00%
3.00%
4.00%
5.00%
6.00%
Age
51
2.00%
3.00%
4.00%
5.00%
6.00%
7.00%
52
3.00%
4.00%
5.00%
6.00%
7.00%
8.00%
53
4.00%
5.00%
6.00%
7.00%
8.00%
9.00%
54
5.00%
6.00%
7.00%
8.00%
9.00%
10.00%
55
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%
Pension for someone aged 52 with 22 years service and final salary
$10,000:$10,000 * 5% = $500