Logical Functions IF and new IfS Function in Microsoft Excel 365

True or False, this is the question?

When it comes in Excel, it is certainly the question, and this is where the IF Statement and the brand new IFS Statement appears. What do they do?

IFS Formula in Excel 365

The IF Function checks whether a condition is met, returns one value If True and another value If False. In other words, it is an instruction that checks any condition, and if that condition is to found to be TRUE then it returns a predefined value however, and if the condition is FALSE, it returns a different predefined value.

The IFS Function checks whether on or more conditions are met and returns a value that corresponds to the first TRUE condition. The IFS Function can take the place of multiple nested IF statements, and it is much easier to read with multiple conditions.

To either select the If or IFS Function we must select the Formula tab of the ribbon and locate the area of the ribbon named Function Library.

Formalas Tab - Formulas Library

Once we have located the Function Library, we select the Logical category as shown below. In the next image I have select the IFS Function.

Formulas - Logical - IFS

The syntax of If Function is the following:

Here, ‘Logic_Test’ refers to the expression that is to be evaluated.

  • Value_if_True’ is the output of IF Statement if the ‘Logic_Test’ is TRUE.
  • Value_if_False’ is the output of IF Statement if the ‘Logic_Test’ is FALSE.

In the next image, we can see the Function Arguments of the IFS dialog box. The syntax of the IFS Function is the following:

IFS ([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])

Function Arguments

In the following image, we have available the Scores of the Students. We want to use the If Function, so we can find out who will Fail or Pass the Class.

The criteria are the following:

  • Logical_test: [@Scores] or B2<=55
  • Value_if_true: If the Students grade are Below or Equal to 55 they Fail (<=55 “Fail”)
  • Value_if_false: If the Students Grades are Above 55 they Pass (<55 “Pass”)

IF Function Criteria

In the following image, we have available the Scores of the Students. We want to use the IFS Function, so we can find out the Grades that they will receive.

The criteria are the following:

  • Logical_test1: B13>89
  • Value_if_true1: “A”
  • Logical_test2: B13>79
  • Value_if_true2: “B”
  • Logical_test3: B13>69
  • Value_if_true3: “C”
  • Logical_test4: B13>59
  • Value_if_true4: “D”
  • Logical_test5: TRUE
  • Value_if_true5: “F” because 56 doesn’t meet any conditions. “TRUE” and its corresponding value “F” provide a default value because the other conditions aren’t met.

IFS Function Criteria

Are few things to keep in mind:

  • We can use 127 different conditions with the use of the IFS Function.
  • It is very hard to use multiple IF or IFS Functions, because they need to be entered in the correct order, which makes them very difficult to build, test and update.
  • The If Function can return only on value out of ‘Value_if_True and ‘Value_if_False’. Both the values cannot be returned.
  • The Nesting of IF Functions are possible, but we are limited only up to 64.
  • If we see an #Name? error, it means that the expression that we are evaluation is invalid.

Below you can check out the video describing the IF and IFS Function in Excel 365.

Don’t Forget To Subscribe To My YouTube Channel.

YouTube Channel

About Smart Office - philippospan

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on November 12, 2018, in Excel 365 English, Microsoft Office 365 ProPlus English and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink. Comments Off on Logical Functions IF and new IfS Function in Microsoft Excel 365.

Comments are closed.