Question

.15 Asian Pvt. Ltd. maintains the employee salary details in the worksheet given below:

A B C D E F G H I

1 EmpId EmpName Basic HRA DA PF NetSalary Bonus

2 E101 Ram Chandran 12000

3 E102 Manish Kumar 25000

4 E103 Srikant Naidu 18000

5 E104 Meena Jain 40000

6 E105 Shefali Gupta 38000

7 E106 Abhishek Jain 27000

8 E107 Romilla Babbar 32000

9 E108 Anupriya Singh 21000

Write a formula to do the following tasks:

1. To calculate the HRA as 40% of the basic pay in cells D2:D9

2. To calculate DA as 45% of the basic pay in cells E2:G9

3. To calculate the PF as 10% of the basic pay in cells F2:F9

4. To calculate the net salary as Basic+HRA+DA-PF in calls G2:G9

5. The employees are given a bonus based on the net salary. If the net salary is greater than or equal to 50,000,

display N, otherwise display Y in column H. What formula will enter in cell H2.

Answer 1

1) CALCULATE HRA:

=C2*40%  or =C2*40/100

2) CALCULATE DA

=C2*45%  or =C2*45/100

3) CALCULATE PF

=C2*10%  or =C2*10/100

4) NET SALARY:

=C2+D2+E2-F2  or =sum(C2:D2)-F2

5) =IF(H2>=50000),"Y","N")


After writing formula use FILL HANDLE tool to duplicate formula down to other rows.