Sales Person Week 1 Week 2 Week 3 Week 4

Anil 20 100 100 10

Ram 80 100 50 100
Arun 10 10 100 80
Aman 80 50 50 80
Vinita 30 60 60 50
Simba 10 20 50 20
Chiku 10 60 20 70
Sales Person Week 1 Week 2 Week 3 Week 4
Anil 40 30 20 20
Ram 80 90 30 30
Arun 80 80 30 70
Aman 80 30 20 20
Vinita 20 60 30 40
Simba 60 60 60 90
Chiku 100 30 80 20
Sales Person Week 1 Week 2 Week 3 Week 4
Anil 90 70 30 70
Ram 80 80 70 100
Arun 100 100 70 10
Aman 50 100 30 20
Vinita 10 50 50 100
Simba 40 50 80 90
Chiku 30 80 80 70
Sales Person Week 1 Week 2 Week 3 Week 4
Anil
Ram
Arun
Aman
Vinita
Simba
Chiku
 Jan Feb
Sales PersonWeek 1 Week 2 Week 3 Week 4 Sales PersonWeek 1 Week 2
Anil 20 100 100 10 Anil 20 50
Ram 80 100 50 100 Ram 100 90
Arun 10 10 100 80 Arun 60 50
Aman 80 50 50 80 Aman 100 30
Vinita 30 60 60 50 Vinita 70 30
Simba 10 20 50 20 Simba 80 100
Chiku 10 60 20 70 Chiku 100 30

1 Calculate Total of Q1 by all the 3 Methods

2 Best performing week in each Month
3 Identify best and least performing Week in Q1
4 Put a Heat Map over Q1
5 Identify & Sort Best Sales Person in Jan, Feb & March. Also in Q1
Feb March
Week 3 Week 4 Sales PersonWeek 1 Week 2 Week 3 Week 4
60 60 Anil 30 40 10 30
40 10 Ram 20 60 100 90
80 10 Arun 20 60 70 40
50 50 Aman 50 20 100 80
60 70 Vinita 100 20 100 60
40 60 Simba 90 100 20 80
90 40 Chiku 40 10 20 80
 Q1 Total
Sales PersonWeek 1 Week 2 Week 3 Week 4
Anil
Ram
Arun
Aman
Vinita
Simba
Chiku
 Simple VLOOKUP
Product Product Code Quantity Price Total PRODUCT camera
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

VLOOKUP With TRIM FUNCTION

Product Product Code Quantity Price Total PRODUCT Mobile
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

VLOOKUP With Data Validation

Product Product Code Quantity Price Total PRODUCT
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

MATCH Function
Product PRODUCT
Mobile Watch
Camera
Watch
Perfume
Wall Clock
Guitar
Drum

HLOOKUP
Product Mobile Camera Watch Perfume Wall Clock Guitar Drum
Product Code ABA - 133339 BVD - 367987 RFG -301565 ESF - 326794 EEE - 300402 WER - 9695 EWR - 228431
Quantity 904 451 863 336 257 366 161
Price 99.38 574.25 59.86 42.93 7.19 65.15 80.18
Total 89,835.94 258,985.69 51,659.99 14,425.77 1,846.88 23,843.61 12,908.50
 ID Brand Product
102
104 ID
101 101
106 102
103 103
105 104
106 105
105 106
106
102
105
106

C
POSITION

PRODUCT Perfume
PRICE
QUANTITY
 Brand Product
Dell Laptop
Logitech Keyboard
HP Laptop
Logitech Mouse
Apple Phone
Lenovo Desktop
 Simple VLOOKUP
Product Product Code Quantity Price Total PRODUCT camera
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

VLOOKUP With TRIM FUNCTION

Product Product Code Quantity Price Total PRODUCT Mobile
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

VLOOKUP With Data Validation

Product Product Code Quantity Price Total PRODUCT
Mobile ABA - 133339 904 99.38 89,835.94 PRICE
Camera BVD - 367987 451 574.25 258,985.69 QUANTITY
Watch RFG -301565 863 59.86 51,659.99
Perfume ESF - 326794 336 42.93 14,425.77
Wall Clock EEE - 300402 257 7.19 1,846.88
Guitar WER - 9695 366 65.15 23,843.61
Drum EWR - 228431 161 80.18 12,908.50

MATCH Function
Product PRODUCT
Mobile Watch
Camera
Watch
Perfume
Wall Clock
Guitar
Drum

HLOOKUP
Product Mobile Camera Watch Perfume Wall Clock Guitar Drum
Product Code ABA - 133339 BVD - 367987 RFG -301565 ESF - 326794 EEE - 300402 WER - 9695 EWR - 228431
Quantity 904 451 863 336 257 366 161
Price 99.38 574.25 59.86 42.93 7.19 65.15 80.18
Total 89,835.94 258,985.69 51,659.99 14,425.77 1,846.88 23,843.61 12,908.50
 ID Brand Product
104
104 ID
104 101
103 102
106 103
103 104
105 105
101 106
101
101
103
103

C
POSITION

PRODUCT Perfume
PRICE
QUANTITY
 Brand Product
Dell Laptop
Logitech Keyboard
HP Laptop
Logitech Mouse
Apple Phone
Lenovo Desktop
 STUDENT MATHS ARTS LANGUAGE SPORTS
Anil 78 75 78 19 FIND SCORES OF BELOW CANDI
Ram 33 83 41 54 STUDENT
Arun 11 52 93 20 AMAN
Aman 29 19 25 19 VINITA
Vinita 73 8 25 11 SIMBA
Simba 65 90 87 16 ARUN
Chiku 78 36 33 43

2-Way VLOOKUP
STUDENT MATHS ARTS LANGUAGE SPORTS TRY AND CREATE BELOW SE
Anil 78 75 78 19
Ram 33 83 41 54 Vinita
Arun 11 52 93 20
Aman 29 19 25 19
Vinita 73 8 25 11
Simba 65 90 87 16
Chiku 78 36 33 43

HLOOKUP
Anil Ram Arun Aman Vinita Simba
MATHS 8 71 91 91 83 18
ARTS 35 49 72 12 38 29
LANGUAGE 41 57 28 68 18 27
SPORTS 31 56 46 86 35 47

Winter Jacket Order for Celebrity Dogs

Dog Neck in Size to

Dog Name
cm Order
Scooby Doo 20 Neck Size Jacket Size
Petra 22 0 XXS
Lassie 19 10 XS
Snoopy 13 18 Small
Beethoven 44 40 M
Benji 27 60 L
Eddie 21 80 Extra Large
Rin Tin Tin 14 100 XXL
Toto 15
Hooch 38
Santa's Little H 15
Shep 19
Marley 25
Cliffford (The 200
ND SCORES OF BELOW CANDIDATES
MATHS ARTS LANGUAGE SPORTS
X X
X
X
X X

RY AND CREATE BELOW SETUP FROM THE DATA

ARTS

Chiku Student
62 Marks
94
95
67
 Class Practice

Value 1 Value 2 Logical Operator Result What

4 4 = Is 25 = 45?
3 3 <> Is 25 <> 45?
5 4 > Is 25 > 45?
6 5 < Is 25 >= 45?
8 9 >= Is 25 < 45?
3 3 <= Is 25 <= 45?

Class Practice (For Student)

Value 1 Value 2 Logical Operator = Logical Operator <> Logical Operator > Logical Operator <
33 80
83 76
88 53
22 6
6 24
83 11
53 32
44 55
76 79
38 87
83 50
71 37
59 64
44 68
85 68
89 32
31 16
75 5
54 21
55 90
87 34
88 43
87 14
28 18
63 7
56 58
45 84
40 70
76 19
85 33
16 24
28 49
83 22
83 75
86 85
42 67
 No. No. LogicalComparative
Formula Operator
Comparative Operation
25 45 = Equal
25 45 <> Not Equal
25 45 > Greater
25 45 >= Greater or Equal
25 45 < Less
25 45 <= Less or Equal

Logical Operator >= Logical Operator <=

 IF Function

Exercise 1

Table A contains names and their respective grades for Excel 101 Course
Complete column C using only IF formula
Grade 60 or higher = Pass
Grade less than 60 = Fail

Name Grade Pass/Fail

Adi 98
Beni 55
Charlie 15
Dani 60

Exercise 2

The following table is an extract from an accounting system that contains four journal entries
Check if column A's cells match column B's cell
if they match - return "match", otherwise return "no match"

A B
Debit Credit Same value?
Journal Entry 1 $94.00 $94.00
Journal Entry 2 $109.00 $109.00
Journal Entry 3 $85.00 $85.50
Journal Entry 4 $12.00 $12.00
Exercise 3

The table below contains details of high school students names and ages, use IF formula to complete columns D and E
1 If the student's age is 16 or above, he/she is eligible for a driver's license. Check if they are eligible or not. Answer in column D

2 If the student is younger than 18 years old he/she is a minor. Check whether the student is a minor or not. for Minor return "Minor" an

Column D
Number Name Age Driver Licence
1 Arik 16
2 Ben 18
3 Cermit 15.5
4 Dan 19
5 Eliko 18
6 Fage 13
7 George 18
8 Herzl 17

Exercise 4

An A+ student gets 100% scholarship and non A+ gets 50% scholarship as shown in the table below:

Amount
A+ 1
A- 0.5
The following table contains the names of students from 2024 class.
Use IF function to calculate the scholarships' amounts each of them will get

Name GPA Tuition Scholarship

Sam A+ 46866
Ari A- 33495
Xena A- 35087
Gabe A+ 42603
Eliko A- 36971
Daniela A+ 41286
Rotem A- 37732

CLASS EXERCISE

QUESTION 1
Deposit
Account
Balance Interest Earned Rate Applied
£62,900.00 If balance >= 85000 get 7.5% interest
£13,005.00 Anything less get 5% interest
£87,000.00
£54,500.00 Threshold
£94,500.00 High Rate
£120,500.00 Low Rate
£85,000.00
£33,400.00 If balance >= 85000 message is "High Rate"
£17,000.00 Anything less message is "Low Rate"
£107,900.00

ANSWER 1
 Deposit
Account
Balance Interest Earned Rate Applied
£62,900.00 #VALUE! Low_Rate If balance >= 85000 get 7.5% interest
£13,005.00 #VALUE! Low_Rate Anything less get 5% interest
£87,000.00 #VALUE! Low_Rate
£54,500.00 #VALUE! Low_Rate Threshold
£94,500.00 #VALUE! Low_Rate High Rate
£120,500.00 #VALUE! Low_Rate Low Rate
£85,000.00 #VALUE! Low_Rate
£33,400.00 #VALUE! Low_Rate If balance >= 85000 message is "High Rate"
£17,000.00 #VALUE! Low_Rate Anything less message is "Low Rate"
£107,900.00 #VALUE! Low_Rate

AND Function

Exercise 1
(Write any number)
Number 1 Number 2 Condition Number Code
399 999 in between 600

OR Function

Exercise 1
(Write any number)
Number 1 Number 2 Condition Number Code
399 999 in between 600

IFERROR Function

Exercise 1
Sales EmployeeTotal Sales Years in Service Average Annual Sales
Ram 213,935.00 3
Buvan 437,834.00 6
Hari 116,450.00 0
Simba 280,043.00 2
Geeta 170,360.00 0

Exercise 2

You have data of a digital shop. Create a inventory checking mechanism. The validated system should return the units left in stock when selected from th

Items in Stock Units Left Total Items Check if the Item exists
Laptop 40 Computer Keyboard
Computer 34 Laptop
Mobile 43 Iphone
Earphone 12 Keyboard
Pen 22 Mouse
Pencil 44 Wires
Screwdriver
Tape
mplete columns D and E
gible or not. Answer in column D

minor or not. for Minor return "Minor" and non minor = "Adult" anwswer in column E

Column E
Minor/Adult?
7.5% interest

£85,000.00
7.50%
5%

ssage is "High Rate"

s "Low Rate"
7.5% interest

£85,000.00
7.50%
5%

ssage is "High Rate"

s "Low Rate"
he units left in stock when selected from the list. In case the item is not available it shuld print "Out of Stock Message"

Step 1 Create a data validation list

Step 2 write a vlookup function with IFERROR Function to produce result.