Is a formula that performs a calculation?
One of the most powerful tools available in most spreadsheet software packages is the ability to create formulas. A formula performs calculations or other actions on the data in your worksheet. They can be simple (adding two cells together) or more complex with multiple functions and variables. Microsoft Excel has an endless possibility of formulas. Google Sheets also has the ability to create formulas and perform functions. Show A formula always starts with an equal sign (=), which can be followed by numbers, math operators (such as a plus or minus sign), and functions, which can really expand the power of a formula. Similar to the Raster Calculator in ArcGIS there are many different operators and functions available when using formulas. Parts of a FormulasFormulas can contain any or all of the following: functions, references, operators, and constants.
Operators
Full List of Excel Calculation Operators Most of the same function operators are also available in Google Sheets. FunctionsThere are hundreds of built-in functions in both Excel and Google Sheets. The functions are pre defined formulas or mathematical operations. Some of the most frequently used functions include average, sum and count. There are also many statistical functions available, logical functions (IF functions), financial (interest), and trigonometric (sin, cos). Here are a couple of examples:
Full List of Excel Functions Full List of Google Sheets Functions Examples of formulas in Excel: The below example shows how you can use Excel to convert area in square meters to Hectares and sum the area to calculate the total area. One of the other powerful tools in Excel is the ability to copy formulas to adjacent cells by using the fill option. This is done by selecting the cell with the formula you want to copy then dragging the fill box over the range of cells that you want to copy the formula to. Excel tables track data in a structured format. To create a formula that finds the total number of exceptions by using the SUM function, you begin by typing =SU. When you enter the letter S, Formula AutoComplete lists functions that begin with the letter S; when you enter the letter U, it narrows the list down to the functions that start with the letters SU. To add the SUM function to the formula, click SUM and then press Tab. The function appears in the formula bar followed by an open parenthesis. To begin adding the table reference, enter the letter E. Excel displays a list of available functions, tables, and named ranges that start with the letter E. Click Exceptions, and press Tab to add the table reference to the formula. Then, because you want to summarize the values in the table’s Count column, enter an opening bracket, and, in the list of available table items, click Count. Finally, enter a closing bracket followed by a closing parenthesis to finish creating the formula =SUM(Exceptions[Count]). If you want to include a series of contiguous cells in a formula, but you haven’t defined the cells as a named range, you can click the first cell in the range and drag to the last cell. If the cells aren’t contiguous, hold down the Ctrl key and select all the cells to be included. In both cases, when you release the mouse button, references to the cells you selected appear in the formula. In addition to using the Ctrl key to add cells to a selection, you can expand a selection by using a wide range of keyboard shortcuts. The following table summarizes many of these shortcuts. Key sequence Description Shift+Right Arrow Extend the selection one cell to the right. Shift+Left Arrow Extend the selection one cell to the left. Shift+Up Arrow Extend the selection up one cell. Shift+Down Arrow Extend the selection down one cell. Ctrl+Shift+Right Arrow Extend the selection to the last non-blank cell in the row. Ctrl+Shift+Left Arrow Extend the selection to the first non-blank cell in the row. Ctrl+Shift+Up Arrow Extend the selection to the first non-blank cell in the column. Ctrl+Shift+Down Arrow Extend the selection to the last non-blank cell in the column. Ctrl+Shift+8 (Ctrl+*) Select the entire active region. Shift+Home Extend the selection to the beginning of the row. Ctrl+Shift+Home Extend the selection to the beginning of the worksheet. Ctrl+Shift+End Extend the selection to the end of the worksheet. Shift+PageDown Extend the selection down one screen. Shift+PageUp Extend the selection up one screen. Alt+; Select the visible cells in the current selection. After you create a formula, you can copy it and paste it into another cell. When you do, Excel changes the formula to work in the new cells. For instance, suppose you have a worksheet in which cell D8 contains the formula =SUM(C2:C6). If you click cell D8, copy the cell’s contents, and then paste the result into cell D16, Excel writes =SUM(C10:C14) into cell D16. In other words, it reinterprets the formula so it fits the surrounding cells! Excel knows to reinterpret the cells used in the formula because the formula uses a relative reference, or a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column—for example, C14. Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled Sale Price and Rate, and you want to calculate a sales representative’s commission by multiplying the two values in a row. To calculate the commission for the first sale, you would enter the formula =A2*B2 in cell C2. Use formulas to calculate values such as commissions. Selecting cell C2 and dragging the fill handle until it covers cells C2:C7 copies the formula from cell C2 into each of the other cells. Because you created the formula by using relative references, Excel updates each cell’s formula to reflect its position relative to the starting cell (in this case, cell C2.) The formula in cell C7, for example, is =A7*B7. Copying formulas to other cells to summarize additional data. You can use a similar technique when you add a formula to an Excel table column. For example, suppose the sale price and rate data were in an Excel table, and you created the formula =A2*B2 in cell C2. Excel would apply the formula to every other cell in the column. Because you used relative references in the formula, the formulas would change to reflect each cell’s distance from the original cell. Adding a formula to an Excel table cell to calculate values in a column. If you want a cell reference to remain constant when you copy the formula that is using it to another cell, you can use an absolute reference. To write a cell reference as an absolute reference, you enter $ before the row letter and the column number. For example, if you want the formula in cell D16 to show the sum of values in cells C10 through C14 regardless of the cell into which it is pasted, you can write the formula as =SUM($C$10:$C$14). One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference through the four possible types of references:
To create a formula by entering it in a cell
To create a formula by using the Insert Function dialog box
To display the current date and time by using a formula
To update a NOW() formula To calculate a payment by using a formula
To refer to a named range in a formula
To refer to an Excel table column in a formula
To copy a formula without changing its cell references
To move a formula without changing its cell references
To copy a formula while changing its cell references
To create relative and absolute cell references
|