Practical 3 – Creating an Academic Marksheet with Conditional Logic

Course: Advanced Excel with Visualization | Course Code: CAD41VSP201
Practical Number: 3
Topic: Create a marksheet in Excel with specified grading and result conditions.
Duration: 2 Hours

1. Objective

The primary objective of this practical is to enable students to create a comprehensive academic marksheet in Microsoft Excel. Students will learn to:

Set up a structured worksheet with appropriate headings and columns.
Utilize basic arithmetic operations and the SUM function to calculate total marks.
Calculate percentages based on total marks.
Apply nested IF logical functions to assign grades (A, B, C) based on percentage criteria.
Apply the IF logical function to determine pass/fail status based on a percentage threshold.
Format cells for readability and professional presentation.

2. Introduction

Creating a marksheet is a practical application of Excel that combines data entry, arithmetic calculations, and logical decision-making. This practical will guide you through building a marksheet that automatically calculates total marks, percentage, and then assigns grades and determines pass/fail status based on predefined conditions. This prepares you for more complex data analysis and reporting tasks.

3. Pre-requisites

Basic understanding of Microsoft Excel interface.
Ability to open, save, and navigate Excel worksheets.
Basic data entry skills.
Familiarity with basic formulas (like SUM).

4. Software Required

Microsoft Excel

5. Procedure: Practical Steps with Examples

Follow these step-by-step instructions to create the marksheet.

Step 5.1: Opening a New Workbook and Setting Up the Marksheet Structure

1. Open Excel: Launch Microsoft Excel and select Blank workbook.

2. Rename Sheet: Double-click on the Sheet1 tab and rename it to Marksheet.

3. Enter Heading: In cell A1, type the main heading: Dr. G. Y. Pathrikar College.

o Optional: Merge and center cells A1:I1 for a better visual appeal.

4. Enter Column Headers: Starting from cell A3, enter the following column headers:

o A3: Student Name

o B3: Subject 1 | C3: Subject 2 | D3: Subject 3 | E3: Subject 4 | F3: Subject 5

o G3: Total Marks

o H3: Percentage

o I3: Grade

o J3: Result

o Optional: Bold the headers and adjust column widths as needed.

5. Enter Sample Data: Starting from cell A4, enter data for a few students. Assume each subject has a maximum of 100 marks, making the total maximum marks 500.

Student Name	Subject 1	Subject 2	Subject 3	Subject 4	Subject 5	Total Marks	Percentage	Grade	Result
Alice	85	72	90	68	75
Bob	55	60	48	50	62
Charlie	92	88	95	80	89
Diana	30	45	38	25	40
Eva	70	65	72	78	63

Step 5.2: Calculate Total Marks

Now, let’s calculate the sum of marks for each student.

1. Select Cell for Total Marks: Click on cell G4 (where Alice’s total marks will go).

2. Enter SUM Formula: Type the formula =SUM(B4:F4) and press Enter.

o Explanation: This formula adds up the values from Subject 1 (B4) to Subject 5 (F4) for Alice.

3. Copy Formula for All Students:

o Click on cell G4 again.

o Hover your mouse over the small square at the bottom-right corner of the cell (the fill handle) until it turns into a + sign.

o Click and drag the fill handle down to G8 to apply the formula to all students.

Step 5.3: Calculate Percentage

Next, we’ll calculate the percentage based on the total marks. Assuming a maximum of 100 marks per subject, the total maximum marks for 5 subjects is 500.

1. Select Cell for Percentage: Click on cell H4 (where Alice’s percentage will go).

2. Enter Percentage Formula: Type the formula =G4/500 and press Enter.

o Explanation: G4 is the total marks obtained by Alice, and 500 is the total maximum marks possible. This will give you a decimal value.

3. Format as Percentage:

o With H4 still selected, go to the Home tab.

o In the Number group, click the % button.

o Optional: You can use the Increase Decimal or Decrease Decimal buttons in the Number group to adjust the number of decimal places (e.g., to one decimal place as required in a task below).

4. Copy Formula for All Students: Use the fill handle (as in Step 5.2) to drag the formula from H4 down to H8.

Step 5.4: Determine Grade using Nested IF Functions

This step involves applying the given grading conditions:

Per ≥ 80 → Grade A
Per ≥ 60 → Grade B
Per > 45 → Grade C

1. Select Cell for Grade: Click on cell I4 (where Alice’s grade will go).

2. Enter Nested IF Formula: Type the following formula and press Enter.

=IF(H4>=0.8,"A",IF(H4>=0.6,"B",IF(H4>0.45,"C","")))

o Explanation:

§ H4>=0.8 checks if the percentage (in decimal form) is 80% or more. If true, it assigns "A".

§ If false, it moves to the next IF: H4>=0.6 checks if it’s 60% or more. If true, it assigns "B".

§ If false, it moves to the next IF: H4>0.45 checks if it’s more than 45%. If true, it assigns "C".

§ If none of these conditions are met, the formula assigns an empty string "" (meaning no grade, as it falls below the lowest grade category).

3. Copy Formula for All Students: Use the fill handle to drag the formula from I4 down to I8.

Step 5.5: Determine Result (Pass/Fail) using IF Function

Now, we’ll apply the pass/fail condition:

Per ≥ 35 → Pass
Otherwise → Fail

1. Select Cell for Result: Click on cell J4 (where Alice’s result will go).

2. Enter IF Formula: Type the following formula and press Enter.

=IF(H4>=0.35,"Pass","Fail")

o Explanation:

§ H4>=0.35 checks if the percentage (in decimal form) is 35% or more. If true, it assigns "Pass".

§ If false, it assigns "Fail".

3. Copy Formula for All Students: Use the fill handle to drag the formula from J4 down to J8.

Step 5.6: Review and Basic Formatting

Your marksheet should now be complete with calculated Total Marks, Percentage, Grade, and Result for all students.

1. Review Data: Check a few rows to ensure the calculations and logical assignments are correct based on the percentages.

2. Apply Formatting:

o Align Text: Select columns A through J. On the Home tab, in the Alignment group, click Center to center the data.

o Borders: Select the entire data range (e.g., A3:J8). On the Home tab, in the Font group, click the Borders dropdown and choose All Borders to make the table clearer.

o Highlight Heading: You might want to make the Dr. G. Y. Pathrikar College heading larger or a different color.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1. Add More Students: Add at least two more students to the marksheet with their respective subject marks. Ensure all formulas and formatting extend correctly to these new rows.

2. Modify Grading Criteria: Change the grading criteria slightly (e.g., Grade A if Per ≥ 85, Grade B if Per ≥ 65). Update the Grade formula in your marksheet accordingly.

3. Conditional Formatting for Results: Apply Conditional Formatting to the Result column to automatically highlight "Fail" in red fill with red text, and "Pass" in green fill with green text.

o (Hint: Select the Result column, go to Conditional Formatting > Highlight Cells Rules > Text that Contains…)

4. Conditional Formatting for Grades: Apply Conditional Formatting to the Grade column to highlight "A" grades in a distinct color (e.g., light blue fill).

7. Assessment / Deliverables

Submit the completed Excel workbook (.xlsx file) with the Marksheet sheet.
Ensure all calculations are correct and the grading/result conditions are accurately implemented.
Verify that the formatting for readability is applied.