Function Basics

Introduction:

By now you should know how to use Excel to enter, edit, and organize data, and perform basic calculations using formulas. The preset formulas in Excel are called functions. Functions can be used for more than just simple calculations.

There are over 450 functions that can be used, which are organized by categories such as Financial, Math & Trig, Statistical, Engineering, Text, Date & time, Lookup and References, and Logical. No one person can know all of the functions available, but once you understand the basic structure of a function, learning and using a new function is relatively easy.

Learning:

By now you should know how to use Excel to enter, edit, and organize data, and perform basic calculations using formulas. The preset formulas in Excel are called functions. Functions can be used for more than just simple calculations.

Every function has a structure called its syntax. Excel includes many common, useful functions that can help you quickly calculate the sum, average, count, maximum value, and minimum value for a range of cells.

A function must have a syntax which indicates how to write a specific function. The basic syntax for a function is an equal sign (=), then the function name (SUM, for example), and one or more arguments within parentheses.

Syntax: = function_name (arguments)

Arguments contain the information Excel needs in order to calculate the function. The arguments appeared within the parentheses of the function. The sequence of the arguments is critical to completing the function calculation correctly.

Arguments:

Arguments may be a single cell or a range of cells and must be enclosed in parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =SUM(B1:B3) would calculate the sum of the values in the cell range B1:B3. This function contains only one argument. Here is the picture below.

You can use multiple arguments in Excel, but they must be separated by a comma. For example, the function =SUM(B1:B3, D1:D2, F1) will add the values of all the cells in the three arguments. Here is the picture of these arguments.

How to Insert a Function

Method 1

Select the cell where the function result will appear. On the formula bar, click the Insert Function button.

The “Insert Function” dialog box appears.

Search for a function or select a function from a category. For example, choose AVERAGE from the Statistical category.

Then click OK.

The “Function Arguments” dialog box appears. The arguments appear in the correct order as required by the Function. You can use values or cell references within the arguments. If the value appears somewhere on the spreadsheet, it is important to use the cell reference instead of typing the actual value in the box. Notice that the right hand side of each argument shows the values. In the middle of the dialog box shows the result of the function calculation that will appear in the cell after selecting OK.

As you navigate to each required argument, in the middle of the Dialog box there are instructions on the meaning of the argument. Additionally, is you select the “Help on this function” option, you’ll be taken to more details of the function, including sample examples.

Clicking OK when completing the steps will result in the calculated value displaying in the cell.

Method 2

The Formulas tab has the different categories for the various functions available in Excel. When selecting from the category, use the dropdown to select the intended function. When you choose the function, the function dialog box will launch.

Method 3

The final method to insert a function is to begin typing directly in a cell. First, you must enter the equal sign ( = ) and then begin typing the function name. Excel will provide all of the functions that match the function name in the tip box that you can select from. Additionally, the user can completely type the entire function including the arguments, but this requires complete knowledge of the function and arguments. You may like this feature as you get more and more comfortable with a given function.

Summary:

Functions are used when a mathematical process is required for a range of cells, such as summing the values in several cell locations. For these computations, functions are preferable to formulas because adding many cell locations one at a time to a formula can be very time-consuming.

The syntax for all Excel functions is =function_name(arguments)

All of the Excel functions can be found on the Formula tab, or by using the Insert Function icon on the formula bar.

Sources:

© Saylor Academy 2010-2020 content authored by Saylor Academy is available under a Creative Commons Attribution 3.0 Unported license. CC BY 3.0