The IF function mastered

Q. Part of my job involves keeping track of all the departments' budget status, which takes several hours each month. Is there a quicker way to do this?

A. The task alluded to in the question involves updating whether each department is over budget and also if some groups of departments are all over budget, or in some cases, if any of the departments in the groups are over budget. Fortunately, the time devoted to figuring all of that out can be cut dramatically with one of the most popular and useful functions in Excel, the IF function. Once you master the IF function, you will wonder how you ever lived without it.

You can follow the walk-throughs below in a video I created, at the bottom of the page. You can also download an Excel file with separate worksheets for each example.

Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

The IF function allows you to specify results based on whether a specific criterion is met. You can instantly make determinations or answer questions such as the following:

Which departments are over budget? You can spot them in the right column in the screenshot below.

Which loans qualify the salesperson to receive a bonus? In the screenshot below, the last column shows the employees eligible for a $10,000 bonus in rows 4, 8, 9, and 11.

Is each customer over the credit limit, near the credit limit, or neither over nor near the credit limit? Again, the answer is easy to spot in the last column in the screenshot below.

For each of the three examples, only one IF formula is created on the first row of the dataset and simply copied down to each row in the dataset. Now imagine your dataset contains 1,000 or 1 million rows. Creating just one formula to populate all rows or columns in your dataset is a huge time-saver. Not to mention, using a formula eliminates the human error that often occurs when manually populating a lot of information.

The IF function contains three arguments: Logical_test, Value_if_true, and Value_if_false. You type in the formula as IF (Logical_test, Value_if_true, Value_if_false), or you can use the Insert Function tool, which is the way I like to do it. Either way, the arguments are defined as follows:

The Logical_test is the condition(s) we want Excel to check and determine whether the outcome is true or false. For instance, the "test" might be "Is either department, A or B, over budget?," "Do the loan terms qualify the salesperson to receive a bonus?," or "Do we have at least 100 units of the product?" Of course, the "test" must be defined in a way that Excel can interpret as either true or false.

Value_if_true is the value returned if the logical test is true. Value_if_false is the value returned if the logical test is false. The values returned can be text, numbers, results of calculations, or results of functions.

Learning how to properly define these three arguments takes a little practice, but most people tend to grasp this relatively quickly, and the many, many ways you can use this function make it invaluable. Let's go through some examples.

For the first example, we want Excel to display the text "Over Budget" if the department is over budget or to display "Acceptable" if the department is not over budget. So, the Logical_test for this situation (in words) is "Is the department over budget?"

Put in a way that Excel can understand, our Logical_test for the first row, department A, is C2>B2. C2 is department A's actual amount, and B2 is department A's budgeted amount (see the screenshot below). Excel will determine if C2>B2 is true or false. Next, we need to define what action Excel will take if C2>B2 is true, or if it is false. In this example, we want to display the text "Over Budget" if the Logical_test is true (C2 is greater than B2), so we write "Over Budget" next to Value_if_true (as shown in the Function Arguments portion of the second screenshot below). We want to display the text "Acceptable" if the Logical_test is false (C2 is not greater than B2), so we write "Acceptable" next to Value_if_false. Click OK. Drag this formula down for each department.

For the second example, we want Excel to display the text "Over Budget" if both department A and department B are over budget, or display "Acceptable" if either department A or department B is not over budget. So, the Logical_test for this situation (in words) is "Are department A and department B over budget?" This is actually two tests — one to determine if department A is over budget and another to determine if department B is over budget. In English, we use the word "and" in between our tests, but in Excel we put the "and" in front of our tests. We use a comma to separate the tests and surround all of the tests in parentheses. In Excel, the Logical_test is AND(C2>B2,C3>B3).

C2 is department A's actual amount, B2 is department A's budgeted amount, C3 is department B's actual amount, and B3 is department B's budgeted amount. Excel will determine if C2>B2 and if C3>B3 is true or false. Then we define what action Excel will take if both C2>B2 and C3>B3 are true, or if it is false. In this example, we want to display the text "Over Budget" if the Logical_test is true (C2 is greater than B2, and C3 is greater than B3), so we write "Over Budget" next to Value_if_true (as shown in the screenshot above). We want to display the text "Acceptable" if the Logical_test is false (C2 is not greater than B2, and/or C3 is not greater than B3), so we write "Acceptable" next to Value_if_false. Click OK.

The third example is similar to the previous example, but now we want Excel to display the text "Over Budget" if either department A or department B is over budget, or display "Acceptable" if neither department A nor department B is over budget. So, the Logical_test for this situation (in words) is "Is department A or department B over budget?" Just as we put the word "and" in front of our tests in the last example, we put the word "or" in front of our two tests in this example, separating the tests with a comma and surrounding all of the tests with parentheses. In Excel, the Logical_test is OR(C2>B2,C3>B3) (see the screenshot below).

We define the Value_if_true as "Over Budget" and the Value_if_false as "Acceptable."

— By Kelly L. Williams, CPA, Ph.D.