Basic Functions

This article introduces spreadsheet functions. It also discusses some of the more complicated functions, such as calculating averages of sets of data. Open your spreadsheet program as you read this article so you can follow along and perform your own calculations with functions.

A formula is an expression that calculates the value of one or more cells. Functions are also predefined formulas. You can use a formula independently in Excel. The formula performs calculations using specific values in a particular order. You can create simple formulas to add, subtract, multiply, and divide the values in your worksheet. Always begin your formula with an equal sign (=), followed by constants that are numeric values and calculation operators, such as plus (+) to add, minus (-) to subtract, asterisk (*) to multiply, and forward slash (/) to divide.

Example:

Cell B4 below contains a formula that adds the value of cell B1, B2, and B3.

Image shows a segment of an Excel worksheet with a formula in the formula bar which adds the numbers in cells B1, B2, and B3. The output appears in B4.

The example below shows that the cell B4 contains the SUM function which calculates the sum of the range B1 to B3.

Image shows the formula from above expressed as the SUM function. The expression =SUM(B1:B3) appears in the formula bar.

How to Enter a Formula

To enter a formula, perform the following steps:

Select a cell.

If you want to enter a formula, type an equal sign (=).

For example: Type the formula =B4 B1+B2+B3 into the cell B4.

Image shows a segment of an Excel worksheet with a formula in the formula bar which adds the numbers in cells B1, B2, and B3. The output appears in B4.

Instead of typing B1, B2, and B3, you can type an (=) sign first and simply select click or select the cell you want to add and type (+) operator between two selections.

Change the value of cell B1 to B8 and see how the result changes.

Image shows the formula from above with one of the data points changed, demonstrating the change in the output in cell B4

How to Edit a Formula

When you select a cell that contains a formula, Excel shows the value or formula that is in the cell in the formula bar.

Image shows the formula from above with the formula highlighted with a red box

To edit a formula, click in the formula bar and change the formula in the formula bar.

Image shows the formula from above. In the formula bar, the formula has been changed so that the data in cell B2 is subtracted from the sum, rather than added. Formula bar is now showing the expression =B1-B2+B3

Press Enter, and change the value of the output.

Image shows the formula from above with the output in cell B4. Since the formula was changed, so has the output.

Operator Precedence

Excel uses a default order to perform calculations. Excel calculates the part of the formula that is in parentheses first. Next, it performs multiplication and division calculations. Once this is complete, Excel will add and subtract the remainder of the formula.

In this example of a formula, Excel performs the multiplication first (B1 * B2). Next, Excel adds the value of cell B3 to this result.

Image shows the expression =B1*B2+B3 to give an example of the order of operations in Excel


Image shows the expression =B1*(B2+B3) to give an example of the order of operations in Excel

In the above example, Excel first calculates the part in parentheses (B2+B3). Next, it multiplies this result by the value of cell B1.

How to Copy and Paste a Formula

When you copy a formula, Excel automatically changes the cell references for each new cell the formula is copied to. The picture below shows the steps.

Image shows a segment of an Excel worksheet with data in Column B, a formula in cell B4, and data prepared in Column C.

Select cell B4, right-click, and then click Copy (or press CTRL and C) as shown below.

Image shows the right-click drop down menu with Copy highlighted in a red box

Next, select the cell C4 and right-click, and then click Paste under "Paste Options" (or press CTRL and v).

Image shows Paste options

You can also drag the formula horizontally or vertically according to your data in the sheet. The picture below shows how you can select cell B4, click on the lower right corner of cell B4, and drag it across to cell C4.

Image shows how to paste a formula into an adjacent cell by dragging from the bottom right corner of a cell with a formula to the right

This picture below shows the result: the formula in cell C4 references the values in column C.

Image shows the result of pasting a formula into an adjacent cell. In the formula bar, the cell references have changed and now refer to data in Column C, rather than Column B.

Insert a Function

Every function has a structure. 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.

Arguments contain the information you want to calculate. Suppose, SUM(B1:B4). This function adds the values in cells B1, B2, B3, and B4. The picture below shows the parts of the function.

Image shows the parts of a function, including the equals sign, the function name, and the arguments. Arguments follow the function name and are bookended by parentheses.

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.

Image shows a sum function in the formula with the argument part of the formula highlighted with a red circle

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.

Image shows a function with multiple arguments In the example, the formula bar shows a sum function where multiple columns are being added

The steps below describe how to insert a function.

Select a cell D2, then click the Insert Function button.

Image shows how to insert a preset function by clicking the Insert Function button to the immediate left of the formula bar

The "Insert Function" dialog box appears.

Image shows the Insert Function dialog box. Functions are listed alphabetically and a description of the function is available

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.

Image shows the Function Arguments dialog box

Click OK.

Output : The average of B2 and C2 is 5.

Image shows a function in the formula bar which gives the average of cells B2 and C2. The result is given in cell D2.

Another way to write the function is to click D2 and simply type =AVERAGE(B2:C2) or Click B2 and drag your curser up to C2, then press Enter key.


Source: w3resource, https://www.w3resource.com/excel/excel-functions-basic.php
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License.

Last modified: Tuesday, June 16, 2020, 3:19 PM