Function Categories

I list and briefly describe Excel's function categories in the following sections.

Cross Ref 

See subsequent chapters for specific examples of using the functions.

New 

Excel 2007 incorporates all the functions that were formerly available in the Analysis Toolpak add-in.

Financial Functions

The financial functions enable you to perform common business calculations that deal with money. For example, you can use the PMT function to calculate the monthly payment for a car loan. (You need to provide the loan amount, interest rate, and loan term as arguments.)

Date & Time Functions

The functions in this category enable you to analyze and work with date and time values in formulas. For example, the TODAY function returns the current date (as stored in the system clock).

Math & Trig Functions

This category contains a wide variety of functions that perform mathematical and trigonometric calculations.

Note 

The trigonometric functions all assume radians for angles (not degrees). Use the RADIANS function to convert degrees to radians.

Statistical Functions

The functions in this category perform statistical analysis on ranges of data. For example, you can calculate statistics such as mean, mode, standard deviation, and variance.

Lookup and Reference Functions

Functions in this category are used to find (look up) values in lists or tables. A common example is a tax table. You can use the VLOOKUP function to determine a tax rate for a particular income level.

Database Functions

Functions in this category are useful when you need to summarize data in a list (also known as a worksheet database) that meets specific criteria. For example, assume you have a list that contains monthly sales information. You can use the DCOUNT function to count the number of records that describe sales in the Northern region with a value greater than 10,000.

Text Functions

The text functions enable you to manipulate text strings in formulas. For example, you can use the MID function to extract any number of characters beginning at any character position. Other functions enable you to change the case of text (convert to uppercase, for example).

Logical Functions

This category consists of only seven functions that enable you to test a condition (for logi- cal TRUE or FALSE). You will find the IF function very useful because it gives your formulas simple decision-making capability.

Volatile Functions

Some Excel functions belong to a special class of functions called volatile. Excel recalculates a volatile function whenever it recalculates the workbook-even if the formula that contains the function is not involved in the recalculation.

The RAND function represents an example of a volatile function because it generates a new random number every time Excel calculates the worksheet. Other volatile functions include

AREAS	INDEX	OFFSET
CELL	INDIRECT	ROWS
COLUMNS	NOW	TODAY

As a side effect of using these volatile functions, Excel will always prompt you to save the workbook when you close it-even if you made no changes to it. For example, if you open a workbook that contains any of these volatile functions, scroll around a bit (but don't change anything), and then close the file, Excel will ask whether you want to save the workbook.

You can circumvent this behavior by using the Manual Recalculation mode, with the Recalculate Before Save option turned off. Change the recalculation mode in the Calculate section of the Formulas tab of the Excel Options dialog box (choose Office Excel Options).

Information Functions

The functions in this category help you determine the type of data stored within a cell. For example, the ISTEXT function returns TRUE if a cell reference contains text. Or you can use the ISBLANK function to determine whether a cell is empty. The CELL function returns lots of potentially useful information about a particular cell.

User-Defined Functions

Functions that appear in this category are custom worksheet functions created using VBA. These functions can operate just like Excel's built-in functions. One difference, however, is that custom functions do not display a description of each argument in the Paste Function dialog box.

Engineering Functions

The functions in this category can prove useful for engineering applications. They enable you to work with complex numbers and to perform conversions between various numbering and measurement systems.

Cube Functions

The functions in this category allow you to manipulate data that is part of an OLAP data cube.

Other Function Categories

In addition to the function categories described previously, Excel includes four other categories that may not appear in the Paste Function dialog box: Commands, Customizing, Macro Control, and DDE/External. These categories appear to be holdovers from older versions of Excel. If you create a custom function, you can assign it to one of these categories. In addition, you may see other function categories created by macros.

Cross Ref 

See Chapter 23 for information about assigning your custom functions to a function category.