Excellent Computer Education

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 ? ? ?

1. Find the Total Number & Average in all Subjects for Each Student
o SUM Formula: =SUM(B2:F2) → Adds all subject marks for each student.
o AVERAGE Formula: =AVERAGE(B2:F2) → Finds the average marks of each student.
2. Find Grade Using IF Function
o IF Formula: =IF(G2>15, "A", "B") → If the average marks are greater than 15, assign "A",
otherwise assign "B".
3. Count How Many Students Have "A" and "B" Grade
o COUNTIF Formula: =COUNTIF(H2:H11, "A") → Counts the number of students with an "A" grade.
o =COUNTIF(H2:H11, "B") → Counts the number of students with a "B" grade.
4. Find Total Number and Average for Ashok and Manoj
o SUMIF Formula: =SUMIF(A2:A11, {"Ashok", "Manoj"}, G2:G11) → Adds up the total marks for
Ashok and Manoj.
o AVERAGE Formula: =AVERAGE(G2:G11) → Calculates the average.
5. Count How Many Students Are in the List
o COUNTA Formula: =COUNTA(A2:A11) → Counts all non-empty cells in the "Student Name"
column.
6. Find How Many Students Have Hindi & English Marks Greater Than 20 and Less Than 15
o COUNTIF Formula: =COUNTIF(B2:B11, ">20") + COUNTIF(C2:C11, "<15")

Page 1
Excellent Computer Education

Assignment -2
Use of Formulas - Product, If, Counta, Countif, Sumif

SRNO ITEMS RATE AMOUNT GRADE

1 AC 4 800000 Expensive

2 FRIDGE 2 ?

3 COOLER 1 ?

4 WASHING MACHINE 1 ?

5 TV 2 ?

6 FAN 2 ?

7 COMPUTER 2 ?

8 KEYBOARD 2 ?

9 MOUSE 1 ?

10 PRINTER 1 ?

1. Calculate Amount Using Product Formula

o PRODUCT Formula: =B2*C2 → Multiplies QTY with Rate to get Amount.
2. Count the Number of Items in the List
o COUNTA Formula: =COUNTA(A2:A11) → Counts all non-empty rows in the "Items" column.
3. Count Items Where QTY > 20 and < 20
o COUNTIF Formula: =COUNTIF(B2:B11, ">20")
o =COUNTIF(B2:B11, "<20")
4. Calculate the Qty, Rate, and Amount for Computers Using SUMIF
o SUMIF Formula: =SUMIF(A2:A11, "Computer", B2:B11) (Qty)
o =SUMIF(A2:A11, "Computer", C2:C11) (Rate)
o =SUMIF(A2:A11, "Computer", D2:D11) (Amount)
5. Check if the Item is Expensive or Affordable Using IF Formula
o IF Formula: =IF(D2>500000, "Expensive", "Let’s Buy It")

Assignment -3
Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup

SUBJECT TOTAL AVERAGE GRADE

HINDI 55 18.33333333 B
ENGLISH ? ? ?

Page 2
Excellent Computer Education
MATH ? ? ?
PHYSICS ? ? ?
CHEMISTRY ? ? ?
HISTORY ? ? ?
GEO ? ? ?
BIO ? ? ?
BOTANY ? ? ?

1. Find the Total and Average for Each Subject

o SUM Formula: =SUM(B2:D2) → Adds up the subject marks.
o AVERAGE Formula: =AVERAGE(B2:D2) → Calculates the average score.
2. Count the Number of Subjects
o COUNTA Formula: =COUNTA(A2:A10)
3. Find Subjects Where the First Paper Score Is Greater Than 20
o COUNTIF Formula: =COUNTIF(B2:B10, ">20")
4. Find the Total & Grade for Hindi, Math & English Using VLOOKUP
o VLOOKUP Formula: =VLOOKUP("Hindi", A2:D10, 4, FALSE)
5. Assign Grade Based on Average Score
o Nested IF Formula: =IF(E2>20, "A", IF(E2>15, "B", "C"))

Assignment -4 (Salary Sheet)

Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup

NAME DEPARTMENT POST DA 2.5% HRA 3.5% PF 1.5% TOTAL GRADE

RAM COMPUTER MANAGER 125 175 50 5250 D

SHYAM COMPUTER SUPERVISOR ? ? ? ? ?

MANOJ COMPUTER PION ? ? ? ? ?

POOJA ELECTRICAL GUARD ? ? ? ? ?

RAHUL ELECTRICAL CASHER ? ? ? ? ?

RAKESH ELECTRICAL ACCOUNTANT ? ? ? ? ?

ASHISH FINANCE MANAGER ? ? ? ? ?

MANISH FINANCE GUARD ? ? ? ? ?

1. Count Employees in Each Department

o COUNTIF Formula: =COUNTIF(B2:B10, "Computer") (for each department)
2. Find the Total Basic Salary in the Computer Department
o SUMIF Formula: =SUMIF(B2:B10, "Computer", C2:C10)

Page 3
Excellent Computer Education
3. Find the Post & Grade for Manoj & Ashish Using VLOOKUP
o VLOOKUP Formula: =VLOOKUP("Manoj", A2:E10, 3, FALSE)
4. Assign Salary Grades
o Nested IF Formula: =IF(F2>20000, "A", IF(F2>10000, "B", "C"))
5. Count How Many Employees Are Managers & Guards
o COUNTIF Formula: =COUNTIF(C2:C10, "Manager") + COUNTIF(C2:C10, "Guard")

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 200 100 7600 1 ACHIVED

RAKESH 500 120 ? 1 ?

RAHUL 300 150 ? 1 ?

POOJA 100 140 ? 1 ?

MANOJ 500 170 ? 1 ?

ASHOK 800 180 ? 1 ?

AJEET 120 250 ? 1 ?

ALOK 150 300 ? 1 ?

AMRIT 180 280 ? 1 ?

SURENDRA 200 150 ? 1 ?

SHASHI 160 170 ? 1 ?

1. Count the Number of Salesmen

o COUNTA Formula: =COUNTA(A2:A10)

2. Find Ajeet’s Target & Result Using VLOOKUP

o VLOOKUP Formula: =VLOOKUP("Ajeet", A2:F10, 5, FALSE)

3. Check if Sales Achieved the Target

o IF Formula: =IF(F2>G2, "Achieved", "Not Achieved")

4. Count How Many Salesmen Achieved Their Targets

o COUNTIF Formula: =COUNTIF(H2:H10, "Achieved")

5. Find Salesman Who Had Jan Sales of 2000 & Feb Sales of 2500
o LOOKUP Formula: =LOOKUP(2000, B2:B10, A2:A10)

Page 4
Excellent Computer Education
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

Page 5
Excellent Computer Education

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

1. Count the Total Number of Items

o COUNTA Formula: =COUNTA(A2:A15)

2. Count Brakes, Windows & Tyres Bought

o COUNTIF Formula: =COUNTIF(A2:A15, "Brakes") + COUNTIF(A2:A15, "Window") +
COUNTIF(A2:A15, "Tyres")

3. Count Items with Cost > 1000 & <= 1000

o COUNTIF Formula: =COUNTIF(C2:C15, ">1000") + COUNTIF(C2:C15, "<=1000")

4. Highlight Tyres with Cost Between 500-2000

o Use Conditional Formatting in Excel.

5. Find Items in Column 15, 18 & 20 Using HLOOKUP

o HLOOKUP Formula: =HLOOKUP(15, A1:E10, 2, FALSE)

6. Calculate Total Cost for Windows & Brakes

o SUMIF Formula: =SUMIF(A2:A15, "Window", C2:C15) + SUMIF(A2:A15, "Brakes", C2:C15)

Page 6