Microsoft Office Tutorials and References

In Depth Information

**Understanding Functions**

Understanding Functions

If you are looking for a speedier way to enter formulas, you can use any one of a wide variety of

functions. Functions are ready-made formulas that perform a series of operations on a specified

range of values. Excel offers more than 300 functions, grouped into 13 categories, that you can use to

perform various types of calculations. Functions use arguments to identify the cells that contain the

data you want to use in your calculations. Functions can refer to individual cells or to ranges of cells.

Function Elements

All functions must start with an equal sign (=).

Functions are distinct in that each one has a name.

For example, the function that sums data is called

SUM, and the function for averaging values is called

AVERAGE. You can create functions by typing them

directly into your worksheet cells or the Formula

bar; alternatively, you can use the Insert Function

dialog box to select and apply functions to your

data.

Construct an Argument

Functions use arguments to indicate which cells

contain the values you want to calculate. Arguments

are enclosed in parentheses. When applying a

function to individual cells in a worksheet, you can

use a comma to separate the cell addresses, as in

=SUM(A5,C5,F5). When applying a function to a

range of cells, you can use a colon to designate the

first and last cells in the range, as in =SUM(B4:G4).

If your range has a name, you can insert the name,

as in =SUM(Sales).

Types of Functions

Excel groups functions into 13 categories, not including functions installed with Excel add-in programs:

Category

Description

Financial

Includes functions for calculating loans, principal, interest, yield, and depreciation.

Date & Time

Includes functions for calculating dates, times, and minutes.

Math & Trig

Includes a wide variety of functions for calculations of all types.

Statistical

Includes functions for calculating averages, probabilities, rankings, trends, and more.

Lookup & Reference

Includes functions that enable you to locate references or specific values in your

worksheets.

Database

Includes functions for counting, adding, and filtering database items.

Text

Includes text-based functions to search and replace data and other text tasks.

Logical

Includes functions for logical conjectures, such as if-then statements.

Information

Includes functions for testing your data.

Engineering

Offers many kinds of functions for engineering calculations.

Cube

Enables Excel to fetch data from SQL Server Analysis Services, such as members, sets,

aggregated values, properties, and KPIs.

Compatibility

Use these functions to keep your workbook compatible with earlier versions of Excel.

Web

Use these functions when you work with web pages, services, or XML content.

200