In this article, you will get eleven Excel logical functions exercises. You will need an intermediate level of Excel knowledge to solve all the problems. You should know the following functions: AND, FALSE, IF, IFERROR, IFNA, IFS, NOT, OR, SWITCH, TRUE, and XOR. Additionally, you should know about nested IF functions, nested IF AND functions, returning TRUE or FALSE using the AND function, and using the AND function with text. These will aid you in solving all the problems. Additionally, Excel 2016 or later is required to successfully complete the exercises.
Download Practice Workbook
You can download the Excel file from the following link.
Problem Overview
There will be eleven exercises related to the Excel logical functions in this article. Our dataset 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. Here is a snapshot of the first problem.
Now, let’s state the exercise problems. 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 01 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 animated image shows the solution to the first problem. We’ve hidden some rows for better presentation.
- Exercise 02 Use of OR Function: Find when a laptop is delivered. Again, the output will be true or false.
- Exercise 03 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 04 Use of XOR Function: Return true if only one of the products’ cost is less than $2,000. Again, the output will be true or false.
Now, we have slightly altered the dataset. This will be applicable for exercises 5 and 6.
- Exercise 05 Combination of AND, OR Function: In this exercise, your task is to return true whenever a laptop or mobile phone is delivered by the “Astro” company.
- Exercise 06 Usage of the IF Function: Use the IF function to return “Same Day” and “Delayed” for the same day delivery and delayed delivery respectively.
Again, we have slightly changed and added a commission range to the dataset. This dataset is applicable for exercises 7 to 10.
- Exercise 07 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 08 Application of Nested IF: Calculate the commission rate using the nested IF.
- Exercise 09 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 again, but this time, using the IFS function. Additionally, use the IFNA function to neglect errors for the zero commission rates.
- Exercise 11 Combination of IF, AND, and OR Functions: One more time, we have changed the dataset. Now, 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.
Conclusion
Thank you for reading this article. By completing these practice exercises, we hope that you have gained knowledge about the logical functions of Excel. Moreover, you can find more articles similar to this on the ExcelDemy website. Additionally, if you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!