Excel Lab Exercise-1
Mathematical Functions
Objective:
The objective of this exercise is to:
Practically apply Excel Mathematical Functions for analyzing student marks.
Learn to calculate total and average marks systematically.
Use conditional logic (IF, AND) for pass/fail determination.
Learn to assign grades based on calculated averages.
Understand and apply MAX and MIN functions to find highest and lowest
scores.
Calculate course-wise and overall pass percentages using Excel.
Identify top-performing students using sorting and conditional formatting.
Develop hands-on skills in Excel for data analysis and reporting in an
educational context.
Dataset: Marks of 30 Students
Student Name Math Physics Chemistry English
Ananya 35 55 62 38
Rahul 78 69 72 74
Sneha 62 42 65 49
Vikram 39 31 52 47
Pooja 95 88 79 82
Arjun 58 53 60 45
Megha 91 88 34 79
Siddharth 73 66 70 48
Kavya 64 29 67 65
Aman 49 50 45 54
Ishita 80 77 75 33
Rohan 55 61 39 59
Divya 33 39 46 42
Harsh 67 70 69 71
Neha 88 82 77 85
Yash 52 24 59 56
Tanvi 74 68 71 73
Karan 69 45 68 66
Ritika 47 49 53 50
Aditya 53 57 60 58
Simran 60 62 66 64
Nikhil 71 35 73 70
Priya 66 63 67 29
Ravi 38 45 50 43
Swati 83 76 78 81
Vikas 42 47 51 45
Preeti 75 72 74 77
Manish 59 30 62 61
Shreya 68 67 70 67
Deepak 50 52 57 53
Lab Exercise Questions
1. Calculate Total Marks for Each Student using Excel formulas.
2. Determine Overall Pass/Fail for Each Student (pass mark is 50 per subject, student
passes only if all subjects >= 50).
3. Calculate Average Marks for Each Student (only if the student has passed overall).
4. Allocate Grades based on Average Marks (only for students who passed):
- A: 80 and above
- B: 65–79
- C: 50–64
- D: Below 50
5. Find Total Pass Percentage Course-Wise
6. Find Overall Pass Percentage of the Section.
7. Identify the Highest and Lowest Marks in Each Course using MAX and MIN functions.
8. Identify the Top 3 Students Based on Average Marks using sorting and conditional
formatting.
Instructions to Write in Record:
Write each of these steps clearly.
Draw a neat table with sample data (5 students) in your record.
Write formulas and explanation for each step neatly for future reference.
Outcome:
After completing this lab, students will be able to:
✅ Use basic mathematical formulas (SUM, AVERAGE) in Excel for real datasets.
✅ Apply logical functions (IF, AND) for decision-making analysis.
✅ Use conditional grading based on calculated results within Excel.
✅ Calculate and interpret pass percentages at subject and section levels.
✅ Use MAX and MIN functions to find highest and lowest marks in a dataset.
✅ Apply sorting and conditional formatting to identify top students.
✅ Gain confidence in using Excel as a practical data analysis tool for academic and
office tasks.
✅ Build a strong foundation for further Excel labs, including statistical and data
visualization exercises.
