Scribd
Upload
16 views2 pages

Excel Lab Question

The document outlines a task to process student academic data from a CSV file, including handling missing values and generating reports. It details specific steps for importing data, performing calculations for eligibility and performance, and assigning grades based on criteria. Finally, it instructs on exporting the filtered results as both PDF and CSV files.

Uploaded by

anumod9b
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views2 pages

Excel Lab Question

The document outlines a task to process student academic data from a CSV file, including handling missing values and generating reports. It details specific steps for importing data, performing calculations for eligibility and performance, and assigning grades based on criteria. Finally, it instructs on exporting the filtered results as both PDF and CSV files.

Uploaded by

anumod9b
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Problem Statement

You are provided with a CSV file containing raw student academic data collected from
multiple departments. The dataset may contain missing values, invalid entries, and special
cases such as medical leave.

Your task is to import the data, process it using logical functions, generate eligibility and
performance reports, and export the final output.

Reg_No Name Dept Internal External Attendance Medical Disciplinary


101 Anu CS 42 48 86 NO NO
102 Ravi CS 35 28 70 YES NO
103 Maya IT 65 92 NO YES
104 Arun AI 48 50 95 NO NO

Lab Tasks
Step 1: Import

• Import the given CSV file into Excel.


• Rename the sheet as Raw_Data.
• Create a new worksheet Processed_Data and copy the imported data.

Step 2: Calculations & Logical Conditions


1⃣ Total Marks (Error-safe)

If any mark is missing, display "Invalid Data".

=IFERROR(D2+E2,"Invalid Data")
2️⃣ Exam Eligibility

A student is Eligible if:

• Attendance ≥ 75 OR
• Medical = "YES"
AND
• NOT involved in disciplinary action

=IF(AND(OR(F2>=75,G2="YES"),NOT(H2="YES")),"Eligible","Not Eligible")
3. Result Status

A student Passes if:

• Total ≥ 50
• AND Exam Eligible = "Eligible"

=IF(AND(I2>=50,J2="Eligible"),"Pass","Fail")
4️⃣ Grade Assignment (IFS)

Grades based on Total:

• ≥ 90 → A+
• ≥ 80 → A
• ≥ 70 → B
• ≥ 60 → C
• Else → Fail

=IFS(I2>=90,"A+",I2>=80,"A",I2>=70,"B",I2>=60,"C",TRUE,"Fail")
5️⃣ Performance Remark (SWITCH)

Based on Grade:

=SWITCH(K2,"A+","Outstanding","A","Excellent","B","Good","C","Average","Fai
l","Poor")
6️⃣ Scholarship Eligibility

Eligible if:

• Grade is A+ or A
• AND Attendance ≥ 85
• AND NOT under disciplinary action

=IF(AND(OR(K2="A+",K2="A"),F2>=85,NOT(H2="YES")),"Approved","Rejected")
7️⃣ Final Academic Status

Use nested logic:

• If Invalid Data → "Review Required"


• If Pass and Scholarship Approved → "Top Performer"
• If Pass only → "Passed"
• Else → "Failed"

=IF(I2="Invalid Data","Review Required",


IF(AND(L2="Pass",M2="Approved"),"Top Performer",
IF(L2="Pass","Passed","Failed")))

Step 3: Export
1. Filter Top Performers.
2. Export the filtered data:
o As PDF
o As CSV

You might also like