Excel Assignment
Instructions:
Complete all these Tasks on an Excel sheet.
Also when you add these tables in Excel adjust the width and height of rows and columns accor
Format all the tables below by applying the design or color of your choice. You can also apply t
choice to the header of each table.
Save your excel workbook as
TASK 1:
Excel Assignment- Aneela Zahra
Create this table in Excel fill in the red color in the header and Use Function Sum,
Average, IF, COUNT, COUNTA, SUMIF
Roll No Student Name Urdu English Math Physics Chemistry Total
1 Aimen 20 10 14 18 15 77
2 Ayesha 21 12 14 12 18 77
3 Maryam 33 15 7 14 17 86
4 Alina 15 14 8 16 20 73
5 Falak 14 17 10 13 18 72
6 Amreen 16 8 20 17 15 76
7 Nimra 18 19 3 10 14 64
8 Neha 19 20 7 14 18 78
9 Andleeb 22 13 8 12 19 74
10 Warisha 26 12 10 11 27 86
1. Find the Total Number & Average in all Subjects in Each Student.
2. Find Grade Using If Function Hint: Average >15, "A", Average<15, "B", Average <10, "C" Also use decrease decimal
3. How Many Student "A" and "B" Grade Hint: Use COUNTIF
4. Student Ali and Maria's Total Number and Average Hint: Use SUMIF (Create a table below this table in Excel to pe
function)
5. Count how many Students Hint: Use of COUNTA
TASK 2:
Us conditional formatting to Highlight the cost less than 1000 with red color and the top 10% with
dark green.
Items Date Cost
BRAKES 01−01−2016 800.00
TYRES 12−05−2016 2000.00
BRAKES 18−05−2016 500.00
SERVICE 20−05−2016 800.00
SERVICE 10−02−2016 1000.00
WINDOW 08−05−2016 1000.00
TYRES 10−05−2016 1200.00
TYRES 25−05−2016 1500.00
CLUTCH 10−07−2016 1800.00
TYRES 10−01−2016 2000.00
CLUTCH 15−06−2016 1500.00
ws and columns accordingly.
You can also apply the color of your
Function Sum,
Average Grade
15.4 A
15 A
17 A
15 B
14 B
15 A
13 B
16 A TOTAL NO. AND AVE
15 B NIMRA
17 A GRADE A GRADE B TOTAL STUDENTS TOTAL
6 4 10 64
o use decrease decimal for average values
this table in Excel to perform the SUMIF
10% with
TOTAL NO. AND AVERAGE OF
NIMRA MARYAM
AVERAGE TOTAL AVERAGE
12.8 86 17.2
Country - all -
Sum of Sales Quarter
Last Name Qtr 1 Qtr 2 Qtr 3 Qtr 4 Total Result
Brown 3255 4865 8120
Johnson 9339 14808 24147
Jones 7433 1390 9213 18036
Smith 9698 35672 45370
Williams 12438 10644 14867 19302 57251
Total Result 29569 23238 51929 48188 152924
TASK 4:
Use formula VLOOKUP to find the values from Table A to Table B
ID BRAND C Co PRODUCT Column3 Column
DEL o lu Computer 4
101 l m
103 Logitech u n2 Mouse
104 HP Printer
101 DEL Computer
102 Logitech Keyboard
103 Logitech Mouse
101 DEL Computer
104 HP Printer
101 DEL Computer
102 Logitech Keyboard
Use the formula HLOOKUP to find the values from Table A Below to Table B
ID 101 102
Brand Dell Logitech
Product Computer Keyboard
ID Product Brand
104 Printer Logitech
103 Mouse Logitech
104 Printer HP
101 Computer Logitech
102 Keyboard Logitech
103 Mouse Logitech
101 Computer Dell
104 Printer HP
101 Computer Dell
102 Keyboard Logitech
TASK 5:
Use concatenate Function to write full names in 4 column
th
Emp ID First Name Last Name Full Name
D21 Vishal Mohan Vishal Mohan
D22 John Mathew John Mathew
D23 Jamemah Powel Jamemah Powel
D24 Arundhati Swaminathan Arundhati Swaminathan
D25 Peter Potter Peter Potter
D26 Roger Williams Roger Williams
ID
101
102
103
104
103 104
Logitech HP
Mouse Printer
Full Name
Vishal Mohan
John Mathew
Jamemah Powel
Arundhati Swaminathan
Peter Potter
Roger Williams
BRAND PRODUCT Column1
DEL Computer
Logitech Keyboard
Logitech Mouse
HP Printer
