With the release of Excel 2019, Microsoft added the IFS function to its spreadsheet application. Although IFS appeared in 2016 for Office 365 subscribers, Microsoft did not include it with Excel 2016. Essentially, the IFS function allows users to create formulas that test for multiple conditions without repeating an IF function multiple times in the same formula. Read on, and in this tip, you will learn how you can create very powerful formulas using IFS.
Creating Formulas with IFS
Suppose you need to create a report that summarizes financial information by a fiscal year that begins on April 1 and ends on March 31. More specifically, you need to summarize the data by fiscal quarter. Among the options for completing this task is to add formulas to the source transactional data that identifies the appropriate fiscal quarter and fiscal year and then using the data in those columns as the foundation for summarizing data by fiscal reporting period. The figure below shows how this might be done using both the new IFS function and the more traditional approach of using repeated IF functions in the same formula. Notice that although both formulas provide the same result, the formula that incorporates the IFS function is a simpler, shorter formula than the one the incorporates multiple IF statements.
With the 2019 release of Excel, Microsoft added several new functions to Excel. One of these new functions – IFS – allows you to create formulas that perform multiple tests without having to repeat an IF statement multiple time within the same formula. So, if you are running Excel 2019 or if your instance of Excel is provisioned through a Microsoft Office 365 subscription, be sure to take advantage of this great new function.