50 Ms Excel Assignments Pdf For Practice Free Download.pdf

Excellent Computer Education(A Professional Training Center) 50 Ms Excel Assignments Pdf For Practice Free Download Assignment -1 Use of Formulas Sum, Average, If, Count, Counta, Countif...

Excellent Computer Education(A Professional Training Center) 50 Ms Excel Assignments Pdf For Practice Free Download Assignment -1 Use of Formulas Sum, Average, If, Count, Counta, Countif & Sumif Roll No Student Name Hindi English Math Physics Chemistry Total Average Grade 1 RAM 20 10 14 18 15 77 15.4 A 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 ? ? ? 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 "A" and "B" Grade Use of Countif Q.4 Student Ashok and Manoj Total Number and Average Use of Sumif Q.5 Count how many Students Use of Counta Q.6 How Many Student Hindi & English Subject Number Grater Then > 20 and 20 and Less Then 500000, Then Items "Expensive" otherwise "Lets Buy it". Assignment -3 Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup SUBJECT 1ST 2ND 3RD TOTAL AVERAGE GRADE HINDI 20 15 20 55 18.33333333 B 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 SUBJECT ? Use of Counta Q.2 HOW MANY SUBJECT 1 PAPER GREATER THAN 20 ? Use of Countif Use of Q.3 SUBJECT HINDI, MATH & ENGLISH TOTAL NO. & GRADE Vlookup Q.4 IF AVE. GREATHER THAN 20 THEN "A", IF AVE. GREATEHR THAN 15 AVE. "B" OTHERWISE "C" Use of Q.5 SUBJECT PHYSICS, MATHS & ENGLISH TOTAL /AVERAGE Vlookup Assignment -4 (Salary Sheet) Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup NAME DEPARTMENT POST BASIC DA 2.5% HRA 3.5% PF 1.5% TOTAL GRADE RAM COMPUTER MANAGER 5000 125 175 50 5250 D 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 ? ? ? ? ? Q.1 HOW MANY EMPLOYEE IN COMPUTER, FINANCE, ELECTRICAL DEPARTMENT Use of Countif Q.2 HOW MANY BASIC SALARY IN COMPUTER DFPARTMENT ONLY? Use of Sumif Q.3 MANOJ, ASHISH POST & GRADE Use of Vlookup Q.4 IF TOTAL SALALRY IS GREATER THEN 20000 THEN "A", IF TOTAL SALARY GREATER THEN 10000 THEN "B", OTHERWISE "C" www.excellentcomputereducation.com Page 2  Excellent Computer Education(A Professional Training Center) Q.5 HOW MANY EMPLOYEE IS MANAGER & GUARD? Use of Countif Assignment -5 (Sales Report) Use of Formulas - Sum, If, Counta, Countif, Sumif, Vlookup, Lookup SALESMAN JAN FEB MAR APR MAY JUNE SALES TARGET RESULT NOT RAMESH 2000 1500 300 1400 1000 1400 7600 10000 ACHIVED RAKESH 5000 1200 500 1200 1200 2800 ? 12000 ? 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 ? AMRIT 1800 2500 1700 1500 2800 1800 ? 12000 ? SURENDRA 200 3000 1900 1200 1500 3000 ? 10000 ? SHASHI 1600 1200 2000 800 1700 800 ? 10000 ? Q.1 How many salesman? Salesman Ajeet Targest & Result? Use of Counta and Vlookup Q.2 If Sales Greater Than Target Then Target Achived otherwise Not Achived Use of If Function Q.3 Rahul Pooja & Ashok Targest & result? Use of Vlookup Q.4 How Many Salesman Achived Target. Use of Countif Q.5 Which Sales Man Jan Sales 2000, & Feb Sales is 2500? Use of Lookup Function Assignment -6 Use of Formulas - Counta, Countif, Sumif, Hlookup, Conditional Formatting 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 www.excellentcomputereducation.com Page 3  Excellent Computer Education(A Professional Training Center) CLUTCH 12-01-2016 1000.00 WINDOW 01-01-2016 1200.00 WINDOW 10-05-2016 1500.00 WINDOW 10-05-2016 1800.00 BRAKES 10-05-2016 1000.00 BRAKES 14-08-2016 1200.00 TYRES 15-08-2016 1500.00 WINDOW 20-08-2016 1800.00 Q.1 HOW MANY ITEMS ? Use of Counta Q.2 HOW MANY BRAKE, WINDOW & TYRES HAVE BEEN BOUGHTS? Use of Countif Q.3 HOW MANY ITEMS COST IS >1000 & BELOW > = 1000? Use of Countif Use of Conditional Q.4 HIGHLIGHT TYRES ITESM & 500 BETWEEN 2000 COST. F Q.5 ITEMS COLOUMN IS 15, 18 & 20 ITEMS NAME? Use of Hlookup Q.6 Total Cost of Window and Brakes Items? Use of Sumif Assignment -7 (Calculate Date of Birth) Use of Formulas - Counta, Countif, Sumif, if & Datedif NAME DATE OF BIRTH DAY MONTH YEAR RAMESH 15-05-1980 10 11 40 RAKESH 20-08-1981 ? ? ? RAHUL 15-10-2003 ? ? ? POOJA 25-05-1990 ? ? ? MANOJ 24-08-1992 ? ? ? ASHOK 23-08-1998 ? ? ? AJEET 12-05-1980 ? ? ? ALOK 18-03-2005 ? ? ? AMRIT 15-08-2007 ? ? ? SURENDRA 25-05-2010 ? ? ? SHASHI 25-08-1993 ? ? ? Use of Q.1 HOW MANY STUDENT? Counta Use of Q.2 STUDENT SURENDRA IS HOW MANY YEAR OLD? Sumif Use of Q.3 HOW MANY STUDENT AGE GREATER THEN 20 YEARS? Countif Q.4 IF STUDENT AGE IS GREATHER THEN 20 THEN STUDENT ADULT / CHILD? Use of Q.5 HOW MANY STUDENT AGE IS >= 25 YEARS? Countif www.excellentcomputereducation.com Page 4  Excellent Computer Education(A Professional Training Center) 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 240 80 EXCELLENT Bob 50 30 40 120 ? ? Carol 60 70 oor 130 ? ? David 90 85 95 270 ? ? Eric 20 30 Absent 50 ? ? Fred 40 60 80 180 ? ? Gail 10 90 80 180 ? ? Harry 80 70 60 210 ? ? Ian 30 10 20 60 ? ? Janice 10 20 30 60 ? ? 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 Use of Formulas - LOOKUP LOOKUP FUNCTION SYNTAX LOOKUP(LOOKUP_value,lookup_vector,[result_vector]) First Last Empoyee ID Last Name First Name Empoyee ID Pay N. N. 110608 Doe John 602693 $ 84,289 Micheal Vick 253072 Cline Andy 611810 $ 1,37,670 ? ? 352711 Smith John 549457 $ 1,90,024 ? ? 391006 Pan Peter 612235 $ 1,22,604 ? ? 392128 Favre Bret 580622 $ 1,11,709 ? ? 549457 Elway John 830385 $ 85,931 ? ? 580622 Manning Eli 253072 $ 1,68,114 ? ? 602693 Vick Micheal 391006 $ 89,627 ? ? 611810 Woods Tiger 990678 $ 1,49,946 ? ? 612235 Jordan Micheal 795574 $ 1,45,893 ? ? 795574 Stark Tony 392128 $ 64,757 ? ? 830385 Williams Prince 352711 $ 71,478 ? ? www.excellentcomputereducation.com Page 5  Excellent Computer Education(A Professional Training Center) 990678 Pitt Brad 110608 $ 1,21,444 ? ? Assignment -10 Use of Formulas - Counta, Countif, Sumif, & Vlookup USE OF VLOOKUP Employee ID Full Name SSN Department Start Date Earnings EMP001 ? ? ? ? ? EMP002 ? ? ? ? ? EMP003 ? ? ? ? ? Employee ID Full Name SSN Department Start Date Earnings EMP001 Faith K. Macias 845-04-3962 Marketing 27-01-2008 $73,500.00 EMP002 Lucian Q. Franklin 345-28-4935 IT/IS 01-03-2008 $80,000.00 EMP003 Blaze V. Bridges 503-53-8350 Marketing 16-04-2008 $95,000.00 EMP004 Denton Q. Dale 858-39-7967 Marketing 03-05-2008 $1,05,000.00 EMP005 Blossom K. Fox 245-18-5890 Engineering 11-07-2008 $90,000.00 EMP006 Kerry V. David 873-45-8675 Finance 17-07-2008 $60,000.00 EMP007 Melanie X. Baker 190-08-3679 Finance 05-10-2008 $87,000.00 EMP008 Adele M. Fulton 352-36-9553 Engineering 28-10-2008 $1,04,000.00 EMP009 Justina O. Jensen 645-74-0451 Marketing 05-11-2008 $3,80,050.00 EMP010 Yoshi J. England 558-53-1475 Marketing 09-12-2008 $93,000.00 EMP011 Brooke Y. Mccarty 129-42-6148 IT/IS 12-02-2009 $1,80,000.00 EMP012 Kay G. Colon 796-50-4767 Marketing 19-03-2009 $1,00,000.00 EMP013 Callie I. Forbes 266-48-1339 Human Resources 13-04-2009 $1,36,000.00 EMP014 Zachery O. Mann 663-00-3285 Marketing 28-04-2009 $68,000.00 Q.1 How Many Employee in a List ? Use of Formula Counta Q.2 How Many Employee work in Finance and Marketing Department? Use of Formula Countif Q.3 Employee Blossom K. Fox Department and Earnings? Use of Vlookup Q.4 Employee Blossom K. SSN No.? Use of Vlookup Q.5 How Many Amount Earnings Marketing Department? Use of Sumif Assignment -11 Use of Formulas - Match and Vlookup With Match CLASSIC FAVORITES TALL GRANDE VENTI Caffe Latte $2.95 $3.75 $4.15 Cappuccino $2.95 $3.65 $4.15 www.excellentcomputereducation.com Page 6  Excellent Computer Education(A Professional Training Center) 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? Use of Match Formula Grande 3 Use of Match Function VENTI ? Use of Match Function TALL ? Use of Match Function Question: What is the price of a Caffe Mocha, size Grande,Tall, Venti? Use of Vlookup with Match Formula Caffe Mocha Grande $3.95 Caffe Mocha TALL ? Caffe Mocha VENTI ? Assignment -12 Use of Formulas - Counta and Vlookup Total Product Name Jan Feb Mar Apr May Jun Jul Aug Sales Apples $2,773 $17,462 $5,954 $1,348 $28,158 $28,799 $25,415 $17,227 $1,27,136 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 ? Q.1 How Many Fruits? Q.2 Fruits Lemons and Pineapples sales in Mar and Jul ? www.excellentcomputereducation.com Page 7  Excellent Computer Education(A Professional Training Center) Assignment -13 Create Pivot Table Using Data Last Name Sales 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 Assignment -14 Use of Formulas - Countif, Countifs and Sumifs Season Year Type State Sales $ Fall 1998 Amber Ale California $5,54,536 Fall 1998 Hefeweizen California $5,40,643 Fall 1998 Pale Ale California $5,77,548 Fall 1998 Pilsner California $4,55,905 Fall 1998 Porter California $4,90,871 Fall 1998 Stout California $4,46,383 Fall 1998 Amber Ale Oregon $4,57,726 Fall 1998 Hefeweizen Oregon $3,47,696 Fall 1998 Pale Ale Oregon $3,84,541 Fall 1998 Pilsner Oregon $3,86,420 Fall 1998 Porter Oregon $3,70,970 Fall 1998 Stout Oregon $4,30,754 Fall 1998 Amber Ale Washington $5,00,847 Fall 1998 Hefeweizen Washington $5,07,070 Fall 1998 Pale Ale Washington $4,82,346 Fall 1998 Pilsner Washington $6,08,713 Fall 1998 Porter Washington $1,50,000 Fall 1998 Stout Washington $5,00,649 Spring 1998 Amber Ale California $5,45,780 www.excellentcomputereducation.com Page 8  Excellent Computer Education(A Professional Training Center) Spring 1998 Hefeweizen California $4,40,644 Spring 1998 Pale Ale California $5,80,359 Spring 1998 Pilsner California $5,36,225 Spring 1998 Porter California $4,14,908 Spring 1998 Stout California $3,77,997 Spring 1998 Amber Ale Oregon $3,31,289 Spring 1998 Hefeweizen Oregon $3,84,572 Spring 1998 Pale Ale Oregon $3,65,813 Spring 1998 Pilsner Oregon $3,96,338 Spring 1998 Porter Oregon $4,53,761 Spring 1998 Stout Oregon $3,56,538 Spring 1998 Amber Ale Washington $6,06,332 Spring 1998 Hefeweizen Washington $5,35,218 Spring 1998 Pale Ale Washington $4,93,364 Spring 1998 Pilsner Washington $5,59,100 Spring 1998 Porter Washington $2,20,350 Spring 1998 Stout Washington $4,76,975 Using Formula Q.1 How Many Spring and Fall Season? Countif Using Formula Q.2 How Many Fall Season in California and Washington? Countifs Q.3 Total Sales if Spring Season in Washngton and California? using Formula Sumifs Using Formula Q.4 How Many Spring Season in Washington only? Countifs Q.5 Create Pivot Table Using Data? Assignment -15 Create Pivot Table Using Data Separate Fruit and Vegetables Order ID Product Category Amount Date Country 1 Carrots Vegetables $4,270 06-01-2016 United States 2 Broccoli Vegetables $8,239 07-01-2016 United Kingdom 3 Banana Fruit $617 08-01-2016 United States 4 Banana Fruit $8,384 10-01-2016 Canada 5 Beans Vegetables $2,626 10-01-2016 Germany 6 Orange Fruit $3,610 11-01-2016 United States 7 Broccoli Vegetables $9,062 11-01-2016 Australia 8 Banana Fruit $6,906 16-01-2016 New Zealand 9 Apple Fruit $2,417 16-01-2016 France 10 Apple Fruit $7,431 16-01-2016 Canada 11 Banana Fruit $8,250 16-01-2016 Germany 12 Broccoli Vegetables $7,012 18-01-2016 United States 13 Carrots Vegetables $1,903 20-01-2016 Germany 14 Broccoli Vegetables $2,824 22-01-2016 Canada www.excellentcomputereducation.com Page 9  Excellent Computer Education(A Professional Training Center) 15 Apple Fruit $6,946 24-01-2016 France 16 Banana Fruit $2,320 27-01-2016 United Kingdom 17 Banana Fruit $2,116 28-01-2016 United States 18 Banana Fruit $1,135 30-01-2016 United Kingdom 19 Broccoli Vegetables $3,595 30-01-2016 United Kingdom 20 Apple Fruit $1,161 02-02-2016 United States 21 Orange Fruit $2,256 04-02-2016 France 22 Banana Fruit $1,004 11-02-2016 New Zealand 23 Banana Fruit $3,642 14-02-2016 Canada 24 Banana Fruit $4,582 17-02-2016 United States 25 Beans Vegetables $3,559 17-02-2016 United Kingdom 26 Carrots Vegetables $5,154 17-02-2016 Australia 27 Mango Fruit $7,388 18-02-2016 France 28 Beans Vegetables $7,163 18-02-2016 United States 29 Beans Vegetables $5,101 20-02-2016 Germany 30 Apple Fruit $7,602 21-02-2016 France Q.1 How Many Fruits and Vegetables Items in a List? Use of Formula Countif Q.2 Total Apple and Banana Amount? Use of Formula Sumif Q.3 How Many Product in a list? Use of Counta Q.4 How Many Apple and Banana Use in Canada & United Kingdom? Use of Countifs Q.5 Apple and Banana Sales in United States ? Use of Sumifs 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 Use of Formula Q.1 How Many Male and Female Candidate in a List? Countif Use of Formula Q.2 How Many Male Employee in United States? Countifs Q.3 Lisa and John Which Country Belong? Use of Vlookup www.excellentcomputereducation.com Page 10  Excellent Computer Education(A Professional Training Center) Use if Formula Q.4 United States Male and Female Candidate Scores? Sumifs Q.5 How Many Male Candidate Belong Country United State Total Score? www.excellentcomputereducation.com Page 11  Excellent Computer Education(A Professional Training Center) Assignment -17 Use of Formulas - Vlookup ID Brand Product ID Brand Product 101 Dell Computer 104 HP Printer 102 Logitech Keyboard 103 ? ? 103 Logitech Mouse 104 ? ? 104 HP Printer 101 ? ? 102 ? ? Use of Vlookup Function ? 103 ? ? 101 ? ? 104 ? ? 101 ? ? 102 ? ? 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 ? ? www.excellentcomputereducation.com Page 12  Excellent Computer Education(A Professional Training Center) Assignment -19 Use of Formulas - Index with Match 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 1,507 INDEX(A4:D8,MATCH(A10,A4:A8,0),MATCH(B10,A4:D4,0)) West Feb ? South Jan ? North Mar ? Assignment -21 Use of Formulas - Index + Match Emp Name Salary Department Emp ID Emp ID Salary Raju 92,671 Sales Prd001 Prd001 92,671 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 ? www.excellentcomputereducation.com Page 13  Excellent Computer Education(A Professional Training Center) Assignment -22 Use of Formulas - Lookup Emp Name Salary Department Emp ID Emp ID Salary Raju 92,671 Sales Prd001 Prd001 92,671 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 ? Use of Countif Q.2 Employee Santosh Salary? Use of Sumif Q.3 IT & Marketing Department Total Salary? Use of Sumif Assignment -23 Use of Formulas - AND NAME PHYSICS CHEMISTRY MATHS BIOLOGY PASSED THE EXAM ? NITIN PASS PASS FAIL PASS FALSE FEROZ PASS PASS PASS PASS ? 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 ? www.excellentcomputereducation.com Page 14  Excellent Computer Education(A Professional Training Center) 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 Use of Formulas - Averageif 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 78.33333333 C 23.00 Third ? A 250.00 B 25.00 Average of B 37.5 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 Average of West zone Avg of Units Sold above 250 375 8233.33 Avg of Units Sold below 100 ? www.excellentcomputereducation.com Page 15  Excellent Computer Education(A Professional Training Center) Assignment -26 Use of If and Vlookup -Compare List 1 to List 2 List 1 List 2 Result List 1 List 2 Result Raj Ankita Not Matching 343749 160466 160466 Rohit Rohit Matching 183257 183258 Value not in List 1 Kajal Abhay Not Matching 160466 249447 249447 Rohan Rohan Matching 249447 343749 343749 Akshay Puneet Not Matching 532765 356160 Value not in List 1 356163 379391 379391 IF(A9=B9,"Matching","Not Matching") 455292 455292 455292 379391 532765 532765 VLOOKUP(F12,E5:E12,1,0) Color List-1 Result Color List-2 Red Red Red Yellow Yellow Yellow Green Green Grey Blue #N/A Green Orange Orange Orange White #N/A Black VLOOKUP(A17,E17:E22,1,FALSE) Assignment -27 Use of Concatenate Emp First Name Last Name ID 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 = CONCATENATE(B5," ",C5) Emp First Name Last Name ID Full Name D21 Vishal Mohan Vishal Mohan D22 John Mathew John Mathew www.excellentcomputereducation.com Page 16  Excellent Computer Education(A Professional Training Center) D23 Jamemah Powel Jamemah Powel = B16&" "&C16 Assignment -28 Use of Counta, Countif, Countifs, Vlookup and Index with Match Employee Database Emp Date Name Designation KRA Id 01-11-2018 1101 ARUN MIS-OPERATION SALES 01-11-2018 1102 ASHOK OPERATION PHP 03-11-2018 1103 BISWAS SOFTWARE ENG JAVA 03-11-2018 1104 DINESH SME MAILS 03-11-2018 1105 ESHWAR PROGRAMMER C++ 06-11-2018 1106 FAHAD PROGRAMMER DOT NET 06-11-2018 1107 GANGA SOFTWARE ASSOCIATE TESTING 08-11-2018 1108 HEMA NETWORK ENG SERVER 08-11-2018 1109 FARZANA SALES EXECUTIVE SALES 08-11-2018 1110 AYESH SALES EXECUTIVE AMAZON 09-11-2018 1111 PRAVEEN SALES EXECUTIVE AMAZON 09-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 01-नवम्बर-18 1010 VAMSEE KRISHNA BRAND MANAGER MARKETING Q.1 How Many Employee? Use of Counta Q.2 How Many Employee in Sales Use of Countif Executive? 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 www.excellentcomputereducation.com Page 17  Excellent Computer Education(A Professional Training Center) 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 101 Donald Patrick Finance Banglore 103 ? ? ? ? 102 ? ? ? ? 105 ? ? ? ? 108 ? ? ? ? 106 ? ? ? ? 107 ? ? ? ? 104 ? ? ? ? 109 ? ? ? ? 110 ? ? ? ? Assignment -30 Get Pivot Table Pivot Table Result Date of Sale Month Sales Amt Month (All) 19-01-2018 January 2,01,440 16-01-2018 January 3,52,519 Date of Sale Sum of Sales Amt 22-01-2018 January 1,72,406 12-01-2018 2,40,000 www.excellentcomputereducation.com Page 18  Excellent Computer Education(A Professional Training Center) 12-01-2018 January 2,40,000 16-01-2018 3,52,519 05-02-2018 February 15,205 19-01-2018 2,01,440 02-02-2018 February 24,327 22-01-2018 1,72,406 13-02-2018 February 50,549 02-02-2018 24,327 15-02-2018 February 15,106 05-02-2018 15,205 15-02-2018 February 19,901 09-02-2018 15,205 09-02-2018 February 15,205 13-02-2018 50,549 22-02-2018 February 3,00,000 15-02-2018 35,007 26-02-2018 February 1,50,000 22-02-2018 3,00,000 26-02-2018 February 3,30,553 26-02-2018 6,43,835 26-02-2018 February 1,63,282 27-02-2018 5,64,030 27-02-2018 February 5,64,030 28-02-2018 7,20,256 28-02-2018 February 5,03,599 Grand Total 33,34,777 28-02-2018 February 15,218 28-02-2018 February 2,01,440 Assignment -31 USE OF HLOOKUP Months January February March April May June Sale 240 180 310 445 650 700 Months April Sale ? Name Roger Mat Jim Cole Ricky Mary Science 36 45 52 66 75 40 English 82 71 56 32 81 66 Maths 32 45 52 51 71 74 Marks in English ? Name Roger Mat Jim Cole Ricky Mary Science 36 45 52 66 75 40 English 82 71 56 32 81 66 Maths 32 45 52 51 71 74 Marks in Maths ? EMP FIS6067 FIS5228 FIS6799 FIS1149 FIS5834 SALES1 66 43 36 82 89 SALES2 51 83 41 125 79 www.excellentcomputereducation.com Page 19  Excellent Computer Education(A Professional Training Center) SALES3 35 97 92 41 39 SALES4 84 76 35 48 37 SALES5 110 77 90 37 34 EMP FIS1149 Sales 4 ? Temperarture 21 33 39 42 50 (In Celsius) Cities New Delhi Patna Mumbai Pune Bangalore Temperature 40 City ? Employee Albert Aaron Albama Abeey Carol Cathy Sales 200 125 320 250 300 421 Employee Cat Sales ? Assignment -32 USE OF NESTEDIF Total Numbers Name Grade earned Earned Condition List John Wilkins 92 A+ 90-100 A+ Steve Harrington 88 A 85 - < 90 A Edward Clark 94 A+ 80 - < 85 B+ Jimmy Chemberlin 84 B+ 75 - < 80 B Alex Wilkins 95 A+ 70 - < 75 C+ Patty Scott 78 B 65 - < 70 C Andrew Williams 59 D 60 - < 65 D+ Emilia johnson 43 F 50 - < 60 D Anthony Rogers 90 A+ < 50 F 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 www.excellentcomputereducation.com Page 20  Excellent Computer Education(A Professional Training Center) 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 Raju ??? ??? Prd002 Ramesh ??? ??? Prd003 Ramila ??? ??? Prd004 Rajeshwari ??? ??? Prd005 Karan ??? ??? Prd006 Rohith ??? ??? Prd007 Jacob ??? ??? Prd008 Fleming ??? ??? Prd009 Navya ??? ??? Prd010 Kavya ??? ??? Prd011 Santosh ??? ??? Prd012 Shankar ??? ??? Prd013 Rajesh ??? ??? Prd014 Mahesh ??? ??? Prd015 Hemaraj ??? ??? Prd016 Nagaraj ??? ??? Prd017 Johson ??? ??? Prd018 David ??? ??? Prd019 Anderson ??? ??? Prd020 Peter ??? ??? www.excellentcomputereducation.com Page 21  Excellent Computer Education(A Professional Training Center) Assignment -34 Use of Sumif 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 Total Quantity Sold By Ben 24383 Sold By Jenny ? Total Quantity Total Quantity Sold By Ben & Jenny 35607 Sold ? By Ben & Jenny SUMIF(A2:A9,"Ben",C2:C9)+SUMIF(A2:A9,"Jenny",C2:C9) 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 Total Quantity Sold By Ben & Jenny 35607 Sold By Jeff & Jenny SUMIF(A2:A9,"Ben",C2:C9)+SUMIF(A2:A9,"Jenny",C2:C9) www.excellentcomputereducation.com Page 22  Excellent Computer Education(A Professional Training Center) Assignment -36 USE OF VLOOKUP Emp First Name Dept Region Salary INCENTIVE Bonus TA Dept INCENTIVE Bonus 1 Raja Sales 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 Q.1 How many Emloyee in Sales and Mktg Department. Q.2 How Many salary in Sales Department. Q.3 How many Employee Department Sales, North Region Salary. Q.4 How many Employee Department Sales and Region north. Q.5 If Salary Greater Then 15000, "A", if salary Greater Then 10000, "B" otherwise "C". Assignment -37 USE OF VLOOKUP WITH CONDITION TRUE/FALSE CONDITION salary Empcode First Name Dept Salary Incentive Grade slab incentive grade 1 Raja Sales 15,625 20% D 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 www.excellentcomputereducation.com Page 23  Excellent Computer Education(A Professional Training Center) 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 ? ? Satinder 19 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 ? ? Assignment -38 USE OF DATEDIF FORMULAS CALCULATE DOB FirstDate SecondDate Interval Difference 01-जनवरी-60 10-मई-70 days 3782 =DATEDIF(C4,D4,"d") 01-जनवरी-60 10-मई-70 months 124 =DATEDIF(C5,D5,"m") 01-जनवरी-60 10-मई-70 years 10 =DATEDIF(C6,D6,"y") 01-जनवरी-60 10-मई-70 yeardays 130 =DATEDIF(C7,D7,"yd") 01-जनवरी-60 10-मई-70 yearmonths 4 www.excellentcomputereducation.com Page 24  Excellent Computer Education(A Professional Training Center) =DATEDIF(C8,D8,"ym") 01-जनवरी-60 10-मई-70 monthdays 9 =DATEDIF(C9,D9,"md") What Does It Do? This function calculates the difference between two dates. It can show the result in weeks, months or years. Syntax =DATEDIF(FirstDate,SecondDate,"Interval") FirstDate : This is the earliest of the two dates. SecondDate : This is the most recent of the two dates. "Interval" : This indicates what you want to calculate. These are the available intervals. "d" Days between the two dates. "m" Months between the two dates. "y" Years between the two dates. "yd" Days between the dates, as if the dates were in the same year. "ym" Months between the dates, as if the dates were in the same year. "md" Days between the two dates, as if the dates were in the same month and year. Formatting No special formatting is needed. Birth date : 01-जनवरी-60 Years lived : 61 =DATEDIF(C8,TODAY(),"y") and the months : 3 =DATEDIF(C8,TODAY(),"ym") and the days : 24 =DATEDIF(C8,TODAY(),"md") You can put this all together in one calculation, which creates a text version. Age is 61 Years, 3 Months and 24 Days ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days" Assignment -44 USE OF FIND & LARGE FORMULA Text Letter To Find Position Of Letter Hello e 2 =FIND(D4,C4) Hello H 1 =FIND(D5,C5) Hello o 5 =FIND(D6,C6) Alan Williams a 3 =FIND(D7,C7) www.excellentcomputereducation.com Page 25  Excellent Computer Education(A Professional Training Center) Alan Williams a 11 =FIND(D8,C8,6) Alan Williams T #VALUE! =FIND(D9,C9) Values Highest Value 800 =LARGE(C4:C8,1) 120 2nd Highest Value 250 =LARGE(C4:C8,2) 800 3rd Highest Value 120 =LARGE(C4:C8,3) 100 4th Highest Value 120 =LARGE(C4:C8,4) 120 5th Highest Value 100 =LARGE(C4:C8,5) 250 What Does It Do ? This function examines a list of values and picks the value at a user specified position in the list. Syntax =LARGE(ListOfNumbersToExamine,PositionToPickFrom) Formatting No special formatting is needed. Example The following table was used to calculate the top 3 sales figures between Jan, Feb and Mar. Sales Jan Feb Mar North £5,000 £6,000 £4,500 South £5,800 £7,000 £3,000 East £3,500 £2,000 £10,000 West £12,000 £4,000 £6,000 Highest Value £12,000 =LARGE(D24:F27,1) 2nd Highest Value £10,000 =LARGE(D24:F27,2) 3rd Highest Value £7,000 =LARGE(D24:F27,3) Note Another way to find the Highest and Lowest values would have been to use the =MAX() and =MIN() functions. Highest £12,000 =MAX(D24:F27) Lowest £2,000 =MIN(D24:F27) www.excellentcomputereducation.com Page 26  Excellent Computer Education(A Professional Training Center) Assignment -45 USE OF LEFT, FIND, LEN,LOWER, NETWORKDAYS USE OF LEFT Number Of Text Characters Required Left String Alan Jones 1 A LEFT(A5,B5) Alan Jones 2 Al Alan Jones 3 Ala Cardiff 6 Cardif ABC123 4 ABC1 USE OF LEFT AND FIND Full Name First Name Alan Jones Alan =LEFT(A12,FIND(" ",A12)-1) Bob Smith Bob Carol Williams Carol USE OF LEN Text Length Alan Jones 10 =LEN(A18) Bob Smith ? Carol Williams ? Cardiff ? ABC123 ? USE OF LOWER Upper Case Text Lower Case ALAN JONES alan jones =LOWER(A27) BOB SMITH ? CAROL WILLIAMS ? CARDIFF ? ABC123 ? USE OF NETWORKDAYS Start Date End Date Work Days 01-मार्च-98 07-मार्च-98 5 =NETWORKDAYS(A36,B36) 25-अप्रैल-98 30-जुलाई-98 69 24-दिसम्बर-98 05-जनवरी-99 9 www.excellentcomputereducation.com Page 27  Excellent Computer Education(A Professional Training Center) Assignment -46 USE OF POWER, PRODUCT, PROPER, REPT USE OF POWER Number Power Result 3 2 9 =POWER(A5,B5) 3 4 ? 5 2 ? 5 4 ? USE OF PRODUCT Numbers Product 2 3 6 =PRODUCT(A12,B12) 5 10 ? 3 7 ? 6300 USE OF PROPER Original Text Proper alan jones Alan Jones =PROPER(A19) bob smith ? caRol wILLIAMS ? cardiff ? ABC123 ? USE OF REPT Text To Number Of Repeated Repeat Repeats Text A 3 AAA =REPT(A27,B27) AB 3 ? - 10 ? | 10 ? Assignment -48 SALESMAN JAN FEB MAR APR MAY JUNE SALES TARGET RESULT comission RAMESH 2000 1500 300 1400 1000 1400 7600 10000 NOT ACHIVED 380 RAKESH 5000 1200 500 1200 1200 2800 11900 12000 NOT ACHIVED 595 RAHUL 3000 800 1200 3000 1500 3500 13000 18000 NOT ACHIVED 650 POOJA 1000 900 1800 5000 1400 1200 11300 10000 ACHIEVED 1130 MANOJ 500 1000 2300 8000 1700 1400 14900 12000 ACHIEVED 1490 www.excellentcomputereducation.com Page 28  Excellent Computer Education(A Professional Training Center) ASHOK 800 500 2400 1900 1800 1800 9200 10000 NOT ACHIVED 460 AJEET 1200 1400 1500 700 2500 7000 14300 12000 ACHIEVED 1430 ALOK 1500 1800 1800 1800 300 1500 8700 10000 NOT ACHIVED 435 AMRIT 1800 2500 1700 1500 2800 1800 12100 12000 ACHIEVED 1210 SURENDRA 200 3000 1900 1200 1500 3000 10800 10000 ACHIEVED 1080 SHASHI 1600 1200 2000 800 1700 800 8100 10000 NOT ACHIVED 405 USE OF COUNTA AND Q.1 How many salesman? Salesman Ajeet Targest & Result? VLOOKUP Q.3 Rahul Pooja & Ashok Targest & result? USE OF VLOOKUP Q.4 How Many Salesman Achived Target. USE OF COUNTIF Q.5 Which Sales Man Jan Sales 2000, & Feb Sales is 2500? USE OF LOOKUP USE OF Q.6 How Many sales Man sales Jan Months Sales >2000 & March Sales 2000 & March Sales = 25 YEARS? More Practice Excel File Link https://drive.google.com/file/d/1toBtR2SRhY9iAeZduEmg4drgYhTiXUSk/v iew?usp=sharing www.excellentcomputereducation.com Page 30