## Download Practice Workbook

## Problem Overview

Our sample dataset below has 7 columns and 20 rows. You will show the output in the eighth column (the blue column). The exercises are on the “Problem” sheet, and the solutions to those problems are on the “Solution” sheet.

For a delivery person to be paid, they must deliver two items in a single slot. This single slot means delivery in the same row. If delivery is not possible on the same date, then it is fulfilled at a later date. So, on each row of the data, there will be two product deliveries. This is applicable for the first four exercises.

**Exercise 1 – Use of AND Function:**The delivered products are given. Your task is to find the number of occurrences where two laptops delivered in a single slot (same row). The output will be true or false.

This image shows the solution to the first problem. We’ve hidden some rows for better presentation.

**Exercise 2 – Use of OR Function:**Find when a laptop is delivered. The output will be true or false.**Exercise 3 – Application of NOT Function:**Check if the second delivery is on the same date as the first delivery. The output will be true or false.**Exercise 4 – Use of XOR Function:**Return true if only one of the products’ cost is less than $2,000. The output will be true or false.

We have altered the dataset. This will be applicable for exercises 5 and 6.

**Exercise 5 – Combination of AND, OR Function:**Your task is to return true whenever a laptop or mobile phone is delivered by the “Astro” company.**Exercise 6 – Usage of the IF Function:**Use the**IF**function to return “Same Day” and “Delayed” for the same day delivery and delayed delivery respectively.

We have changed and added a commission range to the dataset. This dataset is applicable for exercises 7 to 10.

**Exercise 7 – Use of SWITCH Function:**The delivery person will receive commission based on the product cost. We have provided the range for the commission below the main dataset. Use the**SWITCH**function with the**TRUE**function to return the applicable commission percentage.**Exercise 8 – Application of Nested IF:**Calculate the commission rate using the nested**IF**.**Exercise 9 – Combination of IF and AND Functions:**Find the commission rate by combining these functions.**Exercise 10 – Application of IFS Function:**Your task is to find the commission, but this time, using the**IFS**Use the**IFNA**function to neglect errors for the zero commission rates.**Exercise 11 – Combination of IF, AND, and OR Functions:**We have changed the dataset. An employee will be eligible for a paid leave if he or she- Delivers a product costing more than $2,000 and
- Either same day delivery or the delivered product is laptop
- Using these two conditions, create a formula to find the employees that are eligible for leave and return “Yes” or “No”.

The following image shows the solution to the first problem.

Get FREE Advanced Excel Exercises with Solutions!
how switch function work for the exercise 7 =SWITCH(TRUE,D7>=$B$173,$C$173,D148>=$B$172,$C$172,D148>=$B$171,$C$171,D148>=$B$170,$C$170,0%)

how it works if vedio is there kindly update

Hello AMAR,

Thanks for commenting. There is a typing mistake in your formula.

The formula for exercise 7 which we applied to

cell I148is:=SWITCH(TRUE,D148>=$B$173,$C$173,D148>=$B$172,$C$172,D148>=$B$171,$C$171,D148>=$B$170,$C$170,0%)Formula Explanation:The condition

D148>=$B$173is checked first by theSWITCHfunction. If it is true, then it returns the value of cell$C$173. If the first condition is false, the formula checks the next conditionD148>=$B$172. If this is true, then it returns the value of cell$C$172.This pattern continues for the following conditions and their respective values. If none of the conditions are true, the default value returned is

0%.We regret to inform you that we are currently unable to provide the video you requested. As we haven’t made any video on this topic.

If you have any specific questions or if you need further clarification, feel free to ask!

Regards,

Mizbahul Abedin

Team ExcelDemy

Hey !

Please help me with excercise 5.

Hello

Smn,Formula of Exercise -5:

=AND(G102=”Astro”,OR(C102=”Laptop”,C102=”Mobile Phone”))is used to check multiple conditions using the AND and OR logical functions.Here,

G102=”Astro”: It checks the value of G102 is equal to “Astro” or not.OR(C102=”Laptop”,C102=”Mobile Phone”): It checks the value of cell C102 is equal to either “Laptop” or “Mobile Phone”. Here, the OR function will return TRUE if any of the conditions are TRUE.AND(G102=”Astro”, OR(C102=”Laptop”, C102=”Mobile Phone”)): Here, the AND function combines the two conditions. It will return TRUE only if both/two conditions are TRUE.Regards

ExcelDemy