Layout 1

26 HBMA RCM AD ISOR | QUARTER 1 | 2020

ight years ago, one my earliest articles for RCM Advisor

(this publication was then called Billing) was an intro-

duction to the IF function in Excel. IF is a powerful way to

add conditional logic to your spreadsheet. As a quick review,

the IF function has three components, or parameters. First,

IF needs a logical test that it can evaluate as true or false.

For example, are the billed charges in cell C5 over $1,000,

is the patient in cell N15 over age 65, or has the employee

in cell L28 met the FICA limit for the year? The second

parameter tells the IF function what to do if the logical test

is true. The third parameter tells the IF function what to do

if the logical test if false. An example of an IF statement

will follow later in this article.

Adding Power to Excel

Logical Formulas,

Part I: IFS By Nate Moore, CPA, MBA, FACMPE

FIGURE 1

Nested IF Statements

The basic logic of an IF statement was very helpful, so long as

you only had two conditions: true or false, A or B. If your test

required A, B, or C, IF was still an option, but it required nesting

IF statements. Nesting functions in an Excel formula means

to put one function inside another function. In other words, if

A is true, check to see if B is true. If A and B are both true,

return result 1. If A is true but B is false, return 2. If A and B

are both false, return 3. It is easy to see how nested IF functions

could quickly become unwieldy and difficult to maintain.

Look at the sample accounts receivable data in Figure 1.

Column E is an IF function that tests to see if column D is

greater than $500. If Column D is greater than $500, Column

27HBMA RCM AD ISOR | QUARTER 1 | 2020

SOFTWARE TALK

FIGURE 2

E is Yes, indicating to the billing staff to work that claim today.

The formula in cell E2 is =IF(D2> 500, “Yes,” “No”). The

logical test is D2>500. If cell D2 is greater than $500, the

cell returns Yes. If cell D2 is less than or equal to $500, the

cell returns No.

Figure 2 has a nested IF example. This time the billing

manager wants the team to work the claim if the dollar amount

is over $500 or if the claim is over 90 days, but not if the

claim is less than 30 days. The billing manager then added

another request to work any Medicare claims because of a

problem with the fiscal intermediaries’ software upgrade. The

nested IF functions to accomplish this request are in the

formula:

=IF(D2>500,"Yes",IF(C2="Over 90 Days", "Yes", IF(C2 = "0-

30 Days", "No", IF(B2="Medicare","Yes","No"))))

The billing manager’s request could have been a lot more

complicated. Keeping track of which statements must be true

to get the correct result can become tricky, especially if the

formula needs to be edited months after it was written.

The New IFS function

To make IF statements more powerful, faster to write, and

easier to maintain, Microsoft added a new IFS function. The

structure of IFS is a little different. Instead of a logical

condition, followed by what to do if the condition is true,

followed by what to do if the condition is false, IFS is a little

simpler. The syntax is =IFS(logical test 1, value if true 1, logical

test 2, value if true 2), and so on. An example of IFS is in

Column G of Figure 3.

The formula in cell G2 is:

=IFS(D2>500,"Yes",C2="Over 90 Days", "Yes", C2 = "0-30

Days", "No", B2="Medicare","Yes",TRUE, "No")

Notice how there is only one function, IFS, instead of the four

separate IF functions in the nested IF example. As a result,

28 HBMA RCM AD ISOR | QUARTER 1 | 2020

there is only one closing parentheses in the IFS example

compared to the four closing parentheses in the nested IF

example. Keeping track of the correct number of parentheses

can be a challenge with nested formulas. IFS evaluates the

conditions in order. The first time a condition evaluates to

true, IFS stops testing and returns the related value if true.

Given this logic, it is important to prioritize the conditions an

IFS statement in the formula carefully to ensure that the

function returns the results you expect.

The other important difference between nested IF functions

and IFS is what to do if a condition is false. IF statements,

nested or not, require what Excel calls a “value if false.” In

other words, what should the IF function do if the logical test

is false? The IFS function is different. The IFS function is only

a series of logical tests and what to do if the test is true.

There are no instructions or default if the logical test is false.

Look at my IFS example formula again:

=IFS(D2>500,"Yes",C2="Over 90 Days", "Yes", C2 = "0-30

Days", "No", B2="Medicare","Yes",TRUE, "No")

Notice how the very last test is TRUE. The keyword TRUE

simply tells Excel to evaluate this logical test as true. If none

of the logical tests in an IFS function are true, IFS will return

an #N/A error message. By making the last logical test TRUE,

you can control what happens if none of the preceding tests

are true. Since the IFS statement evaluates the conditions

in order, always make TRUE your last condition, and then put

the default value to return if all other tests are false.

The new IFS function makes complex logic easier to read

and document. Microsoft released a new SWITCH function

that also makes complex logic easier. Watch for more infor-

mation on SWITCH in the next issue of RCM Advisor. Please

review more function examples in the Excel Video playlists

mooresolutionsinc.com n

Nate Moore, CPA, MBA, FACMPE, writes custom SQL Server code

to mine practice management data for analysis in Excel, web

pages, and via email. Nate’s second book, “Better Data, Better

Decisions – The SQL: Business Intelligence for Medical Practices,”

was recently published by MGMA. His free Excel videos have been

viewed over 2 million times and are available at mooresolu-

tionsinc.com. Attend the HBMA Annual Conference to get hands-

on training with Moore.

SOFTWARE TALK

FIGURE 3