Functions IFS and SWITCH

Excel that comes with Office365 brings two new logical functions: IFS and SWITCH. The first of them consists of a number of conditions, and returns the selected value after the first fulfilled condition. The other starts with a condition, and then, depending on the values ​​that meet the requirement returns appropriate value. Sometimes these functions can be very useful, and how to use them I’ll show in following recipe.

The IFS function has the following syntax:

IFS (<condition 1>, <value 1>, …, <condition n>, <value n>)

Arguments of this function are pairs  of conditions and values ​​that function returns if the first true condition is met. You must specify at least one pair of conditions-values, and you can enter maximum 127 pairs. For example, if we want to classify items in a particular ranking in the price list, depending on the price, we will write the formula:

= IFS (D2> 5, “HIGH”, D2> 3, “MEDIUM”, D2 <3, “LOW”)

Further should copy the formula into the remaining cells in the column. Excel first checks the first condition, so if it is filled out, it displays the corresponding value. If it does not, Excel switches to the next condition until it finds the condition that has been met.

The SWITCH function is used for conditional branching, and its syntax is:

SWITCH (<condition>, <value>, <return value> …)

For example, if we get a date, and we want to determine the day in a week we should write a formula:

= SWITCH (WEEKDAY (C13,2), 1, “MON”, 2, “TUE”, 3, “WEN”, 4, “THU”, 5, “FRI”, 6, “SAT”, 7, “SUN “)

As a condition we set the function WEEKDAY which returns the day of the week as a number. Depending on the number, the corresponding text will indicate the day of the week.