A function can be thought of as a service provided by Excel to do a specific task. That task might be to perform some math operation, it could be to make a decision based on information you give it, or it could be to perform an action on some text. A function is always indicated by the function name followed by a set of parentheses. For most functions, arguments inside the parentheses either tell the function what to do or indicate the values that the function is to work with. An argument can be a value that you type, a cell reference, a range reference, a name, or even another function. The number and type of arguments vary depending on which function you’re using. It is important to understand the syntax of common functions and be able to correctly enter the function arguments. Fortunately, you don’t have to memorize anything—Excel 2008 does an excellent job of walking you through the process of using a function within your formulas. You can type a function’s syntax yourself if you want, but it’s almost always easier to let Excel guide you through the process.
Excel 2008 includes over 200 functions that you can use to calculate, transform, retrieve, or otherwise manipulate alphanumeric data. Functions are available in the following categories:
Database
Date and time
Engineering
Financial
Information
Logical
Lookup and reference
Math and trigonometry
Statistical
Text
It looks as though Excel pretty much wipes out the need for high school math classes!
You can use formulas and functions to work with text, dates, and numbers. For example, at my company, we create project schedules in Excel that use formulas to help us determine when we need to do things, and calculate the date that a project will be completed. Within the same schedule, we use formulas to estimate the number of pages that each chapter of a book will be when it’s in its final format, how many pages the entire book will be, how many pages the index should be to provide sufficient information to the reader, and whether the projected page count fits the page limitations required by the publisher.
In a typical book project schedule, we use these functions:
DAY() to calculate the date that is a specific number of days beyond the date in the reference cell
WORKDAY() to calculate the date that is a specific number of days, excluding weekends and holidays, beyond the date in the reference cell
SUM() to add columns of numbers
ODD(SUM()) to return an odd number closest to the result of the SUM calculation
EVEN(SUM()) to return an even number closest to the result of the SUM calculation
MAX() to return the largest number (or in this case, the latest date) in a referenced cell range
The types of functions and formulas you’ll create depend largely on what you need to do.
We won’t go into great detail about all the available functions here—you can find information about them in the Excel Help file. However, the following sections provide an overview of the available functions.
The functions in the following table can be used with any alphanumeric data. In the table:
Any argument specified as a number can be a number that is entered directly, a text representation of a number (a number inside quotation marks), a cell reference, a cell range reference, or a named reference. Any cells that contain text that can’t be translated to a number, that are empty, or that have an error are simply ignored by the function.
See Also
For information about named references, see "Reference Named Cells and Ranges" later in this chapter.
Any argument specified as a value can be any type of value. In the case of COUNT, the function will simply ignore anything that it can’t interpret as a number. In the case of COUNTA, the function will count everything that isn’t empty.
The range arguments are references to the set of cells that will be evaluated against the criteria. A criteria argument can be a number, an expression enclosed in quotation marks, or text enclosed in quotation marks. The average_range and sum_range arguments are references to the set of cells whose values will be averaged or summed. In the single criteria versions, if the average_range or sum_range argument is omitted, the function uses the values from the range. The average_range and sum_range arguments are not optional for the multi-criteria functions.
Function | Purpose | Arguments |
|---|---|---|
AVERAGE() | Average a set of numbers | number1,number2,...number255 |
AVERAGEIF() | Average values that meet one condition | range,criteria,average_range |
AVERAGEIFS() | Average values that meet multiple criteria | average_range,criteria_range1,criteria1, criteria_range2,criteria2,... |
COUNT() | Count the number of cells that have numbers | value1,value2,...value255 |
COUNTA() | Count the number of cells that are not empty | value1,value2,...value255 |
COUNTIF() | Count cells that meet one condition | range,criteria |
COUNTIFS() | Count cells that meet multiple criteria | criteria_range1,criteria1,criteria_range2,criteria2,... |
MAX() | Find the maximum value in a set of numbers | number1,number2,...number255 |
MIN() | Find the minimum value in a set of numbers | number1,number2,...number255 |
SUM() | Total a set of numbers | number1,number2,...number255 |
SUMIF() | Sum values that meet one condition | range,criteria,sum_range |
SUMIFS() | Sum values that meet multiple criteria | sum_range,criteria_range1,criteria1, criteria_range2,criteria2,... |
Excel provides several ways to work with text. The simplest text operation is concatenation—adding one text value to the end of another. This is useful when one part of the text value is the result of a calculation and another part is fixed or is the result of a different calculation. Some text functions help you to manage the capitalization of text. You can use the following functions on one cell at a time:
LOWER() returns a text value in all lowercase letters.
PROPER() returns a text value with an uppercase letter followed by lowercase letters.
UPPER() returns a text value in all uppercase letters.
Resources
Changing the case of text for more than one cell requires an array formula. These multi-dataset formulas are beyond the scope of this book, but you can find information about them in the Excel Help file.
Two functions are available to replace parts of a text value:
REPLACE() replaces a specific number of characters in a text value.
SUBSTITUTE() replaces one part of a text value with another (which could include an empty string, in order to delete part of a text value).
The functions in the following table can be used with any alphanumeric data. In the table:
The lookup_value argument is the value to be looked up. This argument can be a number, text, or a cell reference.
The table_array argument is a reference to a range that has one or more rows, in the case of HLOOKUP, or one or more columns, in the case of VLOOKUP. Excel evaluates the first row or column of this range for the lookup value.
The row_index_num and column_index_num arguments specify which row or column to return the value from. For example, in an HLOOKUP function, a row_index_num value of 1 would return the value from the first row, the same one being used for the lookup. A row_index_num value of 2 would return the value from the second row, and so on.
The range_lookup argument is either TRUE or FALSE. If it is TRUE or omitted and an exact match is not found, the largest value that is less than the lookup_value argument is used as a match. If the lookup_value argument is smaller than any number in the first row (HLOOKUP) or column (VLOOKUP), a #N/A error occurs. If the range_lookup argument is FALSE, only an exact match is allowed. If an exact match isn’t found, a #N/A error occurs.
Function | Purpose | Arguments |
|---|---|---|
HLOOKUP() | Return a value from the column in which a value in the first row matches the criteria | lookup_value,table_array,row_index_ num,range_lookup |
VLOOKUP() | Return a value from the row in which a value in the first column matches the criteria | lookup_value,table_array,column_index_ num,range_lookup |
IF() | Return one value if a test is TRUE, and another if the test is FALSE | logical_test,value_if_true,value_if_false |
AND() | Return TRUE if all arguments are true | logical1,logical2,... |
OR() | Return TRUE if any arguments are true | logical1,logical2,... |
NOT() | Reverse the logical value of the argument | logical |
IFERROR() | Return a user-friendly message if a formula has an error due to user input | value,value_if_error |