Computer ICT Lab Assignment Lab 5
Course Name: BSCS
Submitted by: - Abdul Aziz
Roll no: - 2430-0213
Submitted to: Miss Aymen Fatima
Submitted
on: - 06/11/2024
1. Calculating Grades
• Task 1.1: In the Grade column, assign a grade based on the average score
in Math, Science, and English: o Use the following criteria: A if average is
85 and above
B if average is between 70 and 84
C if average is below 70
2. Conditional Formatting
• Task 2.1: Highlight all Math scores in green if they are above 80.
• Task 2.2: Highlight Science scores in red if they are below 70.
• Task 2.3: Apply conditional formatting to the Grade column (when it’s filled in
later) so that: o If the grade is "A", it turns blue. o If the grade is "B", it turns
yellow. o If the grade is "C" or lower, it turns orange.
3. Data Validation
•Task 3.1: Apply Data Validation to the Gender column so that only "M" or "F" can
be
entered.
• Task 3.2: In a separate column (e.g., Salutation) add another Data Validation that
allows selection of Mr., Mrs., or Ms.
4. Lookup Functions
Task 4.1: On same sheet, create a table showing student Name and their
Math, Science, and English marks.
Add a cell where the user can enter a Name, and based on that, use
VLOOKUP to display the
Math marks.
Task 4.2: Use HLOOKUP in the Subject column to retrieve the marks of a
student in a particular subject (Math, Science, or English) based on a
given Name.
Task 4.3: In the above tasks 4.1 and 4.2, Create a dropdown list for student
names, and use
VLOOKUP to display their Math, Science, and English marks when a name is
selected from the dropdown and HLOOKUP to retrieve the marks of a student
in a particular subject (Math,
Science, or English) based on a given Name exactly as done in class.
