My Challenge

Excel Logical Functions Exercises
Exercise 01 Use of AND Function:
First Delivery Second

Date First Product Cost Delivery Second Product
Date
1-Mar-22 Laptop $2,479.94 1-Mar-22 Mobile Phone
15-Mar-22 Laptop $1,732.99 16-Mar-22 Laptop
28-Mar-22 Mobile Phone $699.95 29-Mar-22 Mobile Phone
29-Mar-22 Mobile Phone $949 29-Mar-22 Fitness Tracker
2-Apr-22 Mobile Phone $429 3-Apr-22 Fitness Tracker
5-Apr-22 Mobile Phone $1,525 6-Apr-22 Smart Watch
11-Apr-22 Laptop $1,799.99 13-Apr-22 Smart Watch
17-Apr-22 Mobile Phone $475 18-Apr-22 Smart Watch
23-Apr-22 Mobile Phone $449 24-Apr-22 Laptop
27-Apr-22 Fitness Tracker $149.95 28-Apr-22 Laptop
4-May-22 Fitness Tracker $129.95 6-May-22 Laptop
7-May-22 Smart Watch $399 7-May-22 Laptop
13-May-22 Smart Watch $183.90 14-May-22 Smart Watch
15-May-22 Smart Watch $299 16-May-22 Tablet
17-May-22 Tablet $560 19-May-22 Tablet
19-May-22 Tablet $565 19-May-22 Smart Watch
Exercise 02 Use of OR Function:

Second
First Delivery First Product Cost Delivery Second Product
Date Date
Exercise 03 Application of NOT Function:
First Delivery Second

First Product Cost Delivery Second Product
Date Date
Exercise 04 Use of XOR Function:

Second
First Delivery
Date First Product Cost Delivery Second Product
Date
Exercise 05 Combination of AND, OR Function:
Estimated First Product Cost Delivery Delivery Channel

Delivery Date Date
1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

15-Mar-22 Laptop $1,732.99 16-Mar-22 Bike
22-Mar-22 Laptop $3,164.99 23-Mar-22 Rented Car
28-Mar-22 Mobile Phone $699.95 29-Mar-22 Bike
29-Mar-22 Mobile Phone $949 29-Mar-22 Bike
2-Apr-22 Mobile Phone $429 3-Apr-22 Delivery Van
5-Apr-22 Mobile Phone $1,525 6-Apr-22 Rented Car
11-Apr-22 Laptop $1,799.99 13-Apr-22 Rented Car
17-Apr-22 Mobile Phone $475 18-Apr-22 Bike
27-Apr-22 Fitness Tracker $149.95 28-Apr-22 Bike
4-May-22 Fitness Tracker $129.95 6-May-22 Delivery Van
7-May-22 Smart Watch $399 7-May-22 Delivery Van
13-May-22 Smart Watch $183.90 14-May-22 Bike
15-May-22 Smart Watch $299 16-May-22 Rented Car
17-May-22 Tablet $560 19-May-22 Bike
19-May-22 Tablet $565 19-May-22 Delivery Van
Exercise 06 Usage of IF Function:

Estimated Delivery
Delivery Date First Product Cost Date Delivery Channel

11-Apr-22 Mobile Phone $475 13-Apr-22 Rented Car
23-Apr-22 Fitness Tracker $149.95 24-Apr-22 Delivery Van
7-May-22 Laptop $1,799.99 7-May-22 Delivery Van
Exercise 07 Use of SWITCH Function:

Delivery Date Date
Cost Commission
$500 1%
$1,200 3%
$1,700 7%
$2,800 15%
Exercise 08 Application of Nested IF:

Estimated Delivery

Cost Commission
$500 1%
$1,200 3%
$1,700 7%
$2,800 15%
Exercise 09 Combination of IF, AND Functions:

Estimated Delivery

Cost Commission
$500 1%
$1,200 3%
$1,700 7%
$2,800 15%
Exercise 10 Application of IFS Function:

Delivery Date Date
Cost Commission
$500 1%
$1,200 3%
$1,700 7%
$2,800 15%
Exercise 11 Combination of IF, AND, and OR Functions:

Estimated Delivery

ns Exercises
Delivery Delivered Laptop

Cost Person Twice
Now, let’s state the exercise problems. F
$699.95 Sam 0 two items in a single slot. This single slot
$3,164.99 Janet 1 possible on the same date, then it is fulfi
will be two product deliveries. This is app
$429 Sam 0
$1,525 Leonardo 0
$599 Patrick 0
$1,199.00 Heather 1
$449 Janet 0
$149.95 Daniel 0
$129.95 Stewart 0
$399 Sam 0
$183.90 Daniel 0
$299 Sam 0 Exercise 01 Use of AND Function: Th
number of occurrences where two la
$2,479.94 Janet 0
will be true or false.
$1,732.99 Patrick 0
$1,174.99 Sam 0
$1,799.99 Leonardo 0
$183.90 Patrick 0
$565 Leonardo 0
$560 Janet 0
$299 Sam 0
Cost Delivery Laptop Delivered

Person
$699.95 Sam 1
$3,164.99 Janet 1
$429 Sam 1
$1,525 Leonardo 1
$599 Patrick 1
$1,199.00 Heather 1 Exercise 02 Use of OR Function: Fi
$449 Janet 0 will be true or false.
$149.95 Daniel 0
$129.95 Stewart 0
$399 Sam 0
$183.90 Daniel 1
$299 Sam 0
$2,479.94 Janet 1
$1,732.99 Patrick 1
$1,174.99 Sam 1
$183.90 Patrick 0
$565 Leonardo 0
$560 Janet 0
$299 Sam 0
Delivery
Cost Same Day Delivered
Person
$699.95 Sam 1
$3,164.99 Janet 0
$429 Sam 0
$1,525 Leonardo 0
$599 Patrick 0
$1,199.00 Heather 0 Exercise 03 Application of NOT Fun
$449 Janet 0
same date as the first delivery. The ou
$149.95 Daniel 1
$129.95 Stewart 0
same date as the first delivery. The ou
$399 Sam 0
$183.90 Daniel 0
$299 Sam 0
$2,479.94 Janet 0
$1,732.99 Patrick 0
$1,174.99 Sam 0
$183.90 Patrick 0
$565 Leonardo 0
$560 Janet 0
$299 Sam 1
Delivery Only One Product

Cost Person Cost <$2000
$699.95 Sam 1
$3,164.99 Janet 1
$429 Sam 0
$1,525 Leonardo 0
$599 Patrick 1
$1,199.00 Heather 0
Exercise 04 Use of XOR Function: R
less than $2,000. Again, the output will
$449 Janet 0
$149.95 Daniel 0
$129.95 Stewart 0
$399 Sam 0
$183.90 Daniel 0
$299 Sam 0
$2,479.94 Janet 1
$1,732.99 Patrick 0
$1,174.99 Sam 0
$183.90 Patrick 0
$565 Leonardo 0
$560 Janet 0
$299 Sam 0
Delivery Delivery Laptop or Mobile

Company Person Delivered by Astro
Astro Sam 1
Chiral Janet 0
Astro Sam 1
Chiral Leonardo 0
Umbrella Patrick 0
Chiral Heather 0
Astro Sam 1
Umbrella Daniel 0 Exercise 05 Combination of AND, OR
Umbrella Stewart 0 return true whenever a laptop or mobile p
Astro Sam 1
Chiral Daniel 0
Astro Sam 1
Chiral Janet 0
Astro Sam 0
Umbrella Patrick 0
Umbrella Jennie 0
Astro Sam 0
Chiral Leonardo 0
Chiral Janet 0
Chiral Heather 0
Delivery Delivery
Company Person Delivery Type
Astro Sam Same Day

Chiral Janet Delayed
Astro Sam Delayed
Chiral Leonardo Delayed
Umbrella Patrick Delayed
Chiral Heather Delayed
Astro Sam Delayed
Umbrella Daniel Same Day
Exercise 06 Usage of the IF Funct
Umbrella Stewart Delayed and “Delayed” for the same day deli
Astro Sam Delayed
Chiral Daniel Delayed
Astro Sam Delayed
Astro Sam Delayed
Umbrella Patrick Delayed
Umbrella Jennie Same Day
Astro Sam Delayed
Chiral Leonardo Delayed
Chiral Heather Same Day
Delivery Delivery Commission

Company Person
Astro Sam 7%
Chiral Janet 7%
Astro Sam 1% Exercise 07 Use of SWITCH Fun
Chiral Leonardo 7% based on the product cost. The ra
dataset. Use the SWITCH function
Umbrella Patrick 15%
commission percentage.
Chiral Heather 1%
Astro Sam 1%
Umbrella Daniel 1%
Umbrella Stewart 0%
Astro Sam 3%
Chiral Daniel 0%
Astro Sam 0%
Chiral Janet 0%
Astro Sam 0%
Umbrella Patrick 0%
Umbrella Jennie 7%
Astro Sam 0%
Chiral Leonardo 0%
Chiral Janet 1%
Chiral Heather 1%
Delivery Delivery
Company Person Commission
Astro Sam 7%
Chiral Janet 7%
Astro Sam 1%
Chiral Leonardo 7%
Chiral Heather 1%
Astro Sam 1%
Umbrella Daniel 1% Exercise 08 Application of Nest
Umbrella Stewart 0% nested IF.
Astro Sam 3%
Chiral Daniel 0%
Astro Sam 0%
Chiral Janet 0%
Astro Sam 0%
Umbrella Patrick 0%
Umbrella Jennie 7%
Astro Sam 0%
Chiral Leonardo 0%
Chiral Janet 1%
Chiral Heather 1%
Delivery Delivery
Company Person Commission
Astro Sam 7%
Chiral Janet 7%
Astro Sam 1%
Chiral Leonardo 7%
Chiral Heather 1%
Astro Sam 1%
Umbrella Daniel 1% Exercise 09 Combination of
Umbrella Stewart 0% combining these functions.
Astro Sam 3%
Chiral Daniel 0%
Astro Sam 0%
Chiral Janet 0%
Astro Sam 0%
Umbrella Patrick 0%
Umbrella Jennie 7%
Astro Sam 0%
Chiral Leonardo 0%
Chiral Janet 1%
Chiral Heather 1%
Delivery Delivery Commission

Company Person
Astro Sam 7%
Chiral Janet 7%
Astro Sam 1%
Chiral Leonardo 7%
Chiral Heather 1%
Astro Sam 1%
Umbrella Daniel 1%
Exercise 10 Application of IFS
Umbrella Stewart 0% but this time, using the IFS funct
Astro Sam 3% errors for the zero commission ra
Chiral Daniel 0%
Astro Sam 0%
Chiral Janet 0%
Astro Sam 0%
Umbrella Patrick 0%
Umbrella Jennie 7%
Astro Sam 0%
Chiral Leonardo 0%
Chiral Janet 1%
Chiral Heather 1%
Delivery Delivery
Company Person Eligible to Leave
Astro Rachel Yes

Chiral Ross No
Astro Joey No
Chiral Chandler No
Umbrella Monica Yes
Chiral Phoebe No
Astro Ben No
Umbrella Adam No
Umbrella Rowan No Exercise 11 Combination of IF, A
changed the dataset. Now, an em
Astro Ellie No Delivers a product costing more th
Chiral Britt No Either same day delivery or the de
Astro Alan No Using these two conditions, create
for leave and return “Yes” or “No”.
Chiral Hamish No
Astro Byron No
Umbrella Patrick No
Umbrella Jennie No
Astro Clark No
Chiral Morrison No
Chiral Parkinson No
Chiral Coll No
the exercise problems. For a delivery person to be paid, they must deliver
single slot. This single slot means delivery in the same row. If delivery is not
same date, then it is fulfilled at a later date. So on each row of the data, there
duct deliveries. This is applicable for the first four exercises.
Use of AND Function: The delivered products are given. Your task is to find the
occurrences where two laptops delivered in a single slot (same row). The output
or false.
se of OR Function: Find when a laptop is delivered. Again, the output
alse.
pplication of NOT Function: Check if the second delivery is on the

he first delivery. The output will be true or false.
he first delivery. The output will be true or false.
se of XOR Function: Return true if only one of the products’ cost is

0. Again, the output will be true or false.
mbination of AND, OR Function: In this exercise, your task is to
ver a laptop or mobile phone is delivered by the “Astro” company.
Usage of the IF Function: Use the IF function to return “Same Day”
d” for the same day delivery and delayed delivery respectively.
7 Use of SWITCH Function: The delivery person will receive commission

he product cost. The range for the commission is given below the main
se the SWITCH function with the TRUE function to return the applicable
n percentage.
8 Application of Nested IF: Calculate the commission rate using the
se 09 Combination of IF and AND Functions: Find the commission rate by
ing these functions.
10 Application of IFS Function: Your task is to find the commission again,
me, using the IFS function. Additionally, use the IFNA function to neglect
the zero commission rates.
11 Combination of IF, AND, and OR Functions: One more time, we have
he dataset. Now, an employee will be eligible for a paid leave if he or she
product costing more than 2000 and
e day delivery or the delivered product is laptop
e two conditions, create a formula to find the employees that are eligible
nd return “Yes” or “No”.

My Challenge

Uploaded by

Copyright:

Available Formats

My Challenge

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

My Challenge

Uploaded by

Copyright:

Available Formats

Excel Logical Functions Exercises

Exercise 01 Use of AND Function:

First Delivery Second

Exercise 02 Use of OR Function:

Exercise 03 Application of NOT Function:

First Delivery Second

Exercise 04 Use of XOR Function:

Exercise 05 Combination of AND, OR Function:

Estimated First Product Cost Delivery Delivery Channel

1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

Exercise 06 Usage of IF Function:

1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

Exercise 07 Use of SWITCH Function:

Exercise 08 Application of Nested IF:

1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

Exercise 09 Combination of IF, AND Functions:

1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

Exercise 10 Application of IFS Function:

Exercise 11 Combination of IF, AND, and OR Functions:

1-Mar-22 Laptop $2,479.94 1-Mar-22 Delivery Van

Delivery Delivered Laptop

Cost Delivery Laptop Delivered

Delivery Only One Product

Delivery Delivery Laptop or Mobile

Astro Sam Same Day

Delivery Delivery Commission

Delivery Delivery Commission

Astro Rachel Yes

pplication of NOT Function: Check if the second delivery is on the

se of XOR Function: Return true if only one of the products’ cost is

7 Use of SWITCH Function: The delivery person will receive commission

You might also like