Spreadsheets 3

Gap-fill exercise

Fill in all the gaps, then press "Check" to check your answers. Use the "Hint" button to get a free letter if an answer is giving you trouble. You can also click on the "[?]" button to get a clue.

   $      absolute      Absolute      column      ELSE      equal      errors      fixed      Good      greater      IF      modified      Poor      relative      row      THEN   
Part 3

formulae involving conditions (IF(..))

A formula can contain a condition, e.g. IF. to work out an IF formula the spreadsheet calculates the value of the expression. If the resulting value is true THEN the spreadsheet goes on to work out the value as expressed in the THEN statement. If the condition is false it works out the second expression.

e.g.

This can be used to work out if a value is greater than another, for example:

sprfigure4.gif

In this example the formula used is shown in the formula bar above the spreadsheet. The formula compares the value in cell H2 with 3000.

If the value is than or to 3000 then is shown in cell I2. Otherwise is shown.

Cell references

There are two types of cell references that we can use in formulae. A reference will change when the formula in it is replicated to other cells. An reference will not change when a formula is replcated.

cell references are very useful for referring to specific cells in a spreadsheet (i.e. a wage rate or V.A.T. percentage).

???

If we want the of a cell identifier to stay the same in the formula when replicated, we put the ' ' in front of the row identifier (e.g. D 4+D 5).

If we want the of a cell identifier to stay the same then we put the ' ' in front of the column identifier in the cell (e.g. E3- E4).

If we want both to remain when the formula is replicated then we place the ' ' in front of both the row and the column reference (e.g. D 4+ D 5).

Cell protection

Most spreadsheets allow protection of blocks of cells. This means that the cells cannot be . This prevents in your spreadsheet, caused by accidents. If you want to change a protected spreadsheet you would switch off the cell protection, make the chnages, and then switch the protection back on.