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.

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

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

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.

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.

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

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

Press Enter, and change the value of the output.

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.

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

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.

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

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

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.

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

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.

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.

The steps below describe how to insert a function.

Select a cell D2, then 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.

Click OK.

Output : The average of B2 and C2 is 5.

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

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License.

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