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.

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

Tips & links:

‘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.

using Excel equations 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

Continue to Plotting & Charts...

Download an Excel file of the completed exercise

To toggle any cell reference between relative and absolute, select the reference in the text edit area and press control-T (Mac: cmd-T)