Assignment -1

Roll No Student Name Hindi English Math Physics Chemistry Total Marks Average Marks Grade (A/B)
1 RAM 20 10 14 18 15
2 ASHOK 21 12 14 12 18
3 MANOJ 33 15 7 14 17
4 RAJESH 15 14 8 16 20
5 RANJANA 14 17 10 13 18
6 POOJA 16 8 20 17 15
7 MAHESH 18 19 3 10 14
8 ASHUTOSH 19 20 7 14 18
9 ANIL 22 13 8 12 19
10 PREM 26 12 10 11 27
11 Rajesh 45 12 67 87 23
Q.1 Find the Total Number & Average in all Subjects in Each Student .

Q.2 Find Grade Using If Function - If Average Greater >15 then "A" Grade otherwise "B" Grade

Q.3 How Many Student have "A" and "B" Grade Grade A Grades B

Q.4 What is student Ashok and Manoj Total Number and Average
Total Marks Average
Manoj
Ashok
Q.5 Count how many Students are there in the class
Number of Students in the class

Q.6 How Many Student Hindi & English Subject Number Greater Then > 20 and <15
Marks <15 Marks >20
Hindi
English
 Assignment -2
SRNO ITEMS QTY UNIT RATE TOTAL AMOUNT GRADE
1 AC 20 40000
2 FRIDGE 30 20000
3 COOLER 15 10000
4 WASHING MACHINE 14 15000
5 TV 18 20000
6 FAN 17 2000
7 COMPUTER 10 25000
8 KEYBOARD 5 250
9 MOUSE 25 100
10 PRINTER 30 12000

Q.1 Calculate the total amount against each item

Q.2 How Many Items are there in the list
Q.3 How Many Items qty Greate Then > 20 and Less Then <20
Greater Less than
than 20 20
Items

Q.4 Find if the items are expensive or not. If the total cost of Items is Greater > 500000, then items are "Expensive" otherwise "Lets Buy it".
 Assignment -3
SUBJECT 1ST 2ND 3RD TOTAL AVERAGE GRADE
HINDI 20 15 20
ENGLISH 30 12 15
MATH 15 14 14
PHYSICS 12 17 17
CHEMISTRY 14 18 18
HISTORY 16 25 20
GEO 18 21 22
BIO 17 23 13
BOTANY 20 25 25

Q.1 HOW MANY SUBJECTS ARE THERE ?

Total Number of subjects
Q.2 HOW MANY SUBJECTS IN 1ST PAPER HAVE MARKS GREATER THAN 20
Number of subjects (1st Term) greater than 20
Q.3 SUBJECT HINDI, MATH & ENGLISH TOTAL NO. & GRADE
Total Marks
Botany
Maths
English
Q.5 SUBJECT PHYSICS, MATHS & ENGLISH TOTAL /AVERAGE

Total Marks Average

Physics
Maths
English
 Assignment -4 (Salary Sheet)
NAME DEPARTMENT POST BASIC DA 2.5% HRA 3.5% PF 1.5% TOTAL
RAM COMPUTER MANAGER 5000
SHYAM COMPUTER SUPERVISOR 8000
MANOJ COMPUTER PION 3000
POOJA ELECTRICAL GUARD 6000
RAHUL ELECTRICAL CASHER 8000
RAKESH ELECTRICAL ACCOUNTANT 9000
ASHISH FINANCE MANAGER 10000
MANISH FINANCE GUARD 5000

Q1. Calculate DA @2.5%, HRA @ 3.5%, PF @ 1.5% in the respective columns

Q2. Calculate the Total Salary. Total salary is sum of basic,DA, HRA and less PF
Q.3 HOW MANY EMPLOYEE IN COMPUTER, FINANCE, ELECTRICAL DEPARTMENT
Number of
Employees
Computer
Finance
Electrical

Q.4 What is the total basic salary for Computer Department

Total Salary for Computer Department

 Assignment -5 (Sales Report)
SALESMAN JAN FEB MAR APR MAY JUNE SALES TARGET RESULT
RAMESH 2000 1500 300 1400 1000 1400 10000 A
RAKESH 5000 1200 500 1200 1200 2800 12000 NA
RAHUL 3000 800 1200 3000 1500 3500 18000
POOJA 1000 900 1800 5000 1400 1200 10000
MANOJ 500 1000 2300 8000 1700 1400 12000
ASHOK 800 500 2400 1900 1800 1800 10000
AJEET 1200 1400 1500 700 2500 7000 12000
ALOK 1500 1800 1800 1800 300 1500 10000
Pooja 1800 2500 1700 1500 2800 1800 12000
SURENDRA 200 3000 1900 1200 1500 3000 10000
SHASHI 1600 1200 2000 800 1700 800 10000

Q1. Calculate the total Sales.

Q2. How many salesman are there in total Number of Salesman
Q3, Find the result of the sales man. If sales are greater than target, then the target is achieved other wise target is not achieved.
Q4. What is the result & target of Pooja & Ashok
Target Result
Pooja 22000
Ashok
Q5. How many Salesman have achieved the targets.
 Assignment -8
Use of Formulas - Sum, Average, Counta, Countif, Sumif, & If

Student Name Subject Result

Name Maths English Physics TOTAL PERCENTAGE GRADE
Alan 80 75 85
Bob 50 30 40
Carol 60 70 oor
David 90 85 95
Eric 20 30 Absent
Fred 40 60 80
Gail 10 90 80
Harry 80 70 60
Ian 30 10 20
Janice 10 20 30

Q.1 How Many Student? Use Formula Counta

Q.2 How Many Student Percentage Greather Then > 50 Use Formula Countif
Q.3 Student Bob and Eric Total Number? Use Formula Sumif
Q.4 If Percentage Greater Then >70 Then "Excellent", If Percentage Greater Then >50,"Good", Otherwise "Bed"
Q.5 How Many Student Good and Bed in a list Use Formula Countif
 Assignment -9
Find the First name and the Last Name

Empoyee ID Last Name Pay First Name

110608 Doe John
253072 Cline Andy
352711 Smith John
391006 Pan Peter
392128 Favre Bret
549457 Elway John
580622 Manning Eli
602693 Vick Micheal
611810 Woods Tiger
612235 Jordan Micheal
795574 Stark Tony
830385 Williams Prince
990678 Pitt Brad
Name

Empoyee ID Pay First Name Last Name

602693 $ 84,289
611810 $ 137,670
549457 $ 190,024
612235 $ 122,604
580622 $ 111,709
830385 $ 85,931
253072 $ 168,114
391006 $ 89,627
990678 $ 149,946
795574 $ 145,893
392128 $ 64,757
352711 $ 71,478
110608 $ 121,444
 Assignment -10
Use of Formulas - Counta, Countif, Sumif, & Vlookup

USE OF VLOOKUP
Find the name, SSN, Department, Start Date & Earnings for the Employees (Employee ID) in the
table below
Employee ID Full Name SSN Department Start Date Earnings
EMP001
EMP002
EMP003

Employee ID Full Name SSN Department Start Date Earnings Region

EMP014 Faith K. Macias 845-04-3962 Marketing 27/1/2008 $73,500.00 East Total Earnings 100000
EMP008 Lucian Q. Franklin 345-28-4935 IT/IS 1/3/2008 $80,000.00 West North
EMP012 Blaze V. Bridges 503-53-8350 Marketing 16/4/2008 $95,000.00 South Marketing
EMP004 Denton Q. Dale 858-39-7967 Marketing 3/5/2008 $105,000.00 East
EMP005 Blossom K. Fox 245-18-5890 Engineering 11/7/2008 $90,000.00 North
EMP006 Kerry V. David 873-45-8675 Finance 17/7/2008 $60,000.00 West
EMP007 Melanie X. Baker 190-08-3679 Finance 5/10/2008 $87,000.00 South
EMP002 Adele M. Fulton 352-36-9553 Engineering 28/10/2008 $104,000.00 North
EMP009 Justina O. Jensen 645-74-0451 Marketing 5/11/2008 $380,050.00 South
EMP010 Yoshi J. England 558-53-1475 Marketing 9/12/2008 $93,000.00 North
EMP011 Brooke Y. Mccarty 129-42-6148 IT/IS 12/2/2009 $180,000.00 North
EMP003 Kay G. Colon 796-50-4767 Marketing 19/3/2009 $100,000.00 West
EMP013 Callie I. Forbes 266-48-1339 Human Resources 13/4/2009 $136,000.00 South
EMP001 Zachery O. Mann 663-00-3285 Marketing 28/4/2009 $68,000.00 East

Q.1 How Many Employees are there in the List ?

Finance Marketing
Number of Employees
Q.3 Find employee Blossom K. Fox Department and Earnings?
Department Earnings
Blossom K. Fox
Q.4 What is Employee Blossom K. SSN No.?

SSN No of Blossom K
Q5. What is the amount earned by the Marketing department

Total Amount Earned by Marketing Department

 Assignment -11
CLASSIC FAVORITES TALL GRANDE VENTI
Caffe Latte $2.95 $3.75 $4.15
Cappuccino $2.95 $3.65 $4.15
Caramel Macchiato $3.75 $3.95 $4.25
Caffe Mocha $3.25 $3.95 $4.40
White Chocolate Mocha $3.45 $4.15 $4.55
Caffe Americano $2.00 $2.40 $2.75
Cinnamon Dolce Latte $3.95 $4.75 $5.15
Steamer $2.25 $2.50 $2.75
Drip Coffee $1.75 $1.95 $2.05

Question: What is the column number for the size Grande,Tall, Venti?

Grande
VENTI
TALL

Question: What is the price of a Caffe Mocha, size Grande,Tall, Venti?

Caffe Mocha Grande

Caffe Mocha TALL
Caffe Mocha VENTI
 Assignment -12

Product Name Jan Feb Mar Apr May Jun Jul Aug Total Sales
Apples $2,773 $17,462 $5,954 $1,348 $28,158 $28,799 $25,415 $17,227
Grapefruit $12,908 $3,083 $24,492 $5,825 $1,080 $2,188 $11,087 $15,544
Lemons $6,554 $14,262 $8,377 $24,982 $12,184 $6,430 $21,159 $18,597
Lime $28,913 $1,437 $20,019 $13,026 $26,952 $27,076 $7,040 $10,884
Oranges $4,768 $7,622 $28,918 $27,141 $3,578 $10,092 $15,207 $12,771
Peaches $13,390 $3,611 $6,226 $27,567 $29,962 $2,967 $5,740 $2,137
Pears $17,585 $28,508 $9,614 $17,110 $12,143 $7,365 $24,185 $1,643
Pineapples $22,579 $16,301 $6,469 $22,050 $8,740 $18,806 $3,334 $3,597

Find the total sales

Q.2 Fruits Lemons and Pineapples sales in Mar and Jul ?

Lemons Pineapples
March
July
 Assignment -12

Last Name Sales Revenue Country Quarter

Smith $16,753.00 UK Qtr 3
Johnson $14,808.00 USA Qtr 4
Williams $10,644.00 UK Qtr 2
Jones $1,390.00 USA Qtr 3
Brown $4,865.00 USA Qtr 4
Williams $12,438.00 UK Qtr 1
Johnson $9,339.00 UK Qtr 2
Smith $18,919.00 USA Qtr 3
Jones $9,213.00 USA Qtr 4
Jones $7,433.00 UK Qtr 1
Brown $3,255.00 USA Qtr 2
Williams $14,867.00 USA Qtr 3
Williams $19,302.00 UK Qtr 4
Smith $9,698.00 USA Qtr 1

Create a pivot chart and find the following

1 Country wise revenue generated from Sales

2 Quarter wise revenue generated from Sales
3 Quarter wise , number of person
4 Person wise sales revenue
 Assignment -14

Season Year Type State Sales $

Fall 1998 Amber Ale California $554,536
Fall 1998 Hefeweizen California $540,643
Fall 1998 Pale Ale California $577,548
Fall 1998 Pilsner California $455,905
Fall 1998 Porter California $490,871
Fall 1998 Stout California $446,383
Fall 1998 Amber Ale Oregon $457,726
Fall 1998 Hefeweizen Oregon $347,696
Fall 1998 Pale Ale Oregon $384,541
Fall 1998 Pilsner Oregon $386,420
Fall 1998 Porter Oregon $370,970
Fall 1998 Stout Oregon $430,754
Fall 1998 Amber Ale Washington $500,847
Fall 1998 Hefeweizen Washington $507,070
Fall 1998 Pale Ale Washington $482,346
Fall 1998 Pilsner Washington $608,713
Fall 1998 Porter Washington $150,000
Fall 1998 Stout Washington $500,649
Spring 1998 Amber Ale California $545,780
Spring 1998 Hefeweizen California $440,644
Spring 1998 Pale Ale California $580,359
Spring 1998 Pilsner California $536,225
Spring 1998 Porter California $414,908
Spring 1998 Stout California $377,997
Spring 1998 Amber Ale Oregon $331,289
Spring 1998 Hefeweizen Oregon $384,572
Spring 1998 Pale Ale Oregon $365,813
Spring 1998 Pilsner Oregon $396,338
 Spring 1998 Porter Oregon $453,761
Spring 1998 Stout Oregon $356,538
Spring 1998 Amber Ale Washington $606,332
Spring 1998 Hefeweizen Washington $535,218
Spring 1998 Pale Ale Washington $493,364
Spring 1998 Pilsner Washington $559,100
Spring 1998 Porter Washington $220,350
Spring 1998 Stout Washington $476,975

Q.1 How Many Spring and Fall Seasons are there

Q.2 Create Pivot Table Using Data? Make the report as under

1. Total Sales done season wise

2. Total Sales done statewise
 Assignment -15
Create Pivot Table Using Data Separate Fruit and Vegetables

Order ID Product Category Amount Date Country

1 Carrots Vegetables $4,270 6/1/2016 United States
2 Broccoli Vegetables $8,239 7/1/2016 United Kingdom
3 Banana Fruit $617 8/1/2016 United States
4 Banana Fruit $8,384 10/1/2016 Canada
5 Beans Vegetables $2,626 10/1/2016 Germany
6 Orange Fruit $3,610 11/1/2016 United States
7 Broccoli Vegetables $9,062 11/1/2016 Australia
8 Banana Fruit $6,906 16/1/2016 New Zealand
9 Apple Fruit $2,417 16/1/2016 France
10 Apple Fruit $7,431 16/1/2016 Canada
11 Banana Fruit $8,250 16/1/2016 Germany
12 Broccoli Vegetables $7,012 18/1/2016 United States
13 Carrots Vegetables $1,903 20/1/2016 Germany
14 Broccoli Vegetables $2,824 22/1/2016 Canada
15 Apple Fruit $6,946 24/1/2016 France
16 Banana Fruit $2,320 27/1/2016 United Kingdom
17 Banana Fruit $2,116 28/1/2016 United States
18 Banana Fruit $1,135 30/1/2016 United Kingdom
19 Broccoli Vegetables $3,595 30/1/2016 United Kingdom
20 Apple Fruit $1,161 2/2/2016 United States
21 Orange Fruit $2,256 4/2/2016 France
22 Banana Fruit $1,004 11/2/2016 New Zealand
23 Banana Fruit $3,642 14/2/2016 Canada
24 Banana Fruit $4,582 17/2/2016 United States
25 Beans Vegetables $3,559 17/2/2016 United Kingdom
26 Carrots Vegetables $5,154 17/2/2016 Australia
27 Mango Fruit $7,388 18/2/2016 France
28 Beans Vegetables $7,163 18/2/2016 United States
 29 Beans Vegetables $5,101 20/2/2016 Germany
30 Apple Fruit $7,602 21/2/2016 France

Q.1 How Many Fruits and Vegetables Items are there in the List?
Q.2 Total amount collected from Apple and Banana
Q.3 How Many Products are there in the list?
 Assignment -16
Use of Formulas - Countif, Countifs and Sumifs and Vlookup

Name Gender Country Score

Richard Male United States 74
Jennifer Female United Kingdom 92
James Male United States 65
Lisa Female Canada 82
Sharon Female Australia 50
Elizabeth Female Canada 91
Carol Female United States 96
Mark Male United States 58
John Male Canada 67
Susan Female United Kingdom 54
David Male United States 83

Q.1 How Many Male and Female Candidate in a List?

Q.2 How Many Male Employee in United States?
Q.3 Lisa and John Which Country Belong?
Q.4 United States Male and Female Candidate Scores?
Q.5 How Many Male Candidate Belong Country United State Total Score?
 Assignment -17
Use of Formulas - Vlookup

ID Brand Product ID Brand Product

101 Dell Computer 104
102 Logitech Keyboard 103
103 Logitech Mouse 104
104 HP Printer 101
102
103
101
104
101
102

Find the brand and the product against each ID

 Assignment -18
Use of Formulas - Hlookup

ID 101 102 103 104

Brand Dell Logitech Logitech HP
Product Computer Keyboard Mouse Printer

ID Product Brand
104 Printer HP
103 ? ?
104 ? ?
101 ? ?
102 ? ?
103 ? ?
101 ? ?
104 ? ?
101 ? ?
102 ? ?
 Assignment -19
Use of Formulas - Index with Match to find the

Region Jan Feb Mar

North 5,535 5,414 9,027
South 5,013 5,107 11,667
East 6,597 3,858 1,507
West 3,195 3,654 7,225

East Mar
West Feb
South Jan
North Mar
 Assignment -20
Use of Advance Filter

Reportin
Employe Custome Customer Product Product
Order Id Order Date g Customer Email Product Price Order Total
e Name r Name Contact Name Qty
Manager

Chloe Apple
Jones

Reportin
Order Id Employe g Custome Customer Product Product
Order Date e Name Manager r Name Contact Customer Email Name Product Price Qty Order Total
1 Abhay Aakash Chloe
31/10/2017 Gaurav Harit Jones 919-555-8658 lo@email.com Apple 14 76.00 1064
2 Nisha Aakash Brett
26/6/2017 Kumari Harit Newkirk 919-555-7653 newkb@email.com Banana 3 33.00 99
3 Abhay Aakash Tracey
20/1/2017 Gaurav Harit Beckham 919-555-2314 beck@email.com Banana 3 73.00 219
4 Nishu Aakash Brett
12/12/2017 Kumari Harit Newkirk 919-555-7653 newkb@email.com Apple 14 44.00 616
5 Abhay Aakash Lucinda
18/7/2017 Gaurav Harit George 919-555-4534 lugeo@email.com Apple 14 44.00 616
6 Abhay Aakash Jerrod
14/3/2017 Gaurav Harit Smith 919-555-4564 texj@email.com Banana 3 12.00 36
7 Nishu Aakash Lucinda
21/11/2017 Kumari Harit George 919-555-4534 lugeo@email.com Grapes 33 98.00 3234
8 Nishu Aakash Jerrod
22/5/2017 Kumari Harit Smith 919-555-4564 texj@email.com Grapes 33 42.00 1386
9 Nishu Aakash Chloe
10/11/2017 Kumari Harit Jones 919-555-8658 lo@email.com Apple 14 15.00 210
10 Abhay Aakash Tracey
30/10/2017 Gaurav Harit Beckham 919-555-2314 beck@email.com Pineapple 24 40.00 960
11 Nishu Aakash Brett
30/5/2017 Kumari Harit Newkirk 919-555-7653 newkb@email.com Pineapple 24 71.00 1704
12 Nishu Aakash Brett
13/5/2017 Kumari Harit Newkirk 919-555-7653 newkb@email.com Apple 14 84.00 1176
13 Nisha Aakash Lucinda
2/1/2017 Kumari Harit George 919-555-4534 lugeo@email.com Apple 14 97.00 1358
14 Abhay Aakash Jerrod
16/9/2017 Gaurav Harit Smith 919-555-4564 texj@email.com Banana 3 28.00 84
14 Abhay Aakash Jerrod
16/9/2017 Gaurav Harit Smith 919-555-4564 texj@email.com Banana 3 28.00 84
8 Nishu Aakash Jerrod
22/5/2017 Kumari Harit Smith 919-555-4564 texj@email.com Grapes 33 42.00 1386
17 Vishal Divya Lucinda
30/1/2017 Kumar Sharma George 919-555-4534 lugeo@email.com Pineapple 24 28.00 672
5 Abhay Aakash Lucinda
18/7/2017 Gaurav Harit George 919-555-4534 lugeo@email.com Apple 14 44.00 616
19 Vishal Divya Tracey
28/7/2017 Kumar Sharma Beckham 919-555-2314 beck@email.com Grapes 33 14.00 462
20 Vishal Divya Brett
24/3/2017 Kumar Sharma Newkirk 919-555-7653 newkb@email.com Apple 14 48.00 672
 Assignment -21
Find the salary of the employee with reference to their employee ID

Emp Name Salary Department Emp ID Emp ID Salary

Raju 92,671 Sales Prd001 Prd001
Ramesh 84,120 Operations Prd002 Prd002
Ramila 50,793 Marketing Prd003 Prd003
Rajeshwari 77,833 HR Prd004 Prd004
Karan 58,914 Finance Prd005 Prd005
Rohith 51,096 IT Prd006 Prd006
Jacob 83,735 Marketing Prd007 Prd007
Fleming 74,418 IT Prd008 Prd008
Navya 51,366 Sales Prd009 Prd009
Kavya 54,600 Finance Prd010 Prd010
Santosh 93,509 Operations Prd011 Prd011
Shankar 80,105 Finance Prd012 Prd012
Rajesh 60,802 Marketing Prd013 Prd013
Mahesh 76,260 Sales Prd014 Prd014
Hemaraj 88,965 IT Prd015 Prd015
Nagaraj 63,288 Operations Prd016 Prd016
Johson 45,742 Sales Prd017 Prd017
David 88,354 Marketing Prd018 Prd018
Anderson 76,641 Marketing Prd019 Prd019
Peter 61,678 Sales Prd020 Prd020
 Assignment -22
Use of Formulas - Index & Match

Emp Name Salary Department Emp ID Emp ID Salary

Raju 92,671 Sales Prd001 Prd001
Ramesh 84,120 Operations Prd002 Prd002
Ramila 50,793 Marketing Prd003 Prd003
Rajeshwari 77,833 HR Prd004 Prd004
Karan 58,914 Finance Prd005 Prd005
Rohith 51,096 IT Prd006 Prd006
Jacob 83,735 Marketing Prd007 Prd007
Fleming 74,418 IT Prd008 Prd008
Navya 51,366 Sales Prd009 Prd009
Kavya 54,600 Finance Prd010 Prd010
Santosh 93,509 Operations Prd011 Prd011
Shankar 80,105 Finance Prd012 Prd012
Rajesh 60,802 Marketing Prd013 Prd013
Mahesh 76,260 Sales Prd014 Prd014
Hemaraj 88,965 IT Prd015 Prd015
Nagaraj 63,288 Operations Prd016 Prd016
Johson 45,742 Sales Prd017 Prd017
David 88,354 Marketing Prd018 Prd018
Anderson 76,641 Marketing Prd019 Prd019
Peter 61,678 Sales Prd020 Prd020

Q.1 How Many Employee in Work HR, IT, Marketing Department ?

HR IT Marketing
Employee

Q.2 What is the salary of Santosh

Total Salary of Santosh 93509
Q.3 IT & Marketing Department Total Salary?

IT Marketing
Total Salary
 Assignment -23
Use of Formulas - AND

NAME PHYSICS CHEMISTRY MATHS BIOLOGY PASSED THE EXAM ?

NITIN PASS PASS FAIL PASS
FEROZ PASS fAIL fAIL fAIL
ANITHA PASS FAIL PASS PASS
MADAN PASS PASS PASS PASS
HARRY PASS FAIL PASS PASS
SUMITH FAIL PASS PASS PASS
HARSH PASS PASS PASS FAIL
TRIVEDI PASS PASS FAIL PASS
ASHISH PASS PASS PASS PASS

IN THIS EXAMPLE, IF STUDENT PASSES ALL THE SUBJECT, THEN HE HAS PASSED THE EXAM
 Assignment -24
Product Name Units sold Student Semester Score
A 250.00 John second 90
D 110.00 gary Third 77
E 300.00 Richa second 80
B 50.00 Hari second 65
C 45.00 Tom Third 45
D 23.00 Will Third 55
F 25.00
A 90.00 Average semester score
D 450.00 Second
C 23.00 Third
A 250.00
B 25.00

Average of B
Average of D

Zone City Sales Product Name Units sold

South Chennai 25000 A 250.00
East Patna 12000 D 110.00
North Delhi 4200 E 300.00
North Kanpur 5600 B 50.00
West Gandhinagar 15000 C 45.00
East Hubli 7000 D 23.00
South Manglore 5200 F 25.00
North Chandigarh 6000 A 90.00
West Pune 8500 D 450.00
south Hyderabad 12000 C 23.00
North Meerut 4300 A 250.00
West Nagpur 1200 B 25.00
 Avg of Units Sold above 250
Average of West zone

Avg of Units Sold below 100

 Assignment -27
Emp ID First Name Last Name Full Name
D21 Vishal Mohan
D22 John Mathew
D23 Jamemah Powel
D24 Arundhati Swaminathan
D25 Peter Potter
D26 Roger Williams

WRITE THE FULL NAME USING CONCATENATE

Emp ID First Name Last Name Full Name

D21 Vishal Mohan
D22 John Mathew
D23 Jamemah Powel
D24 Arundhati Swaminathan
D25 Peter Potter
D26 Roger Williams
 Assignment -28
Use of Counta, Countif, Countifs, Vlookup and Index with Match

Employee Database
Date Emp Id Name Designation KRA
1/11/2018 1101 ARUN MIS-OPERATION SALES
1/11/2018 1102 ASHOK OPERATION PHP
3/11/2018 1103 BISWAS SOFTWARE ENG JAVA
3/11/2018 1104 DINESH SME MAILS
3/11/2018 1105 ESHWAR PROGRAMMER C++
6/11/2018 1106 FAHAD PROGRAMMER DOT NET
6/11/2018 1107 GANGA SOFTWARE ASSOCIATE TESTING
8/11/2018 1108 HEMA NETWORK ENG SERVER
8/11/2018 1109 FARZANA SALES EXECUTIVE SALES
8/11/2018 1110 AYESH SALES EXECUTIVE AMAZON
9/11/2018 1111 PRAVEEN SALES EXECUTIVE AMAZON
9/11/2018 1109 FARZANA SALES EXECUTIVE AMAZON
10/11/2018 1112 VISHAL SALES EXECUTIVE GROFFERS
10/11/2018 1113 VISHNU SALES EXECUTIVE PAYTM
10/11/2018 1114 KRISHNA SALES EXECUTIVE PAYTM
10/11/2018 1115 ABHISHEK SALES EXECUTIVE MYNTRA
11/11/2018 1109 FARZANA SALES EXECUTIVE AMAZON
11/11/2018 1116 FARZANA BANU SALES EXECUTIVE MYNTRA
11/11/2018 1116 FARZANA BANU SALES EXECUTIVE MYNTRA
11/11/2018 1116 FARZANA BANU SALES EXECUTIVE MYNTRA
1-Nov-18 1010 VAMSEE KRISHNA BRAND MANAGER MARKETING

Q.1 How Many Employee? Use of Counta

Q.2 How Many Employee in Sales Executive? Use of Countif
Q.3 How Many Employee Sales Executive in Amazone and Myntra? Use of Countifs
Q.4 Employee Dinesh and Vishal Post and KRA? use of Vlookup
Q.5 Employee Abhishek and Hema Emp id? Use of Index with Match
 Assignment -29
Use of Vlookup One Sheet to Another Sheet

Sheet 1- Data
Emp Id First Name Last Name Department Location
101 Donald Patrick Finance Banglore
102 Samuel Samson Marketing Hyderabad
103 Ian Jacob Finance Hyderabad
104 David Johnson Marketing Pune
105 Ian Smith Marketing Banglore
106 Henry Madrid IT Pune
107 Ronica Brave Finance Hyderabad
108 Christine Salvi Marketing Banglore
109 Andrew Baisley IT Hyderabad
110 Erica Irons IT Pune

Sheet 2- Use of Vlookup

Emp Id First Name Last Name Department Location
107
103
102
110
108
106
101
104
109
105
 Assignment -30
Get Pivot Table

Pivot Table Result

Date of
Month Sales Amt
Sale Month (All)
19/1/2018 January 201,440
16/1/2018 January 352,519 Date of Sale Sum of Sales Amt
22/1/2018 January 172,406 12/1/2018 240,000
12/1/2018 January 240,000 16/1/2018 352,519
5/2/2018 February 15,205 19/1/2018 201,440
2/2/2018 February 24,327 22/1/2018 172,406
13/2/2018 February 50,549 2/2/2018 24,327
15/2/2018 February 15,106 5/2/2018 15,205
15/2/2018 February 19,901 9/2/2018 15,205
9/2/2018 February 15,205 13/2/2018 50,549
22/2/2018 February 300,000 15/2/2018 35,007
26/2/2018 February 150,000 22/2/2018 300,000
26/2/2018 February 330,553 26/2/2018 643,835
26/2/2018 February 163,282 27/2/2018 564,030
27/2/2018 February 564,030 28/2/2018 720,256
28/2/2018 February 503,599 Grand Total 3,334,777
28/2/2018 February 15,218
28/2/2018 February 201,440
 Assignment -32
USE OF NESTEDIF
21
Total Numbers
Name Grade earned
Earned
Condition List
John Wilkins 12 90-100 A++
Steve Harrington 87 85-90 A
Edward Clark 94 80-85 B+
Jimmy Chemberlin 84 70-75  B
Alex Wilkins 95 65-70 C+
Patty Scott 78 60-65 C
Andrew Williams 59 55-60 D+
Emilia johnson 43 50-55 D
Anthony Rogers 90 <50 F

Sl # Month Salary
1 January 777,307 Month Number 3
2 February 590,235 Salary #N/A
3 March 585,683
4 April 740,995
5 May 756,502
6 June 626,126
7 July 668,352
8 August 698,558
9 September 562,835
10 October 564,996
11 November 632,549
12 December 702,812
 Assignment -33
Merge Table 1,2 & 3 Using Vlookup

Table 1 Table 2 Table 3

Emp ID Emp Name Emp ID Dept Emp ID Salary
Prd001 Raju Prd001 Sales Prd001 92,671
Prd002 Ramesh Prd002 Operations Prd002 84,120
Prd003 Ramila Prd003 Marketing Prd003 50,793
Prd004 Rajeshwari Prd013 Marketing Prd004 77,833
Prd005 Karan Prd014 Sales Prd005 58,914
Prd006 Rohith Prd015 IT Prd006 51,096
Prd007 Jacob Prd016 Operations Prd015 88,965
Prd008 Fleming Prd017 Sales Prd016 63,288
Prd009 Navya Prd020 Sales Prd017 45,742
Prd010 Kavya Prd004 HR Prd018 88,354
Prd011 Santosh Prd005 Finance Prd019 76,641
Prd012 Shankar Prd006 IT Prd020 61,678
Prd013 Rajesh Prd018 Marketing Prd007 83,735
Prd014 Mahesh Prd019 Marketing Prd008 74,418
Prd015 Hemaraj Prd007 Marketing Prd009 51,366
Prd016 Nagaraj Prd008 IT Prd010 54,600
Prd017 Johson Prd009 Sales Prd011 93,509
Prd018 David Prd010 Finance Prd012 80,105
Prd019 Anderson Prd011 Operations Prd013 60,802
Prd020 Peter Prd012 Finance Prd014 76,260

Result
Emp ID Emp Name Department Salary
Prd001
Prd002
Prd003
Prd004
Prd005
Prd006
Prd007
Prd008
Prd009
Prd010
Prd011
Prd012
Prd013
Prd014
Prd015
Prd016
Prd017
Prd018
Prd019
Prd020
 Assignment -34
Owner Product Class Quantity Sold
Ben A1 4615
Jeff A4 2345
Ben C3 11282
Jeff C14 4159
Jenny A12 7802
Ben B3 8486
Jeff B7 3384
Jenny B11 3422

Total Quantity Sold Total Quantity Sold

By Ben By Jenny

Total Quantity Sold

By Ben & Jenny

Owner Product Class Quantity Sold

Ben A1 4615
Jeff A4 2345
Ben C3 11282
Jeff C14 4159
Jenny A12 7802
Ben B3 8486
Jeff B7 3384
Jenny B11 3422

Total Quantity Sold Total Quantity Sold

By Ben & Jenny By Jeff & Jenny
Total Quantity Sold Total Quantity Sold
By Ben & Jenny By Jeff & Jenny
 Assignment -35
Use of SUMPRODUCT & VLOOKUP

Product Qt. Price

Bulb 20 10
Tube light 15 20
Heater 5 35
CFL 10 6
charger 8 8
RESULT 799 SUMPRODUCT(B5:B9,C5:C9)

USE OF VLOOKUP TABLE 1 TO TABLE 2

Table 1 Table 2
City Code Pin Code City Code Pin Code
415930 U_362 415930
100847 Z_143 100847
592629 F_103 592629
26531 N_148 26531
333812 L_120 333812
261178 G_455 261178
131380 E_195 131380
568870 E_41 568870
357335 O_62 357335
692273 B_277 692273
530883 V_318 530883
124354 J_446 124354
430230 P_18 430230
474386 T_84 474386
62142 J_264 62142
313357 X_296 313357
543456 X_112 543456
74285 J_430 74285
80125 I_159 80125
715820 F_10 715820
 Assignment -36

Emp First Name Dept Region Salary INCENTIVE Bonus TA Dept INCENTIVE Bonus
1 Raja IT north 15625 Sales 100 900
2 Suman Sales east 12500 Mktg 200 800
3 Beena Mktg north 8750 R&D 300 700
4 Seema R&D north 15000 Finance 400 600
5 Julie R&D north 8875 Admin 500 500
6 Neena R&D north 8875 Director 600 400
7 Pankaj Sales north 10625 Personal 700 300
8 Andre Mktg east 11250 CCD 800 200
9 Sujay Finance west 10625
10 Shilpa Admin north 15000
11 Meera Finance east 13750
12 Sheetal Director south 35000
13 K. Sita Personal north 10625 Region TA
14 Priya Personal north 10625 north 100
15 Aalok Admin east 11250 east 200
16 Aakash Admin west 11250 west 300
17 Parvati Mktg north 7500 south 400

1. Find the incentive, bonus and TA for each employee in the table above
Q.2 How many Emloyees are there in Sales and Mktg Department.

Sales Marketing
Number of Employees

Q.3 How much salary in Sales Department.

Total Salary in Sales department
 Assignment -37
CONDITION
EmpcFirst Name Dept Salary Incentive Grade salary sincentive grade
1 Raja Sales 15,625 1 5% A
2 Suman Sales 12,500 5001 10% B
3 Beena Mktg 8,750 10001 15% C
4 Seema R&D 15,000 15001 20% D
5 Julie R&D 8,875 20001 25% E
6 Neena R&D 8,875 25001 30% F
7 Pankaj Sales 10,625 30001 35% G
8 Andre Mktg 11,250 35001 40% H
9 Sujay Finance 10,625
10 Shilpa Admin 15,000
11 Meera Finance 13,750
12 Sheetal Director 35,000
13 K. Sita Personal 10,625
14 Priya Personal 10,625
15 Aalok Admin 11,250
16 Aakash Admin 11,250
17 Parvati Mktg 7,500
18 Farhan Mktg 4,250
19 Satinder Kaur Mktg 5,625
20 Suchita Mktg 5,625
21 Shazia Mktg 5,625
22 Pooja Sales 10,625
23 Jasbinder R&D 5,625
24 Bharat Sales 13,750
25 Rishi Sales 9,375
26 Mala R&D 7,500
27 Hajra Admin 6,875
28 Aalam Personal 10,125
29 Giriraj R&D 11,250
30 Ankur CCD 11,250
31 Tapan CCD 5,000
32 Zarina CCD 6,250
33 Arun Mktg 6,625
34 Pooja Personal 8,375
35 Shilpa Finance 17,500
36 Chitra Finance 17,500
37 Sheetal Finance 17,500
38 Richa Sales 7,500
39 Kirtikar Admin 5,625
40 Pooja R&D 9,500