Shaheed Sukhdev College of

Business Studies

Advanced Spreadsheet Tools

Assignment 1

Submitted By- Submitted To-

Kamad Maheshwari Ms. Priyanka
Roll No.- 24347 Submission Date-
Class- BBA FIA 2A 25th March 2025
28 January
Q1 Create an if function to calculate the parking charge Use the following to help you:
-If the hours parked are lesss than or equal to 2 hours, display a message saying “Free Parking”
-If the number of hours parked are lesser, the parking fee would be number of hours*parking charge

Q2 Create a formula using if function to calculate the following:

If the beetle length>average length in cell B21, set the text message to LONG If the beetle length is shorter,
set the text message to short

29 January
Q1 What are templates in excel? Provide examples and also some samples.
Templates in excel are pre-made spreadsheets that contain a pre-defined layout, formatting and formulas.
Various templates are present in excel which the user can customize as per preferences, one of them is the
Weekly Planner.

Q2 Provide step by step guide to create your own excel template based on a certain utility.
1. Create a new workbook
2. Insert headers(Name, Date, Amount, Status)
3. Adjust column width and row width
4. Use formulas if needed (eg SUM, AVERAGE, IF STATEMENTS etc.)
5. Add drop-down lists(for predefined choices like “Paid” or “Pending”)
6. Add conditional formatting like color based on values
7. Insert a logo or image wherever possible
Q3) Create a Student Attendance Template in Excel
Step 1: Open a New Excel Workbook
Open Microsoft Excel.
Click on Blank Workbook.
Step 2: Create the Structure
1. Enter column headers in Row 1:
A1: Roll No.
B1: Student Name
C1: Class
D1 - H1: Dates (e.g., 01-Feb, 02-Feb, 03-Feb, etc.).
I1: Total Present
J1: Attendance %
2. Format the headers:
Make them bold.
Increase font size.
Apply a background color.
Step 3: Enter Sample Data
Fill in 5–10 student names with roll numbers.
Leave attendance columns (D–H) blank for now.
Step 4: Use Data Validation for Attendance Marking
1. Select cells D2:H10.
2. Click Data > Data Validation.
3. Choose List and enter: P, A (for Present, Absent).
4. Click OK
Step 5: Add Automatic Attendance Calculation
1. In I2 (Total Present):
Type the formula:
=COUNTIF(D2:H2, "P")
Press Enter, then copy it down for all students.
2. In J2 (Attendance %):
Type the formula:
= (I2 / COLUMNS(D2:H2)) * 100
Press Enter, then copy it down for all students.
Step 6: Apply Conditional Formatting (Optional)
Highlight low attendance in red:
1. Select J2:J10.
2. Click Conditional Formatting > New Rule.
3. Select Format cells based on values.
4. Use the formula:
=J2<75
5. Click Format, choose Red Fill, and press OK.
Step 7: Save as a Template
Click File > Save As.
Choose Excel Template (.xltx).
Name it Student_Attendance_Template and save.
 4 February

Q1 What are the steps of Data Validation?

1. Select the cells you want to validate

2. Go to the Data Tab
3. Click on Data Validation
4. Select an option under Allow
5. Select a condition under Data
6. Set the required values
7. Customize the input message
8. Click Ok
=AND(LEFT(A2,3) = “EMP”, LEN(A2)=6,ISNUMBER(VALUE(RIGHT(A2,3))>0,VALUE(RIGHT(A2,3))<=999)
 11 February
Q1 Basic Math Functions

Task: Use SUM, AVERAGE, MIN, MAX and ROUND functions

Data:

Student Math Science English Total Average Rounded

Avg
Alice 78 85 90 ? ? ?
Bob 88 92 79 ? ? ?
Instructions:

Calculate the total score using SUM()

Find the Average score using AVERAGE()

Round the average to 1 decimal place using ROUND()

 Question 2) Logical operators

Task: Categorize students as ‘Pass’ or ‘Fail’, also make an attendance sheet

Data:

Student Score Pass/Fail Attendance

Alice 78 ? ?
Bob 42 ? ?
Charlie 90 ? ?

Instructions:

Use IF() to return “Pass”, if score is >=50, otherwise “Fail”

Modify with AND() so students pass only if the score is >=50 AND attendance is a 75%

Q3 TEXT FUNCTIONS (LEFT, RIGHT, MID, CONCATENATE, LEN, TRIM)

Task: Extract and clean data from names

Data:
Full Name First Name Last Name Initial’s Name Length Cleaned Name
Alice Johnson ? ? ? ? ? ?
Bob McCartly ? ? ? ? ? ?

INSTRUCTIONS
Extract First Name using LEFT()
Extract Last Name using RIGHT()
Extract Initials using LEFT() & MID()
Find Name Length Using LEN()
Clear extra spaces using TRIM()
 18 February

Q1. Lookup Functions (VLOOKUP & XLOOKUP)

Task: Find the price of an item using VLOOKUP().

) ‘) Data:
| Product ID | Product Name | Price |
| | | |
| 101 | Apple | $1.00 |
| 102 | Banana | $0.50 |
| 103 | Orange | $0.75 |

⬛ Instructions:
Search for a product’s price using VLOOKUP().
Replace VLOOKUP() with XLOOKUP() for better performance.
Q2. Date & Time Functions

Task: Calculate the number of days until an event.

)‘ Data:
| Event | Event Date | Days Left |
| | | |

| Exam | 2025-05-10 | ? |
| Project Due | 2025-04-25 | ? |
⬛ Instructions:
Use TODAY() to get the current date.
Subtract the event date to calculate Days Left.

Q3. Conditional Formatting & Data Validation

)‘ Task: Highlight cells where scores are below 50 in red and above 80 in green.
⬛ Instructions:
Use Conditional Formatting to apply colors dynamically.
Use Data Validation to restrict entry to values between 0-100.
 19 Feb 2025

19 February
Q1. Excel Array Practice Questions
1. Calculate Total Sales:
Write a formula to calculate the total sales for each product across all months.
2. Find Top 3 Products:
Use an array formula to extract the top 3 products based on total sales.
3. Rank Products:
Rank all products from highest to lowest based on total sales.
4. Filter High Performers:
Display only the products whose total sales exceed 2000.
5. Generate a Growth Sequence:
Create a formula to generate a sequence representing a 3-month sales growth trend.
6. Find the Best-Selling Product:
Write a formula to return the name of the product with the highest total sales
1. Calculate the Total Sales for each order by multiplying Quantity and Price per Unit.
2. Find the Total Revenue for each product using an array formula.
3. Determine the Total Quantity Sold using a single formula.
4. Identify the Best-Selling Product (by revenue) using an array formula.
5. Create a list of Unique Products using an array function.
 25 February

Q1
1. Convert the data in the "Student Scores" sheet into a table.
2. Apply formatting to the table (e.g., change colors, bold headers).
3. Sort the data by "Score" in descending order.
4. Filter to show only students who passed.
5. Use a formula to find the average score using structured table references.
6. Apply conditional formatting to highlight scores below 80 in red.
7. Name the table as 'StudentTable' and reference it in a formula.
8. Add a Total Row to automatically calculate the average and count.
9. Use slicers to filter data dynamically.
10. Create a dynamic chart that updates when data is filtered.
Q2)

1. Convert the data in the "Advanced Student Data" sheet into a table.
2. Name the table as 'AdvancedStudentTable' and reference it in formulas.
3. Use structured references to calculate:
a. The average score of all students.
b. The highest and lowest scores.
c. The count of students who passed.
4. Add a column to classify students based on attendance:
- "Excellent" for >90%
- "Good" for 80%-90%
- "Needs Improvement" for <80%
5. Apply conditional formatting:
- Highlight scores below 80 in red.
- Highlight attendance <75% in orange.
6. Use slicers to filter data dynamically by "Subject" and "Passed" status.
7. Create a dynamic line chart showing the trend of scores.
8. Use Data Validation to restrict "Score" values between 0 and 100.
9. Add a calculated column to determine if a student qualifies for an award (Score >90 and Attendance
>85%).
 18 March
Q1
Working with name ranges and functions
Q2) What if analysis by changing the number of sales and number of months to achieve different total costs.

Q3) Scenario Manager

A scenario manager to calculate the net and total amount of sales in different seasons by changing the
discount percentage.
Scenario 1) Off Season
Scenario 2) Festive Season

Scenario 3) Normal Season

Scenario Summary)

.
 19 March
Q1
A Scenario Manager which shows the monthly budget of a person in his current scenario, when gets
promoted as a manager, when his expenses rise as a manager.

Scenario Summary:
Q2
Goal Seek in different types of situations
Q3
Use data analysis add in on excel to get descriptive statistics of a dataset

Dataset:

Analysis: