1)AIM:IMPLEMENT FILTER AND SORT OPERATION
QUESTION: Suppose we have a dataset of sales data with the following columns:
Salesperson Region Sales Amount Date
JOHN NORTH 1000 2022-01-01
JANE SOUTH 800 2022-01-05
JOE EAST 1200 2022-01-10
SARAH WEST 900 2022-01-15
JOHN NORTH 1100 2022-01-20
JANE SOUTH 700 2022-01-25
JOE EAST 1300 2022-02-01
SARAH WEST 1000 2022-02-05
A) filter the data to show only sales from the North region.
B) Sort the filtered data by sales amount in descending order.
PROCEDURE :
A)
1. Select the entire dataset (A1:D9).
2. Go to the "Data" tab in the ribbon.
3. Click on the "Filter" button.
4. Click on the filter arrow in the "Region" column header.
5. Select "North" from the filter options.
B)
1. Select the filtered data (A1:D3).
2. Go to the "Data" tab in the ribbon.
3. Click on the "Sort & Filter" button.
4. Select "Sort by Sales Amount" from the sort options.
5. Choose "Descending" as the sort order.
RESULT:
A)
Salesperson Region Sales Amount Date
JOHN NORTH 1000 2022-01-01
JOHN NORTH 1100 2022-01-20
B)
Salesperso Sales
Region Date
n Amount
NORT
JOHN 1100 20-01-2022
H
NORT
JOHN 1000 2022-01-01
H
2)AIM:PERFORM BASIC ARITHMETICOPERATIONS(SUM,DIFFRENCE,PRODUCT,DIVIDES)
A) What is the sum of 5 and 3?
B) What is the difference between 10 and 4
C) What is the product of 4 and 6?
D) What is the quotient of 12 and 3?
PROCEDURE:
A)
1. Open Excel and create a new worksheet.
2. Enter the numbers 5 and 3 in cells A1 and B1, respectively.
3. In cell C1, enter the formula =A1+B1 to calculate the sum.
4. Press Enter to calculate the result.
B)
[Link] the numbers 10 and 4 in cells A2 and B2, respectively.
2. In cell C2, enter the formula =A2-B2 to calculate the difference.
3. Press Enter to calculate the result.
C)
1. Enter the numbers 4 and 6 in cells A3 and B3, respectively.
2. In cell C3, enter the formula =A3*B3 to calculate the product.
3. Press Enter to calculate the result.
D)
1. Enter the numbers 12 and 3 in cells A4 and B4, respectively.
2. In cell C4, enter the formula =A4/B4 to calculate the quotient.
3. Press Enter to calculate the result.
RESULT
A)8
B)6
C)24
D)4
3)AIM:USINGA DATA SET OF STUDENT GRADES IN DIFFERENT SUBJECT CALCULATE
THE AVERAGE GRADE,HIGHEST GRADE(MAX),LOWEST GRADE(MIN),AND THE TOTAL
NUMBER OF GRADES RECORDED(COUNT)
QUESTION: Assume you have the following student grades in different subjects:
STUDENT Math Science English
A 85 78 92
B 90 82 88
C 78 75 80
D 88 85 95
E 92 89 85
A) Calculate the Average Grade for Math
B) Find the Highest Grade in Science
C) Find the Lowest Grade in English
D) Count the Total Number of Grades Recorded for Math
PROCEDURE:
A)
1. Click on an empty cell (e.g., B8).
2. Type =AVERAGE(B2:B6), then press Enter.
3. Excel will calculate the average grade for Math.
B)
1. Click on an empty cell (e.g., B9).
2. Type =MAX(C2:C6), then press Enter.
3. Excel will display the highest grade in Science.
C)
1. Click on an empty cell (e.g., B10).
2. Type =MIN(D2:D6), then press Enter.
4. Excel will show the lowest grade in English
D)
1. Click on an empty cell (e.g., B11).
2. Type =COUNT(B2:B6), then press Enter.
3. Excel will count the total number of grades recorded.
RESULT
A)86.6
B)89
C)80
D)5
4) CREATE A SPREADSHEET WITH A LIST OF FULL NAMES IN ONE COLOUMN. USE TEXT
FUNCTIONS TO SEPARATE THE FIRST NAMES AND LAST NAMES INTO TWO NEW
COLUMNS
QUESTION:
Full Name
John Smith
Jane Doe
Bob Johnson
Alice Williams
PROCEDURE:
1: Create a spreadsheet with a list of full names
2: Use the TEXT-TO-COLUMNS function or FLASH FILL to separate the names
Alternatively, you can use the following formulas:
To extract the first name:
=LEFT(A2,FIND(" ",A2)-1)
To extract the last name:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
Assuming the full names are in column A, starting from cell A2.
Step 3: Copy the formulas down to apply to all cells
RESULT:
Last
Full Name First Name
Name
John Smith John Smith
Jane Doe Jane Doe
Bob
Bob Johnson
Johnson
Alice
Alice Williams
Williams
5)CREATE A SPREADSHEET WITH A LIST OF DATES OF BIRTH AND [Link]
APPROPRIATE DATE AND TIME FUNCTIONS CALCULATE EACH PERSONS CURRENT
AGE
QUESTION: Assume you have the following Dataset
Name Date of Birth
Alice 1995-03-10
Bob 1988-07-22
Charlie 2000-11-15
David 1992-05-05
Emma 1985-12-30
Frank 1978-09-18
Grace 1999-06-25
Harry 1990-01-08
Ivy 2003-04-14
PROCEDURE:
1. Enter Data: Add Names in Column A and Dates of Birth in Column B.
2. Use the Age Formula: In Column C, use the formula
=DATEDIF(B2, TODAY(), "Y")
- B2 = Date of Birth
- TODAY() = Current Date
- "Y" = Returns the difference in years.
4. Drag the formula down to apply it to all rows.
RESULT:
Age
30
36
24
32
39
46
25
35
21
6)PERFORM LOGICAL FUNCIONS ON GIVEN DATASET.
QUESTION: Assume you have the following Dataset
Name Age Department
John 25 Sales
Jane 30 Marketing
Bob 35 IT
Maria 20 HR
David 40 Finance
A) Is John's age greater than 20?
B) Is Jane in the Sales department?
C) Is Bob's age greater than 30 and is he in the IT department?
D) Is Maria in the Sales or Marketing department?
E) Is David's age less than 40?
PROCEDURE:
A)
1. Click on an empty cell (e.g., B8)
2. Type =IF(B2>20, "Yes", "No"), then press Enter.
B)
1. Click on an empty cell (e.g., B9)
2. Type =IF(C2="Sales", "Yes", "No"), then press Enter.
C)
1. Click on an empty cell (e.g., B10)
2. Type =IF(AND(B3>30, C3="IT"), "Yes", "No"), then press Enter.
D)
1. Click on an empty cell (e.g., B10)
2. Type=IF(OR(C4="Sales", C4="Marketing"), "Yes", "No"), then press Enter.
E)
1. Click on an empty cell (e.g., B11)
[Link] =IF(B5<40, "Yes", "No") , then press Enter.
RESULT:
A)YES
B)NO
C)YES
D)NO
E)NO
7) USING A DATASET OF STUDENTS' INFORMATION, CREATE A BAR CHART TO
VISUALIZE THE DATA. CUSTOMIZE THE CHART WITH TITLES, AXIS LABELS, AND
DIFFERENT COLOURS FOR EACH BAR.
QUESTION: Suppose we have following data set.
Student Study Hours per Week
Alice 5
Bob 8
Charlie 10
David 3
Emma 12
Frank 15
Grace 7
Harry 9
Ivy 14
Jack 6
PROCEDURE:
Step 1: Enter the Data
Step 2: Insert a Bar Chart
1. Select the data (both columns, including headers).
2. Click on Insert → Bar Chart → Clustered Bar Chart.
3. A default bar chart will appear.
Step 3: Customize the Chart
Add Title:
- Click on the chart, then click Chart Title → Rename it to "Study Hours per Week”.
Label the Axes:
Click on the chart, then go to Chart Elements (+ sign) → Axis Titles.
- Name the X-axis as "Study Hours" and Y-axis as "Students".
Change Bar Colors:
- Click on a bar, then go to Format → Shape Fill → Choose different colors for each bar.
Adjust the Chart Layout:
- Use the Chart Styles option to apply different visual styles.
Step 4: Finalize and Save
- Ensure all labels and colors are correct.
- Save the Excel file with the chart for future reference.
RESULT:
Study Hour s per Week
16
14
12
10
8
6
4
2
0
Alice Bob Charlie David Emma Frank Grace Harry Ivy Jack
Study Hours per Week
8) A LIST OF 15 STUDENTS WITH THEIR HOURS OF STUDY PER WEEK AND THEIR
CORRESPONDING EXAM SCORES, USE THE CORREL FUNCTION TO CALCULATE THE
CORRELATION COEFFICIENT.
QUESTION: Suppose we have following data set.
Student Study Hours per Week Exam Score (%)
1 5 55
2 8 60
3 10 65
4 3 50
5 12 70
6 15 85
7 7 58
8 9 66
9 14 82
10 6 54
11 11 72
12 13 78
13 4 52
14 16 88
15 2 45
PROCEDURE:
1. Enter the data in two columns, e.g.,
- Column A (A2:A16): Study Hours
- Column B (B2:B16): Exam Scores
2. Use the formula:
=CORREL(A2:A16, B2:B16)
3. Press Enter, and Excel will return the correlation coefficient.
RESULT: 0.987128572
(Close to 1) , it shows a strong positive correlation, meaning more study hours generally lead to
higher scores
9)AIM: Perform a simple linear regression to determine the relationship between advertising spend
and sales.
QUESTION:SUPPOSE WE HAVE FOLLOWING DATA SET
Advertising Spend ($) Sales ($) Advertising Spend ($)
100 10002
200 1500
300 2000
400 2500
500 3000
PROCEDURE:
1. Go to the "Data" tab.
2. Click on "Data Analysis" in the "Analysis" group.
3. Select "Regression" from the list of available tools.
4. Click "OK".
5. Select the range of cells containing your advertising spend data (A1:A6).
6. Select the range of cells containing your sales data (B1:B6).
7. Click "OK".
RESULT
- Coefficients:
-Intercept: 500
- Advertising Spend: 5
- R-squared: 0.98
10)Aim:implement any real life example(linear regression)
QUESTION: SUPPOSE WE HAVE FOLLOWING DATA SET
House Size (sqft) Price ($)
1000 200000
1200 250000
1500 300000
1800 350000
2000 400000
2200 450000
2500 500000
2800 550000
3000 600000
3200 650000
PROCEDURE:
1. Select the data range (A1:B11).
2. Go to the "Data" tab.
3. Click on "Data Analysis" in the "Analysis" group.
4. Select "Regression" from the list of available tools.
5. Click "OK".
6. Select the range of cells containing your house size data (A1:A11).
7. Select the range of cells containing your price data (B1:B11).
8. Click "OK".
RESULT
- Coefficients:
- Intercept: 50000
- House Size: 150
- R-squared: 0.98