Using Other Functions

EXCEL COMES WITH MANY FUNCTIONS, not just SUM. You have already learned about a few of them, such as ROUND and AVERAGE. Because there are so many, Excel groups all of its functions into specific categories, on the theory that this will make it easier for you to find them when needed. For the most part, these categories go a pretty long way in helping you quickly find the function you are looking for. The trick is to get to know what the categories mean, and the best way to do that is to introduce you to the prominent players in each category.

Buttons for each of the function categories are located on the Formulas tab, as shown in Figure 3-13. Click one of these buttons to display a list of functions in that category, and then select the function you want from the list. For example, click the Math & Trig button on the Formulas tab and select the SQRT (square root) function. The function is inserted into the result cell, and the Function wizard appears to guide you through the process of entering the appropriate arguments for that function. After doing that, click OK in the wizard to complete your formula.

Figure 3-13. Use the buttons on the Formulas tab to enter any function you want.

[View full size image]

As I introduce you to the most popular functions in each category, I describe the arguments you need to use. Now, you might have noticed by now that Excel names its arguments—for example, for the SUM function, it uses names like Number1, Number2, and so on. In this section, I might use a different name that I find more descriptive, such as Range1, Range2, so don’t let the names confuse you.

Using the Financial Functions

The Financial functions are used by accountants and the like, to perform common tasks such as calculating the accrued interest on a security, depreciation on an asset, future value of an asset, or the monthly payment on a loan. Because most of the Financial functions are used by financial experts who understand them, I won’t dwell on too many. There are a few Financial functions, however, that just about anyone might use, and in this section, I explain more about them.

PMT

The PMT function can be used in three ways—to calculate the monthly payment on a loan, the interest rate you might earn on an investment, or the monthly deposit needed in order to reach some future total deposit amount. You feed the PMT function various information such as loan amount, interest rate, number of payments, and so on, depending on the result you’re looking for. Let’s take a closer look.

To use the PMT function to calculate a loan, use the following syntax:

=PMT(Rate,NumberofPayments,LoanAmt)

For example, suppose you are looking at buying a used car for somewhere between $10,000 and $12,000. You’ve found several loans and you want to compare them (see Figure 3-14). The first loan amounts have already been calculated, so you’ll enter the formula in cell C8 for the second loan, which is for six years at 7.25%.

Figure 3-14. Use the PMT function to calculate loan payments.

[View full size image]

Type =PMT(.0725/12,6*12,–10000) and press Enter. It’s important that all the arguments are based on the same interval. The first argument is the interest rate, which needs to be converted to its monthly equivalent. So you need to divide the yearly interest rate of 7.25% (which is .0725 in decimal form) by 12 to calculate the interest rate per month. The next argument is the number of monthly payments. Because the loan is for six years, you take 6 times 12 to calculate the number of months. The last argument is the present value of the loan, which for cell C8 is $10,000. Notice that the loan amount is entered as a negative. You don’t have to do that, but if you don’t, the PMT function returns a negative value because of the way the function performs its calculation. Although a negative payment amount might be pleasing to you, it probably won’t be acceptable to your bank. So be sure to enter the loan amount as a negative value.

Another reason you might use the PMT function is to calculate the interest you might earn on an investment, such as a CD (Certificate of Deposit). Use the following syntax:

=PMT(Rate,NumberofPayments,CurrentValue)

For example, to calculate the annual interest you might earn on a CD with an interest of 3.75%, five-year term, and an investment of $5,000, you type =PMT(.0375,5,−5000). Because you are calculating the annual interest, you don’t have to divide the interest rate by 12. If the interest rate is calculated quarterly however, you’ll need to adjust the formula: =PMT(.0375/4,5*4,−5000).

The final reason to use the PMT function is to calculate the amount you need to invest now, if you want to reach some future amount over time. Use the following syntax:

=PMT(Rate,NumberofPayments,,FutureValue)

For example, suppose you want to invest money annually in a CD that pays 6% in order to save $50,000 by the end of its term, which is 15 years. Type =PMT(.06,15,,−50000).

FV and PV

Two other useful Financial functions are FV and PV, which find the future or present value of an investment, given a specific interest rate and an amount to be invested each period. Use the following syntax:

=FV(Rate,NumberofPayments,PmtAmt,PresentValue,
    Type)
=PV(Rate,NumberofPayments,PmtAmt,FutureValue,
    Type)

Like the PMT function, all of these values need to be roughly equivalent, so if you are making quarterly payments, you need to divide the annual interest rate by four. In addition, like the PMT function, you need to enter the future or present value as a negative if you want to get a positive result.

The final argument for both functions, Type, tells Excel when the payment is made: use a 1 if you pay at the beginning of the month, and a 0 if you pay at the end (you can also omit this value and 0 is assumed). So let’s determine how much you’ll have at the end of five years, if you invest $100 per month at 4%. Type this formula: =FV(.04/12,5*12,−100). Hmmmm. It looks like at the end of five years, you’ll have $6,629.90. Not bad!

So what’s it worth to you in spending power, right now, to make those $100 payments each month? Try this formula: =PV(.04/12,5*12,−100). Well, looks like all that money is worth $5,429.91 right now. But keep in mind that you only have to part with $100 of it each month. That thought should keep the pain level down, along with the thought that after five years, you’ll have earned almost $1,200! Now that’s worth a little pain.

Using Logical Functions

Logical functions are used to display text or a value, or to perform some calculation, only if some condition is true. If the condition is not true, some alternate text or value is displayed, or some other calculation is performed. For example, you could tell Excel to compare the value in cell G10 with the value in H10, and if G10 is greater, to perform the calculation G10*.05. If G10 is not greater than H10, you could tell Excel to perform the calculation H10*.05 instead. The most common Logical function is the IF function, which has several variants.

IF

The syntax for the IF function is =IF(Condition,ActionIfTrue,ActionIfFalse). The first argument, Condition, is a logical test, which is essentially a comparison. If the comparison is true, the action listed as the second argument is taken. If the comparison is not true, the action listed as the third argument is taken.

To create a logical test or condition, you can use any of the following operators:

• = Equal

• <> Not equal

• > Greater than

• < Less than

• >= Greater than or equal to

• <= Less than or equal to

For example, to test whether A2 is larger than B2, use the condition A2>B2. Now, suppose a teacher wants to use the IF function to determine whether a student has failed her class. Assuming the first student’s grade is in cell C4, she could type =IF(C4>64,“Pass”,“Fail”) in cell D4. If the first student’s grade is 65 or greater, then the word Pass will appear in cell D4. Otherwise, the word Fail appears. The teacher could then copy this formula down column D to display either Pass or Fail for each student.

SUMIF and SUMIFS

Although technically listed as a Math & Trig function, SUMIF acts like a Logical function so it’s listed here. Use SUMIF to add a group of cells only if some condition is true. The syntax for the SUMIF function is as follows:

=SUMIF(RangeToCheck,”Condition”,RangeToSum)

For example, consider the worksheet shown in Figure 3-15. In order to compute the total of all sales in the Eastern region, this formula is entered in cell I6: =SUMIF(C5:C17,“Eastern”,E5:E17). Basically, this formula looks at each cell in the range C5:C17, and for the ones that contain the word Eastern, it adds the value in the corresponding cell in column E.

Figure 3-15. Use SUMIF to add selected cells.

[View full size image]

The last argument in the SUMIF function is optional, which means that if you want to compare values in a range and add those same values if some condition is true, you can leave this last argument out. For example (using the worksheet shown in Figure 3-15), suppose you want to add all the values in the range E5:E17 that are over $3 million. You could type =SUMIF(E5:E17, “>3000000”).

The SUMIFS function is similar to SUMIF, except that it allows you to enter multiple conditions to identify the cells you want to add. The syntax for the SUMIFS function is as follows:

=SUMIFS(RangeToSum,RangeToCheck,“Condition”,
 RangeToCheck2,“Condition2”,…and so on)

Looking at the worksheet in Figure 3-15, suppose you want to add up all the sales over $1 million for the Eastern region only. In cell I12, you could type =SUMIFS(E5:E17,C5:C17,“Eastern”, E5:E17,“>1000000”).

COUNTIF and COUNTIFS

The COUNTIF and COUNTIFS functions work similarly to SUMIF and SUMIFS, except that they count the cells that meet the given condition, rather than add them. Also, although COUNTIF and COUNTIFS are Statistical functions, they are similar to Logical functions so I describe them here. To insert them into a worksheet, click the More Functions button on the Formulas tab, select Statistical, and then select either COUNTIF or COUNTIFS from the menu that appears.

The syntax for the COUNTIF function is =COUNTIF(RangeToCount,“Condition”).

The syntax for the COUNTIFS function is =COUNTIFS(RangeToCount,“Condition”,RangeTo Count2,“Condition2”,…and so on).

For example, suppose you have a list of potential clients for your new printing business. You want to properly staff your sales force so that they can pay a visit to each these potential clients and close that sale. To do that, you need to count the number of clients in various parts of the city, based on ZIP code. Assuming that the ZIP codes are listed in the range G2:G200, you could type =COUNTIF(G2:F200,“78112”) to count all the potential clients in ZIP code 78112.

If you want to count all the potential clients in the ZIP codes 78112 and 83046, you could use the COUNTIFS function instead: =COUNTIFS (G2:G200,“78112”, G2:G200,“83046”).

Using Text Functions

Not surprisingly, Text functions are used to manipulate text. You use Text functions most often in database worksheets—worksheets that contain a list of names and addresses, inventory items, or statistical data. These functions help you manipulate the text to display what you want. Text functions can also be used on numbers that are entered as text such as a house number, Social Security number, ZIP code, part number, and such.

CONCATENATE

The CONCATENATE function takes the text in several cells and combines it to form a new text string. Take a look at the worksheet shown in Figure 3-16.

Figure 3-16. Use CONCATENATE to add text strings together.

[View full size image]

As you can see in this garden club member database, the month a member was born appears in column H, whereas the birthday appears in column I. Having the birth month and birthday separated into two columns enables you to sort the database as needed. For example, it’s easy to produce a list of everyone with April birthdays, by either sorting or filtering the Birth Month column. Let’s suppose though, that you want to import this list into an e-mail program so you can send out meeting reminders, garden events, and the like. Since you also want to congratulate members on their birthday, you want that information as well. The only problem is, your e-mail program does not have separate fields for Birth Month and Birth Day. Instead, it has one field called Birthday. Using the CONCATENATE function, this problem is easily taken care of.

The syntax is

=CONCATENATE(Text,Text2,Text3,…and so on)

As the argument, you can either type the address of a cell that contains text (text or numbers treated as text), or a text string (enclosed in quotations). To solve the problem here, in cell K2, type =CONCATENATE(I2, “”,J2). The result, shown in cell K2, is a text string that combines the member’s birth month and day, with a space in between: “Sep 20”. Now, since your e-mail program won’t be able to make much sense of the formula, you simply copy the result in column K to column L, using the Paste Value command so that you paste the result and not the formula. At that point, it’s safe to remove columns I–K and keep only column L for importing into your e-mail program.

LEFT and RIGHT

The LEFT function is used to extract a certain number of characters from a text string, starting at the left. The RIGHT function works similarly, except it extracts characters starting from the right. Let’s take a look at one reason why you might need to do this extracting business. Suppose you’re in charge of inventory at a large computer store that caters to corporate customers. You’re moving to a new computerized inventory system, and you need to generate product numbers that take advantage of its capabilities to sort and filter. You’ve decided to change the inventory numbers slightly, by adding a store code (extracted from the current store name) in front of the last portion of the current product number.

The syntax for the LEFT function is

=LEFT(TextCell,NumberOfCharacters)

The syntax for the RIGHT function is

=RIGHT(TextCell,NumberOfCharacters)

Consider the worksheet shown in Figure 3-17. Assume you want to take the first two letters of the store name (such as “Ca” for the Carmel store) and add the last four digits of the current product number. To add these two strings together into a new string, you’ll use our friend, the CONCATENATE function. As you can see, to create the new inventory number in cell D8, you need to use three functions: =CONCATENATE (LEFT(A8,2),RIGHT(B8,4)). The LEFT function extracts the first two characters from the store name (in this case, “Ca”), whereas the RIGHT function extracts the last four digits of the current model number (in this case, “1245”). Finally, the CONCATENATE function puts them together to form a single text string, Ca1245. Copy this formula down column D, and voila! You have your new inventory numbers, easy as pie.

Figure 3-17. Use Excel’s LEFT and RIGHT functions to create a new inventory number.

[View full size image]

TEXT and VALUE

The TEXT function converts a number into equivalent text. The syntax is =TEXT(Number, “Format”). The Number argument is pretty easy to understand—it’s either the address of a cell containing a number, or a formula that results in a number. The Format argument is any valid number or date format. If you open the Format Cells dialog box (by clicking the Dialog Box Launcher in the Number group on the Home tab) and select Custom, you’ll find many examples of valid formats. Here are some samples:

Suppose you run a construction business, and you have a worksheet that everyone in your department is supposed to update as soon as a particular job is completed, listing the total cost of that job, the number of weeks it took to complete, and the approximate level of customer satisfaction. You use this worksheet to create a list of recent referrals for new clients, so it’s pretty important that it’s updated often.

You want to display the date at the top of the worksheet, but you also want to make it clear what that date means by preceding it with the text, “Updated on”. As you can see in the worksheet shown in Figure 3-18, the formula you need is =“Updated on” &TEXT(TODAY(),“m/d/yy”). The TODAY function, which is described in the section “Using Date and Time Functions,” is a simple function that displays today’s date. The date is updated every time the worksheet is recalculated. So if you open the workbook at some later date, this date is automatically changed to reflect the current date. If today’s date was already displayed elsewhere in the worksheet, you could simply reference that cell in the formula like this: =“Updated on” &TEXT(B3,“m/d/yy”).

Figure 3-18. Use TEXT to change a number into text.

[View full size image]

The VALUE function is the opposite of TEXT, converting a text string that looks like a number into an actual value that can be calculated just like any other number. The syntax is =VALUE(Text).

Using Date and Time Functions

The Date and Time functions help you display dates or times in the worksheet without actually entering them. For example, there are functions for entering the current date or the current date and time. There are other functions you can use to add or subtract dates—for example, you might want to subtract the start date of a job from its end date in order to calculate how long the job took. Only work on weekdays? No problem; there’s a date function that counts the number of weekdays only between two dates.

TODAY

The TODAY function does not have any arguments. You simply type =TODAY() into a cell, press Enter, and the current date appears. You can then apply any date format you want to display the date the way you like. Every time the worksheet is recalculated, TODAY updates the date in the cell automatically.

NOW

The NOW function is similar to TODAY except that it displays the current date and time. Like the TODAY function, the NOW function does not have any arguments. You type =NOW() into a cell, and the current date and time are displayed. When the worksheet is recalculated, the date and time are updated.

DAY, MONTH, and YEAR

The DAY, MONTH, and YEAR functions work similarly. Each has only one argument, Date, and each extracts something from that date—the day, the month, or the year. The syntax is

=DAY(Date)

=MONTH(Date)

=YEAR(Date)

For example, suppose you have a worksheet that lists your employees, and you want to determine how many years each employee will be with you come the end of the year. You’ve decided to take the year each person was hired, and subtract it from 2011 to calculate the number of years each employee has been working for you, come 12/31/2011. Assuming the first employee’s hire date is in cell I10, you use this formula: 2011–YEAR(I10). The result is a date, which appears rather confusing. Simply format the cell using General format, and a value such as 12 appears.

NETWORKDAYS

The NETWORKDAYS function calculates the number of workdays between two dates. By workdays, I mean Monday–Friday, minus any obvious holidays such as Christmas and New Year’s. The syntax is:

=NETWORKDAYS(StartDate,EndDate,Holidays)

The last argument, Holidays, is optional, but it allows you a way to specify the holidays your office uses, such as Martin Luther King Day, Veterans Day, and so on. The argument is a range, such as M2:M12, that contains the dates you want treated as holidays. So for example, if you had a worksheet that listed the starting and ending date of a job, you could calculate the number of workdays it took to complete that job with a formula like this: =NETWORKDAYS(C2,D2).

DATE

The DATE function allows you to compose a date, using each of its three arguments: Year, Month, and Day. The syntax is:

=DATE(Year,Month,Day)

The DATE function is useful in situations where the month, day, and year of a date are stored in separate cells, as they might be in a large database. For example, if you had a membership database for your parents’ association that listed everyone’s birth month in one column and birth day in another, you could use the DATE function to compose their actual birthday this year, assuming the birth months are in column G and the birth days are in column I: =DATE(YEAR(TODAY(),G2,I2).

Another example is to compute a date so many years, months, or days from some other date. For example, suppose an employee review is due three months from an employee’s hire date. You could add 180 (3*30) to the hire date, but that’s not exactly accurate, as some months have 31 days and one of them has 28 or 29 depending on the year. No, all you want to do is to take a date like 2/15/10 and add three months to get 5/15/10. So you use the DATE function and a few old friends to add three months to the hire date, which let’s say is stored in cell E4: =DATE(YEAR(E4),MONTH(E4)+3,DAY(E4)). Obviously, this formula, with a small tweak, would work easily in situations where you need to add or subtract so many years from a date, like this example, which adds 25 years to a date: =DATE(YEAR(E4)+25,MONTH(E4),DAY(E4)). To add or subtract days, you can be more straightforward: =E4+15.

Using Lookup and Reference Functions

The Lookup and Reference functions are used to locate data in a large database of information, such as an employee or inventory database. The two most common of these functions include VLOOKUP and HLOOKUP.

VLOOKUP and HLOOKUP

Both VLOOKUP and HLOOKUP are used to look up information in a database. The VLOOKUP function looks up the data vertically down a particular column, whereas HLOOKUP looks up data horizontally across a particular row. Here is the syntax:

=VLOOKUP(ItemToFind,Range,ColumnNumber,Type)

=HLOOKUP(ItemToFind,Range,RowNumber,Type)

The ItemToFind is a cell address, formula, or the actual value you are trying to find in the database, such as a particular employee. This item must be contained in the first column/row of the Range you specify. So, if you’re looking up an employee using his ID, the ID needs to be the first column or row of your database Range.

The Range, by the way, needs to include the column/row that contains the labels or everything will be thrown off. The ColumnNumber/ RowNumber is the column/row where the data you’re trying to look up is stored. For example, if you’re trying to look up an employee’s phone number, and that phone number is in column 5 of the Range, you would use 5 as your ColumnNumber argument.

Finally, the Type is optional, but if you omit it, Excel assumes “True,” which means that your database is sorted by the ItemToFind column/row, and that you will accept an answer that’s close but not over if the actual item can’t be found. For example, suppose you are looking up an employee by his ID, and that the Range is sorted by ID, from lowest to highest. If you tell Excel to look up ID number 417 and there isn’t one, Excel will look up the closest ID that isn’t over 417 (such as say, 415) and give you the results for that person. If you set Type to False, Excel will only provide an answer if an exact match is found. Also, if you set Type to False, Excel does not assume that the Range is sorted by ID (to continue this example).

Consider the worksheet shown in Figure 3-19, which shows an employee database for PhotoTown. Because the database is set up vertically, with the labels across the top of each column, you need to use VLOOKUP to look up an employee’s emergency phone number when needed. You’ve planned the worksheet so that you can enter the employee’s name in cell I4, and his/her phone number appears in cell I5. Type =VLOOKUP(I4,C7:H37,6,FALSE). Cell I4 has the value to look up, which in this case is the employee’s name. The range C7:H37 has the data—note that column C contains the employee names.

Figure 3-19. Look up data in a large database of information using VLOOKUP or HLOOKUP.

[View full size image]

Remember that the thing you are looking up (the employee’s name in this example) must be contained in the first column/row. The number 6 tells Excel to look up data in the sixth column of the Range for the matching employee. The sixth column in this case is the Emergency Phone Number column, which is exactly what we want.

Using Mathematical Functions

You already know some mathematical operations for use in formulas (+, −, *, /, and ^), but what about the Mathematical functions? Which ones might you find the most useful for analysis of data? Well, you are already pretty familiar with one Mathematical function, SUM, which is used to add a group of cells. Let’s take a look at some others.

INT

Need to round a result down to the nearest integer? Then the INT function is for you. The syntax is =INT(Number). Now typically, you won’t enter an actual number as the argument for the INT function, because I’m guessing you could probably round it down to the nearest integer in your head. Instead, you will probably enter a cell address, like this: =INT(G4). Assuming G4 contains the value 102.31, the result is 102. If G4 contains the value, 102.72, the result is still 102 since INT always rounds down.

You can also nest a formula or a function with INT if you like. For example, consider the formula =INT(SUM(G10:J32)). It tells Excel to total the values in the range G10:J32, and then round them down to the nearest integer. Consider this formula: =INT(A2/40). It tells Excel to take the value in cell A2, divide it by 40, and then round the result down to the nearest integer.

ROUND

The ROUND function adjusts a value to a specific number of digits. Now, there are several reasons why you might want to round the results of your formulas, such as to stop your worksheets from driving you crazy. Let me explain.

When a cell is formatted to a specified number of decimal places, the display of that value is the only thing affected. The actual value in the cell is still used in all calculations. For example, if a cell contains the value 13.45687, and you decide to display only the first two decimal places, the value 13.46 will display in the cell, but the value 13.45687 will be used in all calculations, including totals, which is where the “driving you crazy” part comes in. Take a good look at the worksheet shown in Figure 3-20.

Figure 3-20. Your totals can drive you crazy if you forget to round.

[View full size image]

In this worksheet, you can see the actual values (column D), and the values that appear in the worksheet if you apply a two-decimal format to those numbers (column C). Let’s pretend for a second that your worksheet only shows Column C—Column D is there only so you can see what’s going on. If you do some quick math, you will see that the September Sales Projections in Column C look as if they have been added incorrectly, since 2147.83+502.18+1865.33+ 2044.00+3122.16+1755.86 equals 11,437.36 and not 11,437.37.

So, here’s the lesson you need to learn: if you plan on displaying a limited number of decimal places in your worksheet and not whole numbers, you might want to use the ROUND function to adjust each value so that the displayed value is equal to the actual value used in calculations.

Let’s take a look at the worksheet if you round the values, and then add them. As you can see in Column E, the actual values, which are displayed with four decimal places, have been rounded to the nearest penny. So even when you display the values using only two decimal places (as shown in Column F), the total is correct. Of course, if you are interested in every digit of the calculated sales projections, you should display them fully so that your totals will look correct.

To round a value to the nearest specified decimal place, use this syntax: =ROUND(Number,Digits). For example, to round the value in cell G21 to the nearest penny, type =ROUND(G21,2). To round to the nearest whole number, type =ROUND(G21,0). To round to the left of the decimal point, use a negative number. For example, to round to the nearest hundred, type =ROUND(G21,–2).

Using Other Functions

On the Formulas tab, Excel groups other functions that while perhaps less popular, are still very useful. In fact, you may find yourself using the Statistical functions quite often, although perhaps you will not often insert them using the Formulas tab. Most likely, you will insert the following Statistical functions using the AutoSum button, explained earlier in this chapter:

• =AVERAGE(Range1,Range2,…and so on): The AVERAGE function calculates the mean (average) of the values in the given Range(s).

• =COUNT(Range1,Range2,…and so on): The COUNT function counts the cells in the given Range(s) containing numbers (cells that are blank cells or contain text are ignored).

• =MIN(Range1,Range2,…and so on): The MIN function finds the lowest value in the given Range(s).

• =MAX(Range1,Range2,…and so on): The MAX function finds the highest value in the given Range(s).

There are a few related functions that you might be interested in, such as the COUNTA function, which counts the number of non-blank cells (numbers and text cells are counted) in the given ranges. The syntax is:

=COUNTA(Range1,Range2,…and so on)

The COUNTBLANK function is similar, except that it counts the number of blank (empty) cells in the given ranges. The syntax is:

=COUNTBLANK(Range1,Range2,…and so on)