Functions are complex ready-madeformulas that perform a series of operations on a specified range of values. For example, to determine the sum of a series of numbers in cells A1 through H1, you can enter the function =SUM(A1:H1) instead of entering =A1+B1+C1 and so on. Functions can use range references (such as B1:B3), range names (such as SALES), or numerical values (such as 585.86).
Every function consists of the following three elements:
The = sign indicates that what follows is a function (formula).
The function name, such as SUM, indicates which operation will be performed.
The argument, such as (A1:H1), indicates the cell addresses of the values that the function will act on. The argument is often a range of cells, but it can be much more complex.
You can enter functions either by typing them in cells or by using the Function Wizard, as you'll see later in this lesson. Table 16.1 shows Excel's most common functions that you'll use most in your worksheets.
Table 16.1. Excel's Most Common Functions
Function
Example
Description
AVERAGE
=AVERAGE(B4:B9)
Calculates the mean or average of a group of numbers.
COUNT
=COUNT(A3:A7)
Counts the numeric values in a range. For example, if a range contains some cells with text and other cells with numbers, you can count how many numbers are in that range.
COUNTA
=COUNTA(B4:B10)
Counts all cells in a range that are not blank. For example, if a range contains some cells with text and other cells with numbers, you can count how many cells in that range contain text.
IF
=IF(A3>=100, A3*2, A2*2)
Allows you to place a condition on a formula. In this example, if A3 is greater than or equal to 100, the formula A3*2 is used. If A3 is less than 100, the formula A2*2 is used instead.
MAX
=MAX(B4:B10)
Returns the maximum value in a range of cells.
MIN
=MIN(B4:B10)
Returns the minimum value in a range of cells.
PMT
=PMT(rate,nper,pv)
Calculates the periodic payment on a loan when you enter the interest rate, number of periods, and principal as arguments. Example: =PMT(.0825/12,360,180000) for 30 year loan at 8.25% for $180,000.
PMT
=PMT(rate,nper,, fv)
Calculates the deposit needed each period to reach some future value. Example: =PMT(.07/12,60,,10000) calculates the deposit needed to accumulate $10,000 at an annual rate of 7 percent, making monthly payments for five years (60 months).
SUM
=SUM(A1:A10)
Calculates the total in a range of cells.
SUMIF
=SUMIF(rg,criteria,sumrg)
Calculates the total of the range rg for each corresponding cell in sumrg that matches specified criteria. For example, =SUMIF (A2:A4,>100,B2:B4) adds the cells in the range A2:A4 whose corresponding cell in column B is greater than 100.
Excel on the Web
A new function,=HYPERLINK(), is used to create links to Web sites right in your worksheet. For example, =HYPERLINK(http://www.microsoft.com,"Visit Microsoft") will display the words "Visit Microsoft" in a cell. When the user clicks the cell, he or she is connected to the Microsoft home page. You can also use this feature to link to worksheets on your company's intranet.
Enter Text Right
When entering text into a formula, be sure to surround it with quotation marks, as in "Seattle."
