Bill Williams
New Member
- Joined
- Oct 31, 2010
- Messages
- 27
I am having trouble getting my formulae to work in If statements and would very much appreciate some help
I would have attached a file with a description of issues but not allowed so done my best below.
Thank you kindly
Bill
Sheet 1
<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;" width="107"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;" width="127"> <tbody> </tbody>
Sheet 2
<tbody>
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>
Problem
<tbody>
</tbody><colgroup><col span="3"></colgroup>
I would have attached a file with a description of issues but not allowed so done my best below.
Thank you kindly
Bill
Sheet 1
Wage Earners | |
Evaluation Mark | Bonus Amount |
0 | $ - |
3 | $ 10.00 |
10 | $ 20.00 |
15 | $ 30.00 |
20 | $ 40.00 |
Salary $0 to $54,999.00 | |
Evaluation Mark | Bonus Amount |
0 | $ - |
3 | $ 10.00 |
10 | $ 20.00 |
15 | $ 30.00 |
20 | $ 40.00 |
Salary $55,000.00 to $74,999.00 | |
Evaluation Mark | Bonus Amount |
0 | $ - |
3 | $ 11.00 |
10 | $ 21.00 |
15 | $ 31.00 |
20 | $ 41.00 |
Salary $75,000.00 to $104,999.00 | |
Evaluation Mark | Bonus Amount |
0 | $ - |
3 | $ 12.00 |
10 | $ 22.00 |
15 | $ 33.00 |
20 | $ 44.00 |
<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;" width="107"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;" width="127"> <tbody> </tbody>
Sheet 2
Name | Wage or Salary | Salary | Evaluation Mark | Bonus | |
Steve | W | 50000 | 11 | ??? | |
Fred | S | 56000 | 22 | ??? | |
George | W | 56000 | 3 | ??? | |
Susan | S | 79000 | 16 | ??? |
<tbody>
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>
Problem
Needed | ||
??? (from sheet 2) = Bonus which is decided based on the following criteria | ||
Is 'Wage or Salary' = W or S from sheet 2 | ||
If W | ||
Bonus is based on evaluation mark on wage Earners Table | ||
IE for Steve = W so go to Wage Earners table. Evaluation for Steve from sheet 2 = 11 which corresponds 10 on Wage Eaqrners table | ||
Therefore bonus = 20 | ||
If S then table needs to be selected from one of the 3 other (salary) tables 1st | ||
Then same as above fo find bonus | ||
IE Susan Salary (79,000) / Table = Salary $75…. | ||
Evaluation = 16 | ||
Therefore bonus = 33 |
<tbody>
</tbody><colgroup><col span="3"></colgroup>