Creating formulas in Excel
In Excel, you can use formulas to populate the value of a cell. Formulas can be used to perform numeric calculations, such as:
- adding or multiplying
- altering text (e.g. removing white space and splitting values)
- testing logical expressions.
For now, let’s look at how formulas are constructed and how to use Excel to calculate the measures of central tendency and measures of spread to describe a data set. Formulas always start with an equal sign (=), and are constructed with a combination of cell references, constants, operators, and functions.
Operators
Excel has four types of calculation operators: arithmetic, comparison, text concatenation, and reference. The arithmetic operators available are shown in the table below.
| Arithmetic operator and meaning | Example | Result |
|---|---|---|
| + Addition | =4+4 | 8 |
| – Subtraction or negation | =3-1 or =-1 | 2 or -1 |
| * Multiplication | =4 * 4 | 16 |
| / Division | =4/2 | 2 |
| % Percent | =40% | 0.4 |
| ^ Exponentiation | =4^4 | 256 |
Constants
Constants are values entered directly into the formula (e.g. 2, 200, 2.2, or “2”.) Excel expects a specific type of value for each operator. If you enter one that is not expected, Excel might try to convert the value for you.
The table below illustrates some common examples:
| Formula | Output | Explanation |
|---|---|---|
| =1+2 | 3 | Excel expects numbers for arithmetic operators and automatically converts text values to numbers. |
| =1+$2.00 | 3 | Excel converts text to numbers if the text is in a format that would usually be accepted as a number. |
| =6/1/2001-5/1/2001 | 0.0005 | Excel interprets the text as a date (mm/dd/yyyy), converts the dates to serial numbers, and then calculates the difference between them. |
| 1+2 | 1+2 | Excel treats this as text rather than converting it to a number as there is no equal sign (=) preceding it. |
Cell references
Earlier, you learned how to create references. Cell references can also be used in formulas (e.g. ‘=A2*B1/2’). This will multiply the value in cell A2 by the value in B1, and divide the result by two. When you press Enter/Return, the value will display in the cell.
To see or edit the formula, click on a cell and the formula will appear in the formula bar above. You’ll notice that Excel highlights cell references in different colours to help you find them in the sheet.
When you reference cells, there are two types of reference: relative and absolute. Let’s understand the difference between both through an example.
Relative referencing
In this example, we want to calculate the total for each line item. Starting at the top, in cell D2, the formula would be: =B2*C2
Instead of having to type the formula into every cell, relative referencing enables us to copy the formula by expanding the bottom right-hand corner downwards across all required rows. This automatically adjusts the formula in each cell to represent the correct row.
Absolute referencing
However, sometimes you don’t want a cell reference to change when you’re filling cells. Unlike relative references, absolute references don’t change when copied or filled. You can use an absolute reference to keep a row and/or column constant. An absolute reference is designated in a formula by the addition of a dollar sign ($), which can precede the column reference, the row reference, or both.
| Absolute reference | Behaviour |
|---|---|
| $B$2 | The column and the row do not change when copied. |
| B$2 | The row does not change when copied. |
| $B2 | The column does not change when copied. |
Let’s look at an example in which we want to calculate the sales tax $-value of each line item, given the tax rate in %.
We type in the formula, highlight the cell reference we want to turn absolute, press F4 until the correct format appears, and press Enter for the result to appear. Now we can expand the cell overall rows (until Row 6) to get the sales tax $-amount for each line item.
Next, let’s learn about the various functions available in Excel.
Introduction to Data Science for Business
