Excel Exercises with 9 Logical Functions

Get FREE Advanced Excel Exercises with Solutions!

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.

Problem Overview of Excel Logical Functions Exercises

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.

Excel Logical Functions Exercises


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!

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

4 Comments
  1. 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.

        • Avatar photo
          Shamima Sultana Feb 12, 2024 at 1:39 PM

          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.

          Exercise-5 solution

          Regards
          ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo