Salary Sheet Problems

[shujon]

I have my CSE-101 Exam ahead and all I know can do in MS Excel is simple summations and deductions. I would be really grateful if someone helps me out with my following problem.

Consider the following payroll information of a company and write the formula asked.

House Allowance:
40% of Basic salary.

Medical Allowance:
1000 Tk. Whose Basic salary is more than 20000 Tk.
800 Tk. Whose Basic salary is more then or equal to 15000 Tk.
600 Tk. Whose Basic salary is less than 15000 Tk.

[TABLE="width: 654, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee’s ID[/TD]
[TD]Class[/TD]
[TD]Basic Salary[/TD]
[TD]Medical Allowance[/TD]
[TD]House Allowance[/TD]
[TD]Overtime (Hour)[/TD]
[TD]Total Salary[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001-1-23-345[/TD]
[TD]A[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2000-3-56-756[/TD]
[TD]B[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2002-7-56-243[/TD]
[TD]B[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2003-1-10-032[/TD]
[TD]A[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2004-4-67-465[/TD]
[TD]A[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2006-4-67-342[/TD]
[TD]C[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

1. Write a formula to calculate the Medical Allowance for every employee.
2. Write a formula to calculate the House Allowance for every employee.
3. Write formula to find total salary of the employees above (500tk/Hour is given for overtime).
4. Write a formula to calculate the number of employees of A, B and C class employees and show it in a chart.
5. Write a formula to find the sum of Medical allowance of class C employees.
6. Write a formula to find the average House Allowance of class B employees

 

[Toonies]

Medical allowance

=(C2)*0.4

Housing Alowance

=IF(C2<15000,600,IF(C2=15000,800,IF(C2<20000,800,IF(C2>20000,1000,800))))

I'm sure that you are supposed to do homework yourself

 

[shujon]

Thanks I do my own home work but the problem is I find excel & database related works difficult. But I am trying.

Thanks again
Shujon