IFS functions in Google Sheets are an incredibly useful tool for managing spreadsheet formulas. IFS enables users to evaluate multiple expressions, each paired with a desired value, and return the value associated with the first expression that evaluates as true. This not only makes it easier to program a complex behavioral path, but reduces potential errors associated with having a long nested IF statement in your worksheet. The IFS function is executed by evaluating expressions in consecutive order and can be visualized using a flow chart as seen below: IFS allows you to set up “if-then” statements without writing an extensive chain of nested IFs. Any time you’re struggling with a lengthy formula or looking for greater control over your spreadsheet outcomes, IFS is definitely worth considering.
What is the Difference Between IF and IFS Functions?
The IF and IFS functions are two of the most commonly used functions in Google Sheets. Although they both perform similar tasks, there is a distinct difference between the two. The IF function searches for a condition and then returns a result depending on whether it is true or false. This can be helpful when performing tests or comparing values. On the other hand, the IFS function performs multiple tests at once. Each test consists of three parts: the value to evaluate, an operator to test that value, and a true/false output. However, unlike the IF function which only evaluates one condition, IFS will execute each test in order until one returns true. As such, this allows you to check multiple conditions over an entire range of data with fewer lines of code. To choose between IF and IFS, consider how many conditions need to be tested and the amount of data that needs to evaluated – the simpler task should be handled by IF and more complex tasks are better suited for IFS.
IFS Functions Google Sheets Syntax
=IFS(expression1, value1, [expression2, value2], …)
- Expression1 is the first condition in the list, it starts checking values.
- Value1 – the result that depends on the answer in the first condition. Only fires if the first condition returns TRUE.
- Expression2 is the next condition in order. In total, up to 127 conditions can be passed to one function, which will be checked sequentially.
- Value2 – the value that will be displayed if the second condition is met.
Things to understand about the IFS function:
- There must be a value after each condition. If the condition returns FALSE, nothing will happen, the function will simply jump to the next test. If the result is TRUE, the value that follows the condition is substituted.
- If none of the conditions are met, the #N/A! error.
- The function stops after it finds the first match. It doesn’t matter if this condition is followed by another that would also return TRUE, only the first condition in the list will work.
IFS Functions Google Sheets Examples
Below is a simple example of using the formula. Suppose you need to calculate which merchant is entitled to what percentage of the discount. For this, the monthly turnover is taken into account. Depending on the number of sales will change the discount percentage. In order not to do everything manually, you can make a simple formula. For our example, this is:
=ifs(B2<100,1,B2<200,3,B2<1000,5,B2<10000,10,B2>10000,30)
Here’s a slightly more complex formula design, but easier to edit and extend. It is enough to write the formula once, and then expand it to the desired amount. In the example, you need to replace the students’ scores for the control work with grades. The formula will cope with the task in a second. Here’s what the formula looks like:
=ifs(B2<$F$2,$G$2,B2<$F$3,$G$3,B2<$F$4,$G$4,B2<$F$5,$G$5,B2<$F$6,$G$6)
Conclusion
The IFS functions of Google Sheets will speed up the sorting of data and their comparison according to the specified parameters many times over. You can always see up-to-date information based on the data that you will enter into your document. Take 5 minutes to learn the IFS function and you will save many hours of time in the future, especially if your work involves filling out documents.
