Formulas in Spreadsheets
Introduction
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.
Formulas
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 Formulas
Formulas can contain any or all of the following: functions, references, operators, and constants.
- Functions: The PI() function returns the value of pi: 3.142...
- References: A2 returns the value in cell A2.
- Constants: Numbers or text values entered directly into a formula, such as 2.
- Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.
Operators
- Arithmetic operators: (+,-,*,/)
- Comparison operators (+, >, < )
- Text operator (& - joins two text strings)
Full List of Excel Calculation Operators
Most of the same function operators are also available in Google Sheets.
Functions
There 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:
- AVERAGE: calculates the average value of cells
- SUM: adds the values in cells.
- MIN/MAX: Returns the minimum or maximum value of a range of cells
Full List of Google Sheets Functions
Examples of formulas in Excel:
=A1+A2+A3 Adds the values in cells A1, A2, and A3.
=SQRT(A1) Uses the SQRT function to return the square root of the value in A1
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.
Other Excel Tips
Replace a Formula with its Results
Sometimes after using a formula to calculate value you might want to convert the contents of the cell to a value that replaces the formula. One reason to do this is because if you delete the reference cells from your sheet your formula results will no longer be valid. The easiest way to convert cells from a formula to a value is by copying the data and pasting the data, but when you paste the data select the "Values Only" option.
Absolute Reference Cell
| Reference Description |
|---|
| $A$1 (absolute cell reference column and absolute row) |
| A$1 (relative cell reference column and absolute row) |
| $A1 (absolute column and relative row) |
| A1 (relative column and relative row) |
When you create a formula that refers to another cell or range, that cell reference can be relative or absolute. A relative cell reference adjusts to its new location when the formula is copied or moved, this is the default when you create a formula. Sometimes you want the reference cell to stay the same, this is an absolute reference. An absolute cell reference does not change when the formula is moved. The table to the right shows how the specify an absolute reference, this is done using the $ symbol. The examples work in both Excel and Googel Sheets.