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.
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 I148 is: =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$173 is checked first by the SWITCH function. If it is true, then it returns the value of cell $C$173. If the first condition is false, the formula checks the next condition D148>=$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