Chapter 6
Working with logical and information functions
In this chapter, you will:
Add logical tests to your formulas with the
IF()functionPerform multiple logical tests with nested
IF()functions and theAND()andOR()functionsCreate powerful formulas that combine logical tests with arrays
Interrogate Excel using the information functions
Count blanks, errors, and similar value with Excel’s IS functions
I mentioned in Chapter 4, “Understanding functions,” that one of the advantages of using Excel’s worksheet functions is that they enable you to build formulas that perform actions that are simply not possible with the standard operators and operands.
This idea becomes readily apparent when you learn about functions that can add intelligence and knowledge—the two cornerstones of good business analysis—to your worksheet models. You get these via Excel’s logical and information functions, which I describe in detail in this chapter.
Adding intelligence with logical functions
In the computer world, we very loosely define something as intelligent if it can perform tests on its environment and act in accordance with the results of those tests. However, computers are binary beasts, so “acting in accordance with the results of a test” means that the machine can do only one of two things. Still, even with this limited range of options, you’ll be amazed at how much intelligence you can bring to your worksheets. Your formulas will actually be able to test the values in cells and ranges and then return results based on those tests.
This is all done with Excel’s logical functions, which are designed to create decision-making formulas. For example, you can test cell contents to see whether they’re numbers or labels, or you can test formula results for errors. Table 6-1 summarizes Excel’s logical functions.
TABLE 6-1 Excel’s logical functions
Function |
Description |
AND(logical1[,logical2],...) |
Returns TRUE if all the arguments are true. |
FALSE() |
Returns FALSE. |
IF(logical_test,value_if_true [,value_if_false]) |
Performs a logical test and returns a value based on the result. |
IFERROR(value, value_if_error) |
Returns value_if_error if value is an error. |
IFNA(value, value_if_na) |
Returns value_if_na if value returns the #N/A error. |
IFS(logical_test1, value_if_true1,[ logical_test2, value_if_true2, ...]) |
Performs one or more logical tests and returns a value from the first test that returns TRUE. |
NOT(logical) |
Reverses the logical value of the argument. |
OR(logical1[,logical2],...) |
Returns TRUE if any argument is true. |
TRUE() |
Returns TRUE. |
XOR(logical1[,logical2],...) |
Returns TRUE if one and only one of the arguments is true. |
To learn about the IFERROR() and IFNA() functions, see Chapter 3, “Troubleshooting formulas.”
Using the IF() function
I’m not exaggerating even the slightest when I tell you that the royal road to becoming an accomplished Excel formula builder involves mastering the IF() function. If you become comfortable wielding this function, a whole new world of formula prowess and convenience opens up to you. Yes, IF() is that powerful.
To help you master this crucial Excel feature, I’m going to spend a lot of time on it in this chapter. I give you copious examples that show you how to use it in real-world situations.
IF(): The simplest case
Let’s start with the simplest version of the IF() function:
IF(logical_test, value_if_true)
logical_test |
A logical expression—that is, an expression that returns TRUE or FALSE (or their equivalent numeric values: 0 for FALSE and any other number for TRUE) |
value_if_true |
The value returned by the function if logical_test evaluates to TRUE |
For example, consider the following formula:
=IF(A1 >= 1000, "It's big!")
The logical expression A1 >= 1000 is used as the test. Let’s say you enter this formula in cell B1. If the logical expression proves to be true (that is, if the value in cell A1 is greater than or equal to 1,000), the function returns the string It's big!, and that’s the value you see in cell B1. (If A1 is less than 1,000, you see the value FALSE in cell B1 instead.)
Another common use for the simple IF() test is to flag values that meet a specific condition. For example, suppose you have a worksheet that shows the percentage increase or decrease in the sales of a long list of products. It would be useful to be able to flag just those products that had a sales decrease. A basic formula for doing this would look something like this:
=IF(cell < 0, flag)
Here, cell is the cell you want to test, and flag is some text that you use to point out a negative value. Here’s an example:
=IF(B2 < 0, "<<<<<")
A slightly more sophisticated version of this formula would vary the flag, depending on the negative value. That is, the larger the negative number, the more less-than signs (in this case) the formula would display. This can be done using the REPT() function, which I discussed in Chapter 5, “Working with text functions”:
REPT("<", B2 * -100)
This expression multiplies the percentage value by -100 and then uses the result as the number of times the less-than sign is repeated. Here’s the revised IF() formula:
=IF(B2 < 0, REPT("<", B2 * -100))
Figure 6-1 shows how it works in practice.
Handling a FALSE result
As you can see in Figure 6-1, if the result of the IF() condition calculates to FALSE, the function returns FALSE as its result. That’s not inherently bad, but the worksheet would look tidier (and, hence, be more useful) if the formula returned, say, the null string ("") instead.
To do this, you need to use the full IF() function syntax:
IF(logical_test, value_if_true, value_if_false)
logical_test |
A logical expression |
value_if_true |
The value returned by the function if logical_test evaluates to TRUE |
value_if_false |
The value returned by the function if logical_test evaluates to FALSE |
For example, consider the following formula:
=IF(A1 >= 1000, "It's big!", "It's not big!")
This time, if cell A1 contains a value that’s less than 1,000, the formula returns the string It's not big!.
For the negative value flag example, use the following revised version of the formula to return no value if the cell contains a nonnegative number:
=IF(B2 < 0, REPT("<", B2 * -100), "")
As you can see in Figure 6-2, the resulting worksheet looks much tidier than the first version.
Avoiding division by zero
As you saw in Chapter 3, Excel displays the #DIV/0! error if a formula tries to divide a quantity by zero. To avoid this error, you can use IF() to test the divisor and ensure that it’s nonzero before performing division.
For example, the basic equation for calculating gross margin is (Sales — Expenses)/Sales. To make sure that Sales isn’t zero, use the following formula (assuming that you have cells named Sales and Expenses that contain the appropriate values):
=IF(Sales <> 0, (Sales - Expenses)/Sales, "Sales are zero!")
If the logical expression Sales <> 0 is true, that means Sales is nonzero, so the gross margin calculation can proceed. If Sales <> 0 is false, the Sales value is 0, so the message Sales are zero! is displayed instead.
Performing multiple logical tests
The capability to perform a logical test on a cell is a powerful weapon indeed. You’ll find endless uses for the basic IF() function in your everyday worksheets. The problem, however, is that the everyday world often presents us with situations that are more complicated than can be handled in a basic IF() function’s logical expression. It’s often the case that you have to test two or more conditions before you can make a decision.
To handle these more complex scenarios, Excel offers several techniques for performing two or more logical tests: nested IF() functions, the IFS() function, the AND() function, and the OR() function. You’ll learn about these techniques over the next few sections.
Nested IF() functions
When building models using IF(), it’s common to come upon a second fork in the road when evaluating either the value_if_true or value_if_false argument.
For example, consider the variation of our formula that outputs a description based on the value in cell A1:
=IF(A1 >= 1000, "Big!", "Not big")
What if you want to return a different string for values greater than, say, 10,000? In other words, if the condition A1 >= 1000 proves to be true, you want to run another test that checks to see whether A1 >= 10000. You can handle this scenario by nesting a second IF() function inside the first as the value_if_true argument:
=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", "Big!"), "Not big")
If A1 >= 1000 returns TRUE, the formula evaluates the nested IF(), which returns Really big!! if A1 >= 10000 is TRUE and returns Big! if it’s FALSE; if A1 >= 1000 returns FALSE, the formula returns Not big.
Note, too, that you can nest the IF() function in the value_if_false argument. For example, if you want to return the description Small for a cell value less than 100, you use this version of the formula:
=IF(A1 >= 1000, "Big!", IF(A1 < 100, "Small", "Not big"))
Calculating tiered bonuses
A good time to use nested IF() functions arises when you need to calculate a tiered payment or charge. That is, if a certain value is X, you want one result; if the value is Y, you want a second result; and if the value is Z, you want a third result.
For example, suppose you want to calculate tiered bonuses for a sales team as follows:
If the salesperson did not meet the sales target, no bonus is given.
If the salesperson exceeded the sales target by less than 10%, a bonus of $1,000 is awarded.
If the salesperson exceeded the sales target by 10% or more, a bonus of $10,000 is awarded.
Assuming that cell D2 contains the percentage that each salesperson’s actual sales were above or below his target sales, here’s a formula that handles these rules:
=IF(D2 < 0, "", IF(D2 < 0.1, 1000, 10000))
If the value in D2 is negative, nothing is returned; if the value in D2 is less than 10%, the formula returns 1000; if the value in D2 is greater than or equal to 10%, the formula returns 10000. Figure 6-3 shows this formula in action.
The IFS() function
Nesting one IF() function inside another is a handy way to perform a couple of logical tests, but the method quickly becomes unwieldly and difficult to decipher when the nesting goes three or more IF() functions deep. If your data analysis requires more than two logical tests, then you can make your worksheet model easier to read by turning to the IFS() function:
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2,...])
logical_test1, logical_test2,... |
Logical expressions—that is, expressions that return TRUE or FALSE (or their equivalent numeric values: 0 for FALSE and any other number for TRUE) |
value_if_true1, value_if_true2,... |
Value returned by the function if the corresponding logical test is the first to evaluate to TRUE |
The IFS() function consists of a series of logical tests, each of which has an associated return value. IFS() performs each logical test in turn and, when it comes across the first logical test to return TRUE, it returns that logical test’s associated value.
For example, consider the following formula:
=IFS(A1 >= 10000, "Really big!!", A1 >= 1000, "Big!", A1 < 1000, "Not big")
There are three logical expressions here: A1 >= 10000, A1 >= 1000, and A1< 1000. The first of these that calculates to TRUE gets its associated value (“Really big!!”, “Big!”, or “Not big”, respectively) returned by the function.
What if none of the logical tests return TRUE? In that case, IFS() returns the #NA error. To avoid this, you can set up a default return value by setting the last logical “test” to TRUE and then specifying the value you want to use as the default. Here’s an example:
=IFS(A1 >= 10000, "Really big!!", A1 >= 1000, "Big!", TRUE, "Not big")
Here’s how you’d use IFS() to calculate the tiered bonuses that I introduced in the previous section:
=IFS(D2 < 0, "", D2 < 0.1, 1000, D2 >= 0.1, 10000)
The AND() function
It’s often necessary to perform an action if and only if two conditions are true. For example, you might want to pay a salesperson a bonus if and only if dollar sales exceeded the budget and unit sales exceeded the budget. If either the dollar sales or the unit sales fell below budget (or if they both fell below budget), no bonus is paid. In Boolean logic, this is called an And condition because one expression and another must be true for a positive result.
In Excel, And conditions are handled, appropriately enough, by the AND() logical function:
AND(logical1 [,logical2,...])
logical1 |
The first logical condition to test |
logical2,... |
The second logical condition to test |
You can enter up to 255 logical conditions.
The AND() result is calculated as follows:
If all the arguments return TRUE (or any nonzero number), AND() returns TRUE.
If one or more of the arguments return FALSE (or 0), AND() returns FALSE.
You can use the AND() function anywhere you would use a logical formula, but it’s most often pressed into service as the logical condition in an IF() function. In other words, if all the logical conditions in the AND() function are TRUE, IF() returns its value_if_true result; if one or more of the logical conditions in the AND() function are FALSE, IF() returns its value_if_false result.
For example, suppose you want to pay out a bonus only if a salesperson exceeds his budget for both dollar sales and unit sales. Assuming that the difference between the actual and budgeted dollar amounts is in cell B2 and the difference between the actual and budgeted unit amounts is in cell C2, here’s an example of a formula that determines whether a bonus is paid:
=IF(AND(B2 > 0, C2 > 0), "1000", "No bonus")
If the value in B2 is greater than 0 and the value in C2 is greater than 0, the formula returns 1000; otherwise, it returns No bonus.
Slotting values into categories
A good use for the AND() function is to slot items into categories that consist of a range of values. For example, suppose you have a set of poll or survey results, and you want to categorize these results based on the following age ranges: 18–34, 35–49, 50–64, and 65+. Assuming that each respondent’s age is in cell B9, the following AND() function can serve as the logical test for entry into the 18–34 category:
AND(B9 >= 18, B9 <= 34)
If the response is in C9, the following formula will display it if the respondent is in the 18–34 age group:
=IF(AND(B9 >= 18, B9 <= 34), C9, "")
Figure 6-4 tries this on some data. Here are the formulas used for the other age groups:
35-49: =IF(AND(B9 >= 35, B9 <= 49), C9, "") 50-64: =IF(AND(B9 >= 50, B9 <= 64), C9, "") 65+: =IF(B9 >= 65, C9, "")
The OR() function
Similar to an And condition is the situation when you need to take an action if one thing or another is true. For example, you might want to pay a salesperson a bonus if she exceeded the dollar sales budget or if she exceeded the unit sales budget. In Boolean logic, this is called an Or condition.
You won’t be surprised to hear that Or conditions are handled in Excel by the OR() function:
OR(logical1 [,logical2,...])
logical1 |
The first logical condition to test |
logical2,... |
The second logical condition to test |
You can enter up to 255 logical conditions.
The OR() result is calculated as follows:
If one or more of the arguments return TRUE (or any nonzero number), OR() returns TRUE.
If all of the arguments return FALSE (or 0), OR() returns FALSE.
As with AND(), you use OR() wherever a logical expression is called for, most often within an IF() function. This means that if one or more of the logical conditions in the OR() function are TRUE, IF() returns its value_if_true result; if all the logical conditions in the OR() function are FALSE, IF() returns its value_if_false result.
For example, suppose you want to pay out a bonus only if a salesperson exceeds her budget for either dollar sales or unit sales (or both). Assuming that the difference between the actual and budgeted dollar amounts is in cell B2 and the difference between the actual and budgeted unit amounts is in cell C2, here’s an example of a formula that determines whether a bonus is paid:
=IF(OR(B2 > 0, C2 > 0), "1000", "No bonus")
If the value in B2 is greater than 0 or the value in C2 is greater than 0, the formula returns 1000; otherwise, it returns No bonus.
Note
The OR() function returns TRUE when one or more of its arguments are TRUE. However, in some cases, you want an expression to return TRUE only when just one of the arguments is TRUE. In that case, use the XOR() function, which returns TRUE when one and only one of its arguments evaluates to TRUE.
Applying conditional formatting with formulas
The powerful conditional formatting features available in Excel enable you to highlight cells, create top and bottom rules, and apply three types of formatting: data bars, color scales, and icon sets. Excel comes with another conditional formatting component that makes this feature even more powerful: You can apply conditional formatting based on the results of a formula. In particular, you can set up a logical formula as the conditional formatting criteria. If that formula returns TRUE, Excel applies the formatting to the cells; if the formula returns FALSE, instead, Excel doesn’t apply the formatting. In most cases, you use an IF() function, often combined with another logical function such as AND() or OR().
Before I get to an example, here are the basic steps to follow to set up formula-based conditional formatting:
Select the cells to which you want the conditional formatting applied.
Select Home > Conditional Formatting > New Rule. Excel displays the New Formatting Rule dialog box.
Select Use A Formula To Determine Which Cells To Format.
In the Format Values Where This Formula Is True range box, type your logical formula.
Select Format to open the Format Cells dialog box.
Use the Number, Font, Border, and Fill tabs to specify the formatting you want to apply and then select OK.
Select OK.
For example, suppose you have a range or table of items and you want to highlight those items that have the maximum and minimum values in a particular column. You could set up separate top and bottom rules, but you can make things easier and more flexible by instead using a logical formula.
How you go about this in a conditional formatting rule is a bit tricky, but it can be extremely powerful when you know the trick. First, you can use the MAX() worksheet function to determine the maximum value in a range. For example, if the range is D2:D10, then the following function returns the maximum:
MAX($D$2:$D$10)
However, a conditional formatting formula works only if it returns TRUE or FALSE, so you need to create a comparison formula:
=MAX($D$2:$D$10)=$D2
There are two things to note here: First, you compare the range to the first value in the range; second, the cell address uses the mixed-reference format $D2, which tells Excel to keep the column (D) fixed, while varying the row number.
Next, you can use the MIN() function to determine the minimum, so you create a similar comparison formula:
=MIN($D$2:$D$10)=$D2
Finally, you want to check each cell in the column to see if it’s the maximum or the minimum, so you need to combine these expressions by using the OR() function, like so:
=OR(MAX($D$2:$D$10)=$D2, MIN($D$2:$D$10)=$D2)
Figure 6-5 shows a range of sales results (A2:E10) that are conditionally formatted using the preceding formula. This shows which reps had the maximum and minimum percentage differences between target sales and actual sales (column D).
Combining logical functions with arrays
When you combine the array formulas you learned about in Chapter 2, “Creating advanced formulas,” with IF(), you can perform some remarkably sophisticated operations. Arrays enable you to do things such as apply the IF() logical condition across a range as well as sum only those cells in a range that meet the IF() condition.
Applying a condition across a range
Using AND() as the logical condition in an IF() function is useful for perhaps three or four expressions. After that, it just gets too unwieldy to enter all those logical expressions. If you’re essentially running the same logical test on a number of different cells, a better solution is to apply AND() to a range and enter the formula as an array.
For example, suppose that you want to sum the cells in the range B3:B7 but only if all those cells contain values greater than 0. Here’s an array formula to do this:
=IF(AND(B3:B7 > 0), SUM(B3:B7), "")
Note
Recall from Chapter 2 that if you’re using Excel 2016 or earlier, type the formula and then select Ctrl+Shift+Enter to enter it as an array formula; if you’re using Excel 2019 or later, type the formula and just select Enter.
This is useful in a worksheet in which you might not have all the numbers yet, and you don’t want a total entered until the data is complete. Figure 6-6 shows an example. The array formula in B8 is the same as the previous one. The array formula in B16 returns nothing because cell B14 is blank.
Note
The example files for this chapter use static arrays rather than Excel 2019’s new dynamic arrays (see Chapter 2). This is to ensure that the example workbooks remain compatible with older versions of Excel.
Operating only on cells that meet a condition
In the previous section, you saw how to use an array formula to perform an action only if a certain condition is met across a range of cells. A related scenario arises when you want to perform an action on a range, but only on cells that meet a certain condition. For example, you might want to sum only values that are positive.
To do this, you need to move the operation outside the IF() function. For example, here’s an array formula that sums only values in the range B3:B7 that contain positive values:
=SUM(IF(B3:B7 > 0, B3:B7, 0))
The IF() function returns an array of values based on the condition (the cell value if it’s positive; 0 otherwise), and the SUM() function adds those returned values.
For example, suppose you have a series of investments that mature in various years. It would be nice to set up a table that lists these years and tells you the total value of the investments that mature in each year. Figure 6-7 shows a worksheet set up to do just that.
The investment maturity dates are in column B, the investment values at maturity are shown in column C, and the various maturity years are in column E. To calculate the maturity total for 2019, for example, the following array formula is used:
=SUM(IF(YEAR($B$3:$B$18) = E3, $C$3:$C$18, 0))
The IF() function compares the year value in cell E3 (2019) with the year component of the maturity dates in range B3:B18. For cells in which these are equal, IF() returns the corresponding value in column C; otherwise, it returns 0. The SUM() function then adds these returned values.
Note
In Figure 6-7, notice that, with the exception of the reference to cell E3, I used absolute references so the formula can be filled down to the other years.
Determining whether a value appears in a list
Many spreadsheet applications require you to look up a value in a list. For example, you might have a table of customer discounts in which the percentage discount is based on the number of units ordered. For each customer order, you need to look up the appropriate discount, based on the total units in the order. Similarly, a teacher might convert a raw test score into a letter grade by referring to a table of conversions.
You’ll see some sophisticated tools for looking up values in Chapter 7, “Working with lookup functions.” However, array formulas combined with logical functions also offer some tricks for looking up values.
For example, suppose that you want to know whether a certain value exists in an array. You can use the following general formula, entered into a single cell as an array:
=OR(value = range)
Here, value is the value you want to search for, and range is the range of cells in which to search. For example, Figure 6-8 shows a list of customers with overdue accounts. You enter the account number of the customer in cell B1, and cell B2 tells you whether the number appears in the list.
Here’s the array formula in cell B2:
=OR(B1 = B6:B29)
The array formula checks each value in the range B6:B29 to see whether it equals the value in cell B1. If any one of those comparisons is true, OR() returns TRUE, which means the value is in the list.
Tip
As a similar example, here’s an array formula that returns TRUE if a particular account number is not in the list:
=AND(B1 <> B6:B29)
The formula checks each value in B6:B29 to see whether it does not equal the value in B1. If all those comparisons are true, AND() returns TRUE, which means the value is not in the list.
Counting occurrences in a range
Now you know how to find out whether a value appears in a list, but what if you need to know how many times the value appears? The following formula does the job:
=SUM(IF(value = range, 1, 0))
Again, value is the value you want to look up, and range is the range for searching. In this array formula, the IF() function compares value with every cell in range. The values that match return 1, and those that don’t return 0. The SUM() function adds these returned values, and the final total is the number of occurrences of value. Here’s a formula that does this for our list of overdue invoices:
=SUM(IF(B1 = B6:B29, 1, 0))
Figure 6-9 shows this formula in action (see cell B3).
Note
The generic array formula =SUM(IF(condition, 1, 0)) is useful in any context where you need to count the number of occurrences in which condition returns TRUE. The condition argument is normally a logical formula that compares a single value with each cell in a range of values. However, it’s also possible to compare two ranges, as long as they’re the same shape (that is, they have the same number of rows and columns). For example, suppose that you want to compare the values in two ranges named Range1 and Range2 to see if any of the values are different. Here’s an array formula that does this:
=SUM(IF(Range1 <> Range2, 1, 0))
This formula compares the first cell in Range1 with the first cell in Range2, the second cell in Range1 with the second cell in Range2, and so on. Each time the values don’t match, the comparison returns 1; otherwise, it returns 0. The sum of these comparisons is the number of different values between the two ranges.
Determining where a value appears in a list
What if you want to know not just whether a value appears in a list but where it appears in the list? You can do this by getting the IF() function to return the row number for a positive result:
IF(value = range, ROW(range), "")
Whenever value equals one of the cells in range, the IF() function uses ROW() to return the row number; otherwise, it returns the empty string.
To return that row number, use either the MIN() function or the MAX() function, which returns the minimum or maximum, respectively, in a collection of values. The trick here is that both functions ignore null values, so applying this to the array that results from the previous IF() expression tells where the matching values are:
To get the first instance of the value, use the MIN() function in an array formula, like so:
=MIN(IF(value = range, ROW(range), ""))
To get the last instance of the value, use the MAX() function in an array formula, as shown here:
=MAX(IF(value = range, ROW(range), ""))
Here are the formulas you would use to find the first and last occurrences in the previous list of overdue invoices:
=MIN(IF(B1 = B6:B29, ROW(B6:B29), "")) =MAX(IF(B1 = B6:B29, ROW(B6:B29), ""))
Figure 6-10 shows the results (with the row of the first occurrence in cell D2 and the row of the last occurrence in cell D3).
Tip
It’s also possible to determine the address of the cell that contains the first or last occurrence of a value in a list. To do this, use the ADDRESS() function, which returns an absolute address, given a row and column number:
=ADDRESS(MIN(IF(B1 = B6:B29, ROW(B6:B29), "")), COLUMN(B6:B29)) =ADDRESS(MAX(IF(B1 = B6:B29, ROW(B6:B29), "")), COLUMN(B6:B29))
Case study: Building an accounts receivable aging worksheet
If you use Excel to store accounts receivable data, it’s a good idea to set up an aging worksheet that shows past-due invoices, calculates the number of days past due, and groups the invoices into past-due categories (1—30 days, 31—60 days, and so on).
Figure 6-11 shows a simple implementation of an accounts receivable database. For each invoice, the due date (column D) is calculated by adding 30 to the invoice date (column C). Column E subtracts the due date (column D) from the current date (in cell B1) to calculate the number of days each invoice is past due.
Calculating a smarter due date
You might have noticed a problem with the due dates in Figure 6-11: Several of the dates, including the date in cell D5, fall on weekends. The problem here is that the due date calculation just adds 30 to the invoice date. To avoid weekend due dates, you need to test whether the invoice date plus 30 falls on a Saturday or Sunday. The WEEKDAY() function helps because it returns 7 if the date is a Saturday, and 1 if the date is a Sunday.
So, to check for a Saturday, you could use the following formula:
=IF(WEEKDAY(C4 + 30) = 7, C4 + 32, C4 + 30)
Here, I’m assuming that the invoice date resides in cell C4. If WEEKDAY(C4 + 30) returns 7, the date is a Saturday, so you add 32 to C4 instead (to make the due date the following Monday). Otherwise, you just add 30 days as usual.
Checking for a Sunday is similar:
=IF(WEEKDAY(C4 + 30) = 1, C4 + 31, C4 + 30)
The problem, though, is that you need to combine these two tests into a single formula. To do that, you can nest one IF() function inside another. Here’s how it works:
=IF(WEEKDAY(C4+30) = 7, C4+32, IF(WEEKDAY(C4+30) = 1, C4+31, C4+30))
The main IF() checks whether the date is a Saturday. If it is, you add 32 days to C4; otherwise, the formula runs the second IF(), which checks for Sunday. Figure 6-12 shows the revised aging sheet with the nonweekend due dates in column D.
For calculating due dates based on workdays (that is, excluding weekends and holidays), Excel has a function named WORKDAY() that handles this calculation with ease; see “A workday alternative: The WORKDAY() function,” in Chapter 8, “Working with date and time functions.”
Aging overdue invoices
For cash-flow purposes, you also need to correlate the invoice amounts with the number of days past due. Ideally, you’d like to see a list of invoice amounts that are between 1 and 30 days past due, between 31 and 60 days past due, and so on. Figure 6-13 shows one way to set up accounts receivable aging.
The worksheet in Figures 6.11 through 6.13 uses ledger shading for easier reading. To learn how to apply ledger shading automatically, see “Creating ledger shading,” in Chapter 9, “Working with math functions.”
The aging worksheet calculates the number of days past due by subtracting the due date from the date shown in cell B1. If you calculate days past due using only workdays (weekends and holidays excluded), a better choice is the NETWORKDAYS() function, covered in “NETWORKDAYS(): Calculating the number of workdays between two dates,” in Chapter 8.
For the invoice amounts shown in column G (1–30 days), the sheet uses the following formula (which appears in G4):
=IF(E4 <= 30, F4, "")
If the number of days the invoice is past due (cell E4) is less than or equal to 30, the formula displays the amount (from cell F4); otherwise, it displays a blank.
The amounts in column H (31–60 days) are a little trickier. Here, you need to check whether the number of days past due is greater than or equal to 31 days and less than or equal to 60 days. To accomplish this, you can press the AND() function into service:
=IF(AND(E4 >= 31, E4 <= 60), F4, "")
The AND() function checks two logical expressions: E4> = 31 and E4 <= 60. If both are true, AND() returns TRUE, and the IF() function displays the invoice amount. If one of the logical expressions isn’t true (or if they’re both not true), AND() returns FALSE, and the IF() function displays a blank. Similar formulas appear in column I (61–90 days) and column J (91–120 days). Column K (Over 120) looks for past-due values that are greater than 120.
Getting data with information functions
Excel’s information functions return data concerning cells, worksheets, and formula results. Table 6-2 lists all the information functions.
TABLE 6-2 Excel’s information functions
Function |
Description |
CELL(info_type[,reference]) |
Returns information about various cell attributes, including formatting, contents, and location. |
ERROR.TYPE(error_val) |
Returns a number corresponding to an error type. |
INFO(type_text) |
Returns information about the operating system and environment. |
ISBLANK(value) |
Returns TRUE if value is blank. |
ISERR(value) |
Returns TRUE if value is any error value except #N/A. |
ISERROR(value) |
Returns TRUE if value is any error value. |
ISEVEN(number) |
Returns TRUE if number is even. |
ISFORMULA(reference) |
Returns TRUE if the cell specified by reference contains a formula. |
ISLOGICAL(value) |
Returns TRUE if value is a logical value. |
ISNA(value) |
Returns TRUE if value is the #N/A error value. |
ISNONTEXT(value) |
Returns TRUE if value is not text. |
ISNUMBER(value) |
Returns TRUE if value is a number. |
ISODD(number) |
Returns TRUE if number is odd. |
ISREF(value) |
Returns TRUE if value is a reference. |
ISTEXT(value) |
Returns TRUE if value is text. |
N(value) |
Returns values converted to a number (a serial number if value is a date, 1 if value is TRUE, 0 if value is any other non-numeric). |
NA() |
Returns the error value #N/A. |
SHEET(value) |
Returns the sheet number of the sheet referenced by value. |
SHEETS(reference) |
Returns the number of sheets in reference. |
TYPE(value) |
Returns a number that indicates the data type of value: 1 for a number, 2 for text, 4 for a logical value, 8 for a formula, 16 for an error, or 64 for an array. |
The rest of this chapter takes you through the details of several of these functions.
The CELL() function
CELL() is one of the most useful information functions. Its job is to return information about a particular cell:
CELL(info_type, [reference])
info_type |
A string that specifies the type of information you want. |
reference |
The cell you want to use. (The default is the cell that contains the CELL() function.) If reference is a range, CELL() applies to the cell in the upper-left corner of the range. |
Table 6-3 lists the various possibilities for the info_type argument.
TABLE 6-3 The CELL() function’s info_type argument
Info_Type Value |
What CELL() Returns |
|
address |
The absolute address, as text, of the reference cell. |
|
col |
The column number of reference. |
|
color |
Returns 1 if reference has a custom cell format that displays negative values in a color; returns 0 otherwise. |
|
contents |
The contents of reference. |
|
filename |
The full path and file name of the file that contains reference, as text. Returns the null string ("") if the workbook that contains reference hasn’t been saved for the first time. |
|
format |
A string that corresponds to the built-in Excel numeric format applied to reference. Here are the possible return values: |
|
|
Built-In Format CELL() Returns |
|
|
General |
G |
|
0 |
F0 |
|
#,##0 |
,0 |
|
0.00 |
F2 |
|
#,##0.00 |
,2 |
|
$#,##0_);($#,##0) |
C0 |
|
$#,##0_);[Red]($#,##0) |
C0- |
|
$#,##0.00_);($#,##0.00) |
C2 |
|
$#,##0.00_);[Red]($#,##0.00) |
C2- |
|
0% |
P0 |
|
0.00% |
P2 |
|
0.00E+00 |
S2 |
|
# ?/? or # ??/?? |
G |
|
d-mmm-yy or dd-mmm-yy |
D1 |
|
d-mmm or dd-mmm |
D2 |
|
mmm-yy |
D3 |
|
m/d/yy or m/d/yy h:mm or mm/dd/yy |
D4 |
|
mm/dd |
D5 |
|
h:mm:ss AM/PM |
D6 |
|
h:mm AM/PM |
D7 |
|
h:mm:ss |
D8 |
|
h:mm |
D9 |
parentheses |
Returns 1 if reference has a custom cell format that uses parentheses for positive or all values; returns 0 otherwise. |
|
prefix |
A character that represents the text alignment used by reference. Here are the possible return values: |
|
|
Alignment |
CELL() Returns |
|
Left |
' |
|
Center |
^ |
|
Right |
" |
|
Fill |
|
protect |
Returns 0 if reference isn’t locked; 1 otherwise. |
|
row |
The row number of reference. |
|
type |
A letter that represents the type of data in the reference. Here are the possible return values: |
|
|
Data Type |
CELL() Returns |
|
Text |
L |
|
Blank |
B |
|
All others |
V |
width |
The column width of reference, rounded to the nearest integer, where one unit equals the width of one character in the default font size. |
|
Figure 6-14 shows how the CELL() function works.
The ERROR.TYPE() function
The ERROR.TYPE() function returns a value that corresponds to a specific Excel error value:
ERROR.TYPE(error_val)
error_val |
A reference to a cell containing a formula that you want to check for the error value. Here are the possible return values: |
|
error_val Value |
ERROR.TYPE() Returns |
|
#NULL! |
1 |
|
#DIV/0! |
2 |
|
#VALUE! |
3 |
|
#REF! |
4 |
|
#NAME? |
5 |
|
#NUM! |
6 |
|
#N/A! |
7 |
|
#GETTING_DATA |
8 |
|
#SPILL! |
9 |
|
#UNKNOWN! |
12 |
|
#FIELD! |
13 |
|
#CALC! |
14 |
|
All others |
#N/A |
You most often use the ERROR.TYPE() function to intercept an error and then display a more useful or friendly message. You do this by using the IF() function to see if ERROR.TYPE() returns a value less than or equal to 7; if so, the cell in question contains an error value. Because the ERROR.TYPE() returns value ranges from 1 to 8, you can apply the return value to the CHOOSE() function to display the error message.
For the details of the CHOOSE() function, see “The CHOOSE() function,” in Chapter 7.
Here’s a formula that does all that. (I’ve split the formula so that different parts appear on different lines to make it easier for you to see what’s going on.)
=IF(ERROR.TYPE(D8) <= 8,
***ERROR IN " & CELL("address",D8) & ": " &
CHOOSE(ERROR.TYPE(D8),"The ranges do not intersect",
"The divisor is 0",
"Wrong data type in function argument",
"Invalid cell reference",
"Unrecognized range or function name",
"Number error in formula",
"Inappropriate function argument",
"Waiting for query data",
"Non-empty spill range",
"Unknown data type",
"Referenced field is not found",
"Calculation error"))
Figure 6-15 shows this formula in an example. (Note that the formula displays #N/A when there is no error; this is the return value of ERROR.TYPE() when there is no error.)
The INFO() function
The INFO() function is seldom used, but it’s handy when you need it because it gives you information about the current operating environment:
INFO(type_text)
type_text |
A string that specifies the type of information you want. |
Table 6-4 lists the possible values for the type_text argument.
TABLE 6-4 The INFO() function’s type_text argument
type_text Value |
What INFO() Returns |
directory |
The full pathname of the current folder. (That is, the folder that will appear the next time you display the Open or Save As dialog box.) |
numfile |
The number of worksheets in all the open workbooks, including hidden ones. |
origin |
The address of the upper-left cell that is visible in the current worksheet. In Figure 6-16, for example, cell A2 is the visible cell in the upper-left corner. The absolute address begins with $A: for Lotus 1-2-3 release 3.x compatibility. |
osversion |
A string containing the current operating system version. |
recalc |
A string containing the current recalculation mode: Automatic or Manual. |
release |
A string containing the version of Microsoft Excel. |
system |
A string containing a code representing the current operating environment: pcdos for Windows or mac for Macintosh. |
Figure 6-16 shows the INFO() function at work.
The SHEET() and SHEETS() functions
Excel includes two information functions—SHEET() and SHEETS()—that return information about the worksheets in a workbook. You use the SHEET() function to return a sheet number using the following syntax:
SHEET([value])
value |
An optional value that specifies a sheet. If you omit value, Excel references the current sheet. |
For example, the formula =SHEET() returns the number of the sheet that contains the formula, where 1 is the first sheet in the workbook, 2 is the second sheet, and so on. Note that Excel counts all sheet types, including worksheets and chart sheets.
If a worksheet has the name Budget, then the formula =SHEET("Budget") returns its sheet number. Alternatively, you can use a cell reference within that sheet, such as SHEET(Budget!A1).
You use the SHEETS() function (which takes no arguments) to return the total number of worksheets in the current workbook.
The IS functions
Excel’s so-called IS functions are Boolean functions that return either TRUE or FALSE, depending on the argument they’re evaluating:
ISBLANK(value) ISERR(value) ISERROR(value) ISEVEN(number) ISFORMULA(reference) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISODD(number) ISREF(value) ISTEXT(value)
value |
A cell reference, function return value, or formula result |
reference |
A cell reference |
number |
A numeric value |
The operation of these functions is straightforward, so rather than run through the specifics of all 11 functions, in the next few sections I show you some interesting and useful techniques that make use of these functions.
Counting the number of blanks in a range
When putting together the data for a worksheet model, it’s common to pull the data from various sources. Unfortunately, this often means that the data arrives at different times, and you end up with an incomplete model. If you’re working with a big list, you might want to keep a running total of the number of pieces of data you’re still missing.
This is the perfect opportunity to break out the ISBLANK() function and plug it into the array formula for counting that you learned earlier:
=SUM(IF(ISBLANK(range), 1, 0))
The IF() function runs through the range, looking for blank cells. Each time it comes across a blank cell, it returns 1; otherwise, it returns 0. The SUM() function adds the results to give the total number of blank cells. Figure 6-17 shows an example (see cell G1).
Tip
Using an array formula to count blank cells is fine, but it’s not the easiest way to go about it. In most cases, you’re better off just using the COUNTBLANK(range) function, which counts the number of blank cells that occur in the range specified by the range argument.
Checking a range for non-numeric values
A similar idea is to check a range on which you’ll be performing a mathematical operation to see if it holds any cells that contain non-numeric values. In this case, you plug the ISNUMBER() function into the array counting formula and then return 0 for each TRUE result and 1 for each FALSE result. Here’s the general formula:
=SUM(IF(ISNUMBER(range), 0, 1))
Counting the number of errors in a range
For the final counting example, it’s often nice to know not only whether a range contains an error value but also how many such values it contains. You can do this easily with the ISERROR() function and the array counting formula:
=SUM(IF(ISERROR(range), 1, 0))
Ignoring errors when working with a range
Sometimes you have to work with ranges that contain error values. For example, suppose that you have a column of gross margin results (which require division), but one or more of the cells are showing the #DIV/0! error because you’re missing data. You could wait until the missing data is added to the model, but it’s often necessary to perform preliminary calculations. For example, you might want to take the average of the results that you do have.
To do this efficiently, you need some way of bypassing the error values. Again, this is possible by using the ISERROR() function plugged into an array formula. For example, here’s a general formula for taking an average across a range while ignoring any error values:
=AVERAGE(IF(ISERROR(range), "", range))
Figure 6-18 provides an example.
