ASSIGNMENT – 4 – ANSWER KEY

Salary Sheet

Completed Salary Table

Name	Department	Post	Basic	DA 2.5%	HRA 3.5%	PF 1.5%	Total	Grade
AMIT	COMPUTER	MANAGER	12,000	300	420	180	12,540	C
NEHA	COMPUTER	SUPERVISOR	8,500	212.50	297.50	127.50	8,882.50	C
ROHAN	COMPUTER	ACCOUNTANT	6,500	162.50	227.50	97.50	6,792.50	C
PRIYA	ELECTRICAL	GUARD	5,500	137.50	192.50	82.50	5,747.50	C
VIKAS	ELECTRICAL	CASHIER	9,000	225	315	135	9,405	C
KIRAN	ELECTRICAL	ACCOUNTANT	10,500	262.50	367.50	157.50	10,972.50	C
ARUN	FINANCE	MANAGER	15,000	375	525	225	15,675	B
POOJA	FINANCE	GUARD	6,000	150	210	90	6,270	C
RAVI	FINANCE	SUPERVISOR	11,000	275	385	165	11,495	C
SIMRAN	COMPUTER	GUARD	5,000	125	175	75	5,225	C

Q.1 COUNTIF – Department-wise Employees

Assuming Department is in B2.

COMPUTER

=COUNTIF(B2:B11,"COMPUTER")

Answer: 4 Employees

ELECTRICAL

=COUNTIF(B2:B11,"ELECTRICAL")

Answer: 3 Employees

FINANCE

=COUNTIF(B2:B11,"FINANCE")

Answer: 3 Employees

Q.2 SUMIF – Computer Department

Formula:

=SUMIF(B2:B11,"COMPUTER",D2:D11)

Calculation:

12,000 + 8,500 + 6,500 + 5,000

Answer:

₹32,000

Q.3 VLOOKUP – ROHAN

Lookup range:

$A$2:$I$11

Post

=VLOOKUP("ROHAN",$A$2:$I$11,3,FALSE)

Answer: ACCOUNTANT

Grade

=VLOOKUP("ROHAN",$A$2:$I$11,9,FALSE)

Answer: C

Q.4 VLOOKUP – ARUN

Post

=VLOOKUP("ARUN",$A$2:$I$11,3,FALSE)

Answer: MANAGER

Grade

=VLOOKUP("ARUN",$A$2:$I$11,9,FALSE)

Answer: B

Q.5 COUNTIF – Post-wise Employees

MANAGER

=COUNTIF(C2:C11,"MANAGER")

Answer: 2 Employees

GUARD

=COUNTIF(C2:C11,"GUARD")

Answer: 3 Employees

Q.6 DA, HRA & PF

Assuming Basic Salary is in D2:

DA – 2.5%

In E2:

=D2*2.5%

HRA – 3.5%

In F2:

=D2*3.5%

PF – 1.5%

In G2:

=D2*1.5%

Copy all three formulas down to Row 11.

Q.7 SUM – Total Salary

Because PF has to be deducted, use:

=SUM(D2:F2)-G2

Copy down to Row 11.

Total Salary Answers

Name	Total Salary
AMIT	₹12,540
NEHA	₹8,882.50
ROHAN	₹6,792.50
PRIYA	₹5,747.50
VIKAS	₹9,405
KIRAN	₹10,972.50
ARUN	₹15,675
POOJA	₹6,270
RAVI	₹11,495
SIMRAN	₹5,225

Q.8 Nested IF – Grade

In I2:

=IF(H2>20000,"A",IF(H2>15000,"B","C"))

Copy down to I11.

Grade Results

• AMIT → C

• NEHA → C

• ROHAN → C

• PRIYA → C

• VIKAS → C

• KIRAN → C

• ARUN → B

• POOJA → C

• RAVI → C

• SIMRAN → C

Final Grade Summary

A = 0 employees
B = 1 employee
C = 9 employees

Q.9 COUNTA – Total Employees

Formula:

=COUNTA(A2:A11)

Answer:

10 Employees

Q.10 SUMIF – Post-wise Basic Salary

1. Total Basic Salary of MANAGERS

Formula:

=SUMIF(C2:C11,"MANAGER",D2:D11)

Calculation:

₹12,000 + ₹15,000 = ₹27,000

Answer:

₹27,000

2. Total Basic Salary of GUARDS

Formula:

=SUMIF(C2:C11,"GUARD",D2:D11)

Calculation:

₹5,500 + ₹6,000 + ₹5,000 = ₹16,500

Answer:

₹16,500

Final Answers

Question	Answer
Q.1 COMPUTER Employees	4
Q.1 ELECTRICAL Employees	3
Q.1 FINANCE Employees	3
Q.2 Computer Basic Salary	₹32,000
Q.3 ROHAN Post	ACCOUNTANT
Q.3 ROHAN Grade	C
Q.4 ARUN Post	MANAGER
Q.4 ARUN Grade	B
Q.5 MANAGER Count	2
Q.5 GUARD Count	3
Q.9 Total Employees	10
Q.10 MANAGER Basic Salary	₹27,000
Q.10 GUARD Basic Salary	₹16,500