IFS

IFS(logical_test1, value_if_true1 [,logical_test2, value_if_true2] [..])

Returns the value based on multiple True or False conditions.

logical_test1Condition that evaluates to True or False.
value_if_true1Result if the logical value evaluates to True.
logical_test2(Optional) Condition that evaluates to True or False.
value_if_true2(Optional) Result if the logical value evaluates to True.

REMARKS
* This function was added in Excel 2019 to remove nested IF functions.
* For an illustrated example refer to the page under Advanced Functions
* For an overview of conditional formulas refer to this page
* This function is not case sensitive when matching text strings.
* This function does not support wildcards (? and *).
* It is possible to specify a default value by adding a condition that will always be true as your last condition.
* If you do not provide a corresponding value for each logical test then the pop up message "you have entered too few arguments for this function" is displayed.
* If any logical_text argument does not evaluate to either True or False, then #VALUE! is returned.
* You can have a maximum of 127 (logical_tests and values).
* If no conditions are found, then #N/A! is returned.
* If the function is not available, then #NAME? is returned.
* You can use the IF function to return a value based on one condition.
* You can use the IFERROR function to return the value or something else if it evaluates to an error.
* You can use the SWITCH function to return a value based on a list of exact matches.
* There are also AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS and SUMIFS functions.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 AB
1=IF(B2<10, 10, IF(B2<=20, 20, IF(B2<=30, 30, IF(B2<=40, 40, "over 40")))) = 2010
2=IFS(B2<10, 10, B2<=20, 20, B2<=30, 30, B2<=40, 40, B2>40, "over 40") = 2014
3=IF(B4="one", 1, IF(B4="two", 2, IF(B4="three", 3, IF(B4="four", 4, 5)))) = 141
4=IFS(B4="one", 1, B4="two", 2, B4="three", 3, B4="four", 4, ISTEXT(B4)=TRUE, 5) = 1one
5=SWITCH(B4, "one", 1, "two", 2, "three", 3, "four", 5) = 1two
6=IFS(B1=10, B4, B2=20, B5) = one 
7=IFS(B2=15, B4, B2=20, B5) = #N/A 
8=IFS(B1>10, "false", TRUE, "something else") = something else 
9=IFS(FALSE, "1stFalse", FALSE, "2ndFalse", TRUE, "1stTrue") = 1stTrue 
10=IFS(B1*"some text", "not logical") = #VALUE! 
11=IFS(B1>10, "B1", B2>20, "B2", B3<30, "B3") = #N/A 

1 - What value is returned when cell "B2" equals 14 using nested IF functions.
2 - What value is returned when cell "B2" equals 14 without nested IF functions.
3 - What value is returned when cell "B4" equals "one" using nested IF functions.
4 - What value is returned when cell "B4" equals "one" without nested IF functions.
5 - What value is returned when cell "B4" equals "one" using the SWITCH function.
6 - What value is returned when the logical test "B1=10" is evaluated.
7 - What value is returned when the logical test "B2=15" is evaluated.
8 - What value is returned when the logical text "B1>10" is evaluated.
9 - What is returned when the first two logical tests evaluate to False.
10 - What is returned when the logical test is not a valid expression.
11 - What is returned when none of the logical tests are True.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top