## Formulas & Equations:

We will now see how to manipulate data in Excel. This might be as simple as sorting values and calculating simple sums, or using a calibration equation to analyze experimental data. Although generate highly complex equations, we will start with very simple ones. Before we begin, note the table of operators below used in numerical computing. These are not exactly the same as you would see written elsewhere, but they mean the same thing.

Multiplication*2*36
Division/4/22
Exponent^2^38
Order of Operations(..)2*3+5 or 2*(3+5)11 or 16
Power of tene or E 3.2E+2 or 3.2e-2320 or 0.032

‘Operators’ perform functions on numebrs, e.g. addition & multiplication. Remember that some operators have higher precedence than otheres, and so will be used first

Entering a Formula

Absolute & Relative Cells

### Entering a Formula:

Re-open the spreadsheet from the previous exercise, and extend the data series in column `B` so that the final value is 1.5. We will use column `B` as the value of x in the equation y=2x+5; we will put the value of y in column `C`.

In cell `C1`, beside the 0 from the second series, type `=`, then click on cell `B2` (do not press enter yet). Cell `C1` should now contain the phrase `=B1`. The “=” sign tells Excel that the text following the equals sign is part of an equation.

Complete the equation by typing `*2+5` so that cell `C1` reads `=B1*2+5`. Now press enter and the cell should display 5. This is the result of 0×2+5. Note that clicking on the cell displays the actual equation in the text box located in the toolbar above the spreadsheet, while the result of the calculation remains visible in the cell itself.

You can fill the remaining cells with the same equation using the same techniques as before:

1. Click and drag to select all the cells from `C1` to `C16`, and choose Edit→Fill→Down; or
2. select cell `C1`, position the cursor over the square box at the lower-right corner of the cell, and drag this down to cell `C16`

Click on any cell from `C1` to `C16`, and you will see that Excel has updated the cell reference in the equation so that each row calculates y for a different value of x.

### Absolute & Relative Cells:

It is essential to understand how spreadsheets such as Excel use cell references. In the preceding example, the cell reference in the equation is a relative reference. This is why filling the column down with the same equation automatically resulted in successive x values being used to calculate values of y.

When Excel encounters a cell reference such as `E10`, it calculates the offset between the current cell and that location. For example:

Cell `C5` contains a reference to `E10`:
The offset is two columns to the right and 5 rows down
Cell `B9` contains a reference to `A6`:
The offset is one column to the left and 3 rows up

This is fine for compiling tables of values for y as a function of x, but what if we wanted an equation that used a constant, and we wanted to be able to change that constant for the whole table at once? This is where an absolute reference comes in: this is a reference that always points to the same cell. Most spreadsheet programs use a \$ prefix to denote an absolute reference. Further, you can specify that both the row and column positions are absolute, or that only the row or the column reference is absolute:

Cell referenceMeaning
\$E\$10always refers to column E row 10
C\$6always refers to row 6
\$M7always refers to column M

A relative reference is an offset, e.g. ‘two over and three down’

An absolute reference always points to the same place, e.g. ‘the cell in row 10, column E’

To illustrate, we will first try one more equation: the parabola y = x2 + 2 for x = -1 to +1.

1. In column `E`, create a series from -1 to 1 with an increment of +0.2
2. In cell `F1`, type the equation `=E1^2+\$A\$2`
3. Fill the series down in column `F` to match the series of x values in column `E`