Employee Salary Calculation and Analysis: Formulas in Excel – Exam Question

Excel is a powerful spreadsheet software developed by Microsoft, used for data organization, analysis, and visualization. It users to perform complex calculations and automate tasks efficiently. This exam question demonstrates the utilization of Excel formulas to analyze and calculate employee salaries.

Read the following conditions and Answer the given Questions

1. If basic salary is less than 5000 then DA 8%, HRA 15 %, PF 7%, I. Tax 0%
2. If basic pay is greater than and equal to 5000 and less than 10000 then DA 10 %, HRA 18%, PF 12 %, Income Tax 8%
3. If Basic pay is greater than equal to 10000 than DA 15%, HRA 22%, PF 15%, income Tax 15 %.

1. Write a formula to calculate PF in cell E2.
2. Write a formula to calculate income tax in cell G2.
3. Write a formula to calculate Net salary in cell H2.[BS+HRA+DA-PF- I. Tax] 
4. Calculate the maximum and Average Basic Salary.
5. Count the number of employee paying Tax.

	A	B	C	D	E	F	G	H
1	Emply_ID	Name	Basic Salary	HRA	DA	PF	Income Tax	Net Salary
2	Emp001		5000
3	Emp002		8000
4	Emp003		11000
5	Emp004		9950
6	Emp005		14500

1. Formula to calculate PF in cell E2.

E2=IF(C2<5000, C27%, IF(C2<10000, C212%, C2*15%))

• Write a formula to calculate income tax in cell G2.

G2=IF(C2<5000, 0, IF(C2<10000, C28%, C215%))

• . Formula to calculate HRA in cell D2

D2 =IF(C2<5000, C215%, IF(C2<10000, C218%, C2*22%))

• Formula to calculate DA in cell E2:

E2=IF(C2<5000, C28%, IF(C2<10000, C210%, C2*15%))

• Formula to calculate Net salary in cell H2

H2 =C2+D2+E2-F2-G2

The calculations will use the basic salary from column C and compute HRA and DA accordingly before calculating the net salary.

• To calculate the maximum and average Basic Salary

=MAX(C2:C6)

This formula calculates the maximum basic salary

=AVERAGE(C2:C6)

This formula calculates the average basic salary

• To count the number of employees paying tax

=COUNTIF(G2:G6, “>0”)

This formula counts the number of cells in the range G2:G6 that contain values greater than 0, representing the number of employees paying tax.