Excel Practical Tutorials
1.A Microsoft Excel sheet contains Roll , Name, and Marks in five subjects in column A,B,C,D,E,F and G columns respectively as shown in figure , solve :
I. Using “IF” and “AND” operators, write the syntax to display “PASS” or “FAIL” In H Column (Assumed pass marks in 45 in each subject).
ii. Write formula to find percentage of marks obtained in “I” column.
iii. Also write syntax to assign “Division” for “Pass” in j column (assumed 80% and above “Excellent”, above 60% “First Division” otherwise Second).
I. Result (H2) = IF(AND(C2>=45,D2>=45,E2>=45,F2>=45,G>=45),”Pass”, “Fail”)
ii. Percent (I2) = SUM(C2:G2)/5
iii. Division (j2) = IF(H2=”Pass”, if(I2>=80), ”Excellent”, IF(I2>)=60, “FIRST” , “Second”)),”Fail”)
2. Let us assume the following excel sheet with some sample records
Now write the Excel formula for the following purposes:
I. To calculate HRA and PF (on cell E2) on the basis of following conditions:
-If basic salary is less than of equal, 10000 then HRA will be 20% and PF will be 10% of basic salary. If basic salary is greater than 10000 nut less than or equal to 20000 then HRA will be 15% and PF will be 8% of basic salary, otherwise HRP will be Rs.5000 and PF will be 6.5% of the basic Salary.
II. To calculate TAX (on cell F2) as the Basis of following conditions: if annual Basic salary is >=200,000 then 16% of total annual basic salary otherwise 1% of total annual Basic Salary.
III. To Calculate TAX (on the cell G2) as BasicSalary+HRA-PF-TAX.
IV. To calculate the maximum and average gross salary, assuming that records exist up to 20th row.
V. To Count the Number of employees paying tax.
i. Calculation of HRA on cell D2
=F2 (C2<=10000, 20%C2, IF (C2<=20000, 15%C2, 5000)).
ii. Calculation of PF on cell E2
=IF (C2<=10000, 10%C2, IF (C2<=20000, 8C2, 6.5%*C2)).
iii. Calculation on Tax on cell F2 = IF ((C212)>=200000, 16%C212, 1%C2*12).
iv. Calculation of Gross Salary on cell G2 = C2+D2+E2-(F2/12)
Assuming records exist up to 20th row,
Maximum gross salary on cell H2 = MAX (G2:G20)
Average gross salary on cell I2=AVERAGE (G2:G20)
v. Number of employees paying tax on cell j2=COUNTIF (F2:F20,”>0”)
3. A Shopping Complex offers the following discount according to the purchase of goods by customer. Design the discount formula in excel sheet according to following criteria.
Discount is 25% for the total sales >= 10,000
Discount is 15% for the total sales >= 5,000
Discount is 10% for the total sales >=2,500
Also, calculate net price, Maximum price and average price of purchased goods.
|6||Discount||=IF(E2>=10000, E2*.25, IF(E5>=5000, E5*.15, IF(E5>=2500, E5*.1,0)))|
Let assume that items are to be filled from row 2 to row 4.
Amount (E2) = Qty*Rate=C2*D2
Total (E5) = sum (E2:E4)
Discount (E6) = =IF (E2>=10000, E2*.25, IF (E5>=5000, E5*.15, IF (E5>=2500, E5*.1, 0)))
Net Price (E8) = max (E2:E4)
Average Price (E9) = E7/sum (C2:C4)
4. Excel sheet contain commission table. Row 1 is used for column heading. Column A is used for name. Column B is used for sales, Column C contain commission (Commission is 30% of sales for sales greater than or equal to 25000, commission is 20% of sales for sales lying between 10000 to 25000, otherwise 10 % of sales). Also, calculate the maximum and average sales.
|7||Maximum Sale :||=MAX(B2:B5)|
|8||Average Sale :||=AVERAGE(B2:B5)|
5. Let us assume the following Excel sheet that maintains marks of students in different subject (Assume the full marks is 100)
i. To calculate total and percentage
ii. To calculate division. Use the following conditions for division.
-if percentage is greater than or equal 80. Distinction.
-if percentage is greater than or equal 60 but less than 80. First Division.
-if percentage is greater than or equal 50 but less than 60 second Division.
-if percentage is greater than or equal 40 but less than 50. Third Division otherwise Fail.
iii. To calculate result as pass of fail. If marks in all subjects are greater than or equal 45 then pass otherwise fail
i. For total : G2=sum(C2:F2) For percentage : H2=G2/4
ii.Division : I2=IF(H2>=80,”DISCINCTION”,IF(H2>=60,”FIRST”,IF(H2>=50,”SECOND”,IF(H2>=40,”THIRD”,”FAILED”))))
iii.For Result : j2=IF(AND(C2>=45,D2>45,E2>=45,F2>=45),”PASS”,”FAIL”)