Worked Exercises
Practise on the sample sheet (A1:D9) from the formulas topic. Add the formulas below in columns E, F, G and H, copy them down, and check each answer.
Exercise 1: Result sheet
| Column | Header | Formula in row 2 | Row 2 result |
|---|---|---|---|
| E | Result | =IF(C2>=40,"Pass","Fail") | Pass |
| F | Grade | =IF(C2>=90,"A+",IF(C2>=75,"A",IF(C2>=60,"B",IF(C2>=40,"C","F")))) | A |
| G | Rank | =RANK.EQ(C2,$C$2:$C$9,0) | 4 |
| H | Fee share | =D2/SUM($D$2:$D$9) (format as %) | 10.6% |
Summary formulas:
| Question | Formula | Answer |
|---|---|---|
| Number of students | =COUNTA(A2:A9) | 8 |
| Students who passed | =COUNTIF(E2:E9,"Pass") | 8 (Farhan scored exactly 40) |
| Class average | =AVERAGE(C2:C9) | 71.5 |
| Highest and lowest | =MAX(C2:C9) and =MIN(C2:C9) | 92 and 40 |
| Topper's name | =INDEX(A2:A9,MATCH(MAX(C2:C9),C2:C9,0)) | Charu |
| Average marks of Srinagar | =AVERAGEIF(B2:B9,"Srinagar",C2:C9) | 83 |
| Students from Jammu above 70 | =COUNTIFS(B2:B9,"Jammu",C2:C9,">70") | 2 |
| Total fee from Anantnag | =SUMIFS(D2:D9,B2:B9,"Anantnag") | 6000 |
| Students above the class average | =COUNTIF(C2:C9,">"&AVERAGE(C2:C9)) | 4 (78, 92, 88, 83) |
Exercise 2: Salary sheet (assumed rates for practice)
Basic pay in B2 = 30,000. Assume DA = 40% of basic, HRA = 8% of basic, deduction = 10% of basic.
| Item | Formula | Result |
|---|---|---|
| DA | =B2*40% | 12,000 |
| HRA | =B2*8% | 2,400 |
| Gross pay | =B2+C2+D2 (basic + DA + HRA) | 44,400 |
| Deduction | =B2*10% | 3,000 |
| Net pay | =E2-F2 | 41,400 |
Real DA and HRA rates are set by the government and change, so use the current order in real work.
Exercise 3: Quick checks you should be able to do
- Which function counts cells that contain numbers? COUNT.
- What does
=A1&B1do when A1 is "JK" and B1 is "Edu"? JKEdu. - Which function returns today's date and time? NOW.
- What is the shortcut for AutoSum? Alt + =.
- Which reference does not change when copied? Absolute ($A$1).
- Which function finds the position of a value in a range? MATCH.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.