MS EXCEL ASSINGMENT

Assignment-1

Use of formula 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”

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 Greater Then >20 And < 15 Use Of Countif

1|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-2
Use Of Formula – Product , If , Counta , Sumif
SR.NO ITEMS QTY RATE AMOUNT GRADE
1 AC 20 40000 800000 Expensive
2 FRIDGE 30 20000 ? ?

3 COOLER 15 10000 ? ?

4 WASHIN 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 USING OF PRODUCT FORMULA FOR CALCULATE AMOUNT = QTY * RATE

Q.2 HOW MANY ITEMS IN A LIST

Q.3 HOW MANY ITEMS QTY GREATER THEN > 20 AND LESS THEN < 20

Q.4 CALCULATE ITEM COMPUTER QTY , RATE AND AMOUNT USING SUMIF FORMULA

Q.5 IF ITEMS AMOUNT IS GREATER > 500000,THEN ITEMS “EXPENSIVE” OTHERWISE “LETS BUY IT “,

2|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-3
USE OF FORMULA – SUM , NESTEDIF , COUNTA , COUNTIF , SUMIF , VLOOKUP
SUBJECT 1ST 2ND 3RD TOTAL AVERAGE GRADE
HINDI 20 15 20 55 18.33333 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 ? ? ?
POL SCI 20 25 25 ? ? ?

Q.1 HOW MANY SUBJECT ? USE OF COUNTA

Q.2 HOW MANY SUBJECT 1 PAPER GREATER THAN 20 ? USE OF COUNTIF

Q.3 SUBJECT HINDI ,MATH & ENGLISH TOTAL NO .& GRADE USE OF VLOOKUP

Q.4 IF AVE. GREATER THAN 20 THAN “A”, IF AVE. GREATER THAN 15 AVE.”B” OTHERWISE “C”

Q.5 SUBJECT PHYSICS , MATHS, & ENGLISH TOTAL / AVERAGE USE OF VLOOKUP

3|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-4 (SALARY SHEET)
Use of formulas-sum,nestedif,counta,countif,sumif,vlookup

DA HRA PF
NAME DEPARTMENT POST BASIC TOTAL GRADE
2.5% 3.5% 1.5%
RAM COMPUTER MANAGER 5000 125 175 75 5225 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 EMPLOYEES IN COMPUTER,FINANCE,ELECTRICAL DEPARTMENT Use Of Countif

Q.2 HOW MANY BASIC SALARY IN COMPUTER DEPARTMENT ONLY ? Use Of Sumif

Q.3 MANOJ , ASHISH POST AND Use Of Vlookup

Q.4 IF TOTAL SALARY IS GREATER THAN 20000 THEN “A”, IF TOTAL SALARY GREATER THAN 10000 THEN “B”
OTHERWISE “C”

Q.5 HOW MANY EMPLOYEE IS MANAGER & GUARD ? Use Of Countif

4|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-5 (SALES REPORT)
Use For Formulas- Sum,If,Counta,Countif,Sumif,Vlookup,Lookup

SALESMAN JAN FEB MAR APR MAY JUNE SALES TARGET RESULT
NOT
RAMASH 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 ? SALASMAN AJEET TARGET & RESULT ? Use Of Counta & Vlookup

Q.2 IF SALES GREATER THAN TARGET THEN TARGET ACHIEVED OTHERWISE NOT Use Of If Function

Achieved

Q.3 RAHUL POOJA & ASHOK TARGET & RESULT ? Use Of Vlookup

Q.4 HOW MANY SALESMAN ACHIEVED TARGET Use Of Countif

Q.5 WHICH SALES MAN JAN SALES 2000,& FEB SALES IS 2500 ? Use Of Lookup Function

5|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-6
Conditional Formatting
USE OF FORMULAS- COUNTA,COUNTIF,SUMIF,HLOOKUP

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
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 BRAKES,WINDOW & TYRES HAVE BEEN BOUGHTS ? USE OF COUNTIF

Q.3 HOW MANY ITEMS COST IS >1000 & BELOW > = 1000 ? USE OF COUNTIF

Q.4 HIGHLIGHT TYRES ITEMS & 500 BETWEEN 2000 COST . USE OF CONDITIONAL FORMATING

Q.5 ITEMS COLUMS IS 15,18,& 20 ITEMS NAME ? USE OF HLOOKUP

Q.6 TOTAL COST OF WINDOW AND BRAKES ITEMS ? USE OF SUMIF

6|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-7
(CALCULATE DATE OF BIRTH)
USE OF FORMULAS – COUNTA,COUNTIF,IF & DATEDIF

NAME DATE OF DAY MONTH YEAR CURRENT

BIRTH AGE
RAMESH 15-05-1980 15 5 1980 45
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 ? ? ? ?

DAY FUNCTION =DAY(D.O.B ) ENTER

MONTH FUNCTION =MONTH(D.O.B) ENTER

YEAR FUNCTION =YEAR(D.O.B) ENTER

CURRENT YEAR =DATEDIF(D.O.B,TODAY(),”Y”)

Q.1 HOW MANY STUDENT ? Use Of Counta

Q.2 STUDENT SURENDRA IS HOW MANY YEARS OLD ? Use Of Datedif(D.O.B,Today(),”Y”)

Q.3 HOW MANY STUDENT AGE GRATER THAN 20 YEARS ? Use Of Countif(Current Age,”>20”)

Q.4 IF STUDENT AGE ID GREATER THAN 20 THEN STUDENT ADULT / CHILD ?

If(Current Age>=20,”Adult”,”Child)

Q.5 HOW MANY STUDENT AGE IS > = 25 YEARS ? Use Of Countif(Current Age,”>=25”)

7|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-8
Use Of Formula-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 POOR 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 ? ?

Q1. HOW MANY STUDENT USE FORMULA COUNTA

Q2. HOW MANY STUDENT PERCENTAGE GREATER THAN >50 USE FORMULA COUNTIF

Q3. STUDENT BOB AND ERIC TOTAL NUMBER ? USE FORMULA SUMIF

Q4. IF PERCENTAGE GREATER THAN >70 THEN “EXCELLENT”,

IF PERCENTAGE GREATER THAN >50,”GOOG”, OHTERWISE “BED”

Q5. HOW MANY STUDENT GOOD AND BED IN A LIST USE FORMULA COUNTIF

8|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”

 MS EXCEL ASSINGMENT
Assignment-9
Use Of Formula- Lookup
LOOKUP FUNCTION SYNTEX LOOKUP(LOOKUP_Value,Lookup_Vector,[Result_Vector])

EMPLOYEE ID LAST NAME FIRST NAME

EMPLOYEE PAY FIRST LAST
110608 DOE JOHN ID NAME NAME
253072 CLINE ANDY 602693 84289 MICHEAL VICK
352711 SMITH JOHN 611810 137670 ? ?
391006 PAN PETER 549457 190024 ? ?
392128 FAVRE BRET 612235 122604 ? ?
549457 ELWAY JOHN 580622 111709 ? ?
580622 MANNING ELI 830385 85931 ? ?
602693 VICK MICHEAL 253072 168114 ? ?
611810 WOODS TIGER 391006 89627 ? ?
612235 JORDAN MICHEAL 990678 149946 ? ?
795574 STARK TONY 795574 145893 ? ?
830385 WILLIAMS PRINCE 392128 64757 ? ?
990678 PITT BRAD 352711 71478 ? ?
110608 121444 ? ?

9|Page

“CREATED BY TAYYABA ,POWERD BY PRECISION & EXCEL MAGIC”