Many Google Sheets users may not be familiar with the capabilities of using AND and OR functions with IFS. While these scenarios may not commonly occur, understanding how to correctly utilize these functions can greatly enhance your spreadsheet skills. In this article, we will explore the proper usage of AND and OR functions when combined with IFS in Google Sheets.
Understanding the Limitations
Before we delve into the tutorial on correctly utilizing AND and OR functions with IFS in Google Sheets, it’s important to note that in many cases, the IF function is more efficient when handling these logical tests. However, there are circumstances where IFS may be the better choice. Let’s take a look at some examples to shed light on when to avoid using AND and OR with IFS.
Example:
In this example, we will examine the monthly sales volume of a product and determine if certain conditions are met.
Formula #1:
If the sales volume in any month is greater than 1000, we want the formula to return the text “Target Met.” Otherwise, it should return “Not Met.”
- Using the OR function in combination with IF:
=IF(OR(B3>1000, C3>1000, D3>1000), "Target Met", "Not Met")- Using the OR function in combination with IFS:
=IFS(OR(B3>1000, C3>1000, D3>1000), "Target Met", 1*1=1, "Not Met")From the above formulas, you can see that the IF formula is easy to interpret. However, the IFS formula evaluates multiple conditions from left to right and returns the value corresponding to the first true condition. If no true condition is met, it will return a “#NA” error. In our example, this can lead to inconsistencies.
Formula #2:
If the sales volume in all three months is greater than 1000, the formula should return the text “Target Met.”
- Using the AND function in combination with IF:
=IF(AND(B3>1000, C3>1000, D3>1000), "Target Met", "Not Met")- Using the AND function in combination with IFS:
=IFS(AND(B3>1000, C3>1000, D3>1000), "Target Met", 1*1=1, "Not Met")Formula #3:
If the sales volume in any two months is greater than 3000, the formula should return the text “Target Met.”
- Using the OR and AND functions in combination with IF:
=IF(OR(AND(B3>3000, C3>3000), AND(B3>3000, D3>3000), AND(C3>3000, D3>3000)), "Target Met", "Not Met")- Using the OR and AND functions in combination with IFS:
=IFS(OR(AND(B3>3000, C3>3000), AND(B3>3000, D3>3000), AND(C3>3000, D3>3000)), "Target Met", 1*1=1, "Not Met")In all of the above formulas, you can see that using the IF function is more effective than using IFS. While IFS can be used in these scenarios, it can lead to more complex and potentially confusing formulas.
The Proper Use of AND and OR with IFS
Here, we will explain the correct usage of AND with IFS. In similar situations, you can use OR with IFS as well. Let’s consider the sales volume of Product 1 in our examples.
If the sum of the sales volume in all three months is less than 2500, the result should be “Poor.” If it’s between 2500 and 6000, the result should be “Average.” If it’s above 6000, the result should be “High.”
- Using IF with AND:
=IF(SUM(B3:D3)<2500, "Poor", IF(AND(SUM(B3:D3)>=2500, SUM(B3:D3)<6000), "Average", IF(SUM(B3:D3)>=6000, "High")))- Using IFS with AND:
=IFS(SUM(B3:D3)<2500, "Poor", AND(SUM(B3:D3)>=2500, SUM(B3:D3)<6000), "Average", SUM(B3:D3)>=6000, "High")In this scenario, IFS is more efficient than IF when it comes to handling multiple conditions.
Conclusion
With this tutorial, we hope you have gained a better understanding of how to correctly use AND and OR functions with IFS in Google Sheets.
While these functions may not always play a significant role when combined with IFS, having a solid understanding of how and when to use them can greatly improve your ability to handle logical tests in your spreadsheets.
Remember, a well-structured formula is not only reader-friendly but also allows for easy modification if needed in the future.
For more informative articles and helpful guides on Google Sheets and other spreadsheet-related topics, visit Crawlan.com.
