Spreadsheets |
A spreadsheet package is used for performing calculations and drawing charts. When you open a spreadsheet package the screen displays a very large table similar to this :
The table is divided into a number of boxes called cells. You can type information into each cell. Going across the table are many rows each of which is identified by a number. Going down the table are many columns each of which is identified by a letter.
Each cell is identified by a unique name called a cell reference. The cell reference is formed by writing down the letter of the column that the cell is in followed by the number of the row that it is in.
Groups of joined cells are known as a range. A range can be described by writing down the cell references of the cells at the top left and bottom right corners of the range with a colon to separate them.
You can enter three different types of information into a cell. The three types are :
Whenever any numbers are changed the calculations worked out by the formulas will be recalculated.
This simple spreadsheet is used to calculate exam marks. The marks of each pupil in the two separate papers which make up the exam must be typed in. Formulas are then used to calculate each pupil’s total mark and the average mark for the whole class in each paper.
The first diagram shows the numbers, labels and formulas that were typed in. The second shows the answers that were calculated by the spreadsheet.
A formula carries out a calculation using numbers that have been typed into the spreadsheet. The answer appears in the cell you type the formula into. In most spreadsheets formulas always start with a special symbol such as the equals sign. Some simple example formulae are :
| Formula | Explanation | Formula | Explanation |
| =A1+B7 | Add up numbers in A1 and B7. | =A2/C8 | Divide number in A2 by C8. |
| =B2-C2 | Subtract number in C2 from B2. | =A1*B1 | Multiply numbers in A1 and B1. |
| =A1+B7-C2 | Add number in A1 to B7 then subtract number in C2. | =(A1+A2)/B7 | Add together number in A1 and A2. Then divide by number in B7. |
When you change any numbers the results of the formulas that use the numbers will be recalculated.
Formulae can sometimes become very long if you want to include a lot of numbers in the calculation. Long formulae are tedious to type and can lead to errors. Consider a formula to add up fifty numbers in column A :
= A1 + A2 + A3 + A4 + … + A50
Functions can be used in formulae to make some calculations simpler to carry out. Functions often act on ranges of numbers. Important functions supported by most spreadsheets include :
| Function | Explanation |
| =SUM(A1:C2) | Adds up the contents of the cells in the range A1:C2. These cells are A1,A2,B1,B2,C1 and C2. |
| =AVG(A1:C2) | Averages the contents of the cells in the range A1:C2. |
| =MIN(A1:C2) | Finds the smallest number in the cells in the range A1:C2. |
| =MAX(A1:C2) | Finds the largest number in the cells in the range A1:C2. |
| =CNT(A1:C2) | Counts how many numbers are in the range A1:C2. |
| =PI() | Gives the value of PI. |
| =RAND() | Gives a random decimal number between 0 and 1. |
| =INT(A1) | Gives the integer (whole number part) of the contents of A1. |
Most spreadsheets have many more functions than this. They will include financial, statistical and database functions.
Often you will want to use several similar formulae in a spreadsheet. In the spreadsheet below a formula which is nearly the same is used to calculate each student’s total mark. The formula is changed slightly for each pupil because their marks are on different rows.
To avoid the need to type a similar formula in the three formulas in rows 3 to 5 can be generated automatically using the formula in row 2. To do this you must :
Copying a formula like this is known as replication. If you want to copy a formula along a row rather than down a column you can use the fill right option instead. Because the formula changes as you copy it is known as a relative formula, or relative reference.
Sometimes you may not want a reference to a cell to change when a cell is copied in this way. To prevent a cell reference in a formula changing when you copy it you should precede the letters and numbers in the reference by a $ sign. Look at this formula :
=D3* $A$1
If you chose the fill down option with this formula the next row will contain the formula =D4*$A$1, the one after the formula =D5*$A$1 and so on. The first cell reference D3 changes but the second, A1, does not. Because the A1 reference does not change it is known as an absolute reference.
As well as fill down and fill right, the usual cut, copy and paste operations can be used to copy (or replicate) formulas.
You may wish to put a series of numbers or values that follow on from each other into a row or column on your spreadsheet. Most spreadsheets have a fill series operation to do this. Example series include :
To put a series of values into a cell you will need to :
Type the initial (first) value into the cell you want it to appear in.
Select the cells you want the series to be put into, including the first value.
Choose the fill series option.
You may then have to enter the step that the series should increase by each time.
The spreadsheet can use a condition function to make a decision based on a value stored in the spreadsheet. The formula :
= IF(A1>=100,"Boiling","Not Boiling")
will output the message "Boiling" if the contents of cell A1 is greater than or equal to 100 and will output the message "Not Boiling" otherwise.
If statements can be nested (put inside each other) to give more than two responses based on several conditions. The formula :
= IF (A1<=0,"Frozen",IF(A1>=100,"Boiling","Liquid"))
will display "Frozen" if A1 contains a value less than or equal to 0. Otherwise, if A1 contains a value greater than or equal to 100 it will display "Boiling". If neither of these are true it will display "Liquid".
Most spreadsheets include database functions which can be used to treat a table in the spreadsheet as if it were a database. These functions typically let you search through the table to lookup information. A common spreadsheet has a VLOOKUP function which looks down a table to find a value. It is most easily understood by using an example.
This section of a spreadsheet is a table containing information about cars.
The column titles are the field names. Each row (apart from the top one) contains a record.
The formula =VLOOKUP("Corsa",A2:C4,2) will look down column A cells A2 to A4 for the value "Corsa". If it finds it, it will return the value in the same row but two columns to the rights, which is the price of a Corsa, £8,295. If the value "Corsa" was not in the list then the formula would display an error message.
The HLOOKUP function is similar to VLOOKUP except it looks along rows for data instead of looking down columns.
A spreadsheet's sort function will let you put the data in a table into order using any of the columns of data in the table. To sort data in a spreadsheet you will need to :
The data in this table has been sorted :
To perform the sort the table range (A2:C4) was selected. The table was then sorted using the data in column A into ascending order. Note that row 1 was not included in the sort range because it contains the titles for the table rather than data.
Information in spreadsheet cells can be formatted like text in a word processor. The font can be changed and information can be aligned to the left, right or centre of a cell. Titles can also be centered across multiple columns. Borders can be placed around cells to make distinct tables and cells can be filled with colour to make a spreadsheet clearer. Changing the appearance of the information in a cell like this is known as changing the cell format.
The appearance of numbers in cells (called the data format) can also be controlled. Example formats you may want to apply to a value in a cell are :
You should also be able to change the width of columns and the height of rows to suit the data that has been typed in. If necessary extra rows or columns can be inserted into the spreadsheet and unwanted rows or columns can be deleted.
Charts can be created from data in a table in a spreadsheet. To create a chart you will need to :
To draw a chart you will need to set a number of options such as :
When you save a spreadsheet the charts you have drawn from it will be saved with it. If you change any of the figures on your sheet the charts should change automatically.
Alternatively, a specialised charting package could be used to produce a chart.
A macro is a short sequence of instructions that will automate a task. Using macros can save you a lot of work when you are using a spreadsheet by carrying out repetitive tasks for you. Not all spreadsheet programs will let you use macros.
Consider a spreadsheet which shows the league positions of some football teams in a league. Whenever a team plays a match the team's points score and position in the league may change. You will need to sort the data in the spreadsheet to put the teams into points order after each match. To sort data in most spreadsheets you will need to follow these three steps :
Manually completing this process every time a team played a match would be very time consuming. Instead a macro can be created that will automatically carry out the three steps for you. Once a macro is created you can carry out the entire process by simply executing (or running) the macro.
Creating A Macro
A macro can be created by either programming or recording it.
| Programming A Macro | A macro is stored as a sequence of instructions in a macro language. Programming a macro is like writing a program in a high level language. The user writes the instructions that will carry out the actions required of the macro. The macro instructions to sort the football league table might look something like this :
Most computer users do not have time to learn how to program macros, so a simpler method of creating basic macros is available. |
| Recording a Macro | To record a macro you simply show the computer what you want the macro to do.
A macro can be recorded like this :
When this process is followed the spreadsheet will automatically generate the macro instructions as if the macro had been programmed. |
When a macro is being designed a flowchart is often used to show the sequence of actions that the macro should carry out.
Executing A Macro
Every macro must have a name to identify it. When a macro is selected and its macro instructions are carried out the macro is said to be executing or running. There are two ways that you can execute a macro :
Sophisticated spreadsheet packages can validate data that a user enters into a spreadsheet to ensure that it is sensible. Data is validated as it is typed into a cell.
Validation checks are specified at the time that the spreadsheet file is created. Here are some common types of validation checks that can be carried out by spreadsheet software :
| Validation Checks | ||
| Presence Check | Checks that data has been entered into a cell and that it has not been left blank. | 
|
| Type Check | Checks that an entered value is of a particular type. e.g. check that age is numeric. |
|
| Length Check | Checks than an entered value e.g. surname is no longer than a set number of characters. |
|
| Range Check | Checks that an entered value falls within a particular range. For example the age of a person should be in the range 0 to 130 years. |
|
| Format Check | Checks that an entered value has a particular format. e.g. a new-style car registration number should consist of a letter followed by 1 to 3 numbers followed by 3 letters. |
|
| Table Lookup Check | Checks that an entered value is one of a pre-defined list of valid entries which should be allowed. |
|
Spreadsheets are used for applications (jobs) which involve calculation or graph-plotting. Some example application areas are :
| Financial Modelling | Organisations like the treasury model mathematically the effect that changes in economic variables such as the rate of interest will have on the economy. Simple versions of such models can be set up using a spreadsheet. Such models are often known as simulations. |
| Optimisation Problems | Spreadsheet models can be used to find optimal solutions to problems. For example what should the dimensions of a can be to store a fixed volume of liquid at the cheapest possible price ? Or how many architects should be employed for how long to find the largest number of items at an archaeological site within a fixed budget ? |
| Book-Keeping | Spreadsheets can be used for general financial tasks such as stock control, payroll, profit calculations etc. |
| Presentations | Graphs produced using a spreadsheet can be incorporated into printed publications or presentations. |
Spreadsheet models (a type of simulation) can be used to investigate what happens when variables in a model are changed. For example here is a simple model of the cost of producing a packed lunch :
The model uses formulas to calculate the total cost of producing a packed lunch from the individual costs of the items and the quantities required. It could be used to answer questions such as :
| Question | How Answered |
| What would happen to the cost of a lunch if the number of slices of ham required fell to 2. | The number in cell B2 is changed from 3 to 2. |
| What would happen to the cost of a lunch if crisps rose in price from 27p to 29p ? | The number in cell D5 is changed from £0.27 to £0.29. |
| What would happen to the cost of a lunch if an apple was no longer required ? | The number in cell B7 is changed from 1 to 0 or row 7 is deleted. |
Because the values in column E are calculated using formulas when the values typed into the spreadsheet are changed the totals are automatically recalculated. It is for this reason that spreadsheets are often used to answer "what-would-happen-if" (or "what-if") problems. Once the formulas have been created, the effects of changing values can be determined easily and quickly.
Spreadsheets can be used to answer much more complicated questions. Typical "what-if" problems could be :
1.
| What would happen to the school tuck shop's profits if the cost of buying sweets rises by 10%?
|
| 2.
| If a company made two workers redundant, what would happen to the company's
wage bill and profits?
|
| 3.
| What would be the effect on an airline's profit if the number of passengers flying
with it rose by 50% as a result of a 15% reduction in ticket fares ?
|
| 4.
| What would be the effect on the economy of a 1% rise in the interest rate ?
| |
There are many reasons why you might want to use a spreadsheet to perform some calculations rather than carry them out by hand. Some good reasons to use a spreadsheet include :
1.
| If you have to carry out several calculations by hand (or using a calculator) then
it is easy to make a mistake. If you use a spreadsheet then, providing you get the
formulas right, the computer will not make any calculating mistakes.
|
| 2.
| Information presented using a spreadsheet is usually neater than handwritten
calculations. The results of such calculations can be easily graphed with a
spreadsheet.
|
| 3.
| Sometimes you have to perform the same calculation on a lot of data (e.g.
convert lots of different amounts from one currency to another) or on data that
changes frequently. Once you have set up the correct formulas the
spreadsheet can use them again and again on different sets if data.
|
| 4.
| Some calculations would be extremely difficult to perform by hand. For example can
you imagine the number of calculations that would have to be performed to predict
the effect of a rise in interest rates of 0.25% on the economy. If a spreadsheet
model is set up then you can simply change the interest rate and it will
automatically perform all the calculations to show you the predicted effect.
|
| |
You should follow these eight steps to help you design a spreadsheet :
| 1. | Decide why you are setting up the spreadsheet - why is it needed ? |
| 2. | Decide what outputs you want the spreadsheet to produce - what must it calculate ? |
| 3. | Use the information about the outputs you want to get to decide what inputs you must put into the spreadsheet. |
| 4. | Plan the calculations that you will need to perform with the inputs to work out the outputs. It may help to split the calculations up into different parts to make this stage easier. |
| 5. | Plan the layout of the spreadsheet including where information will be placed and how it will be formatted to make it easy to use. Consider how replication can be used to create the spreadsheet efficiently. |
| 6. | Plan any macros that you are going to create to automate tasks such as sorting. |
| 7. | If the spreadsheet you are using supports them decide what validation rules to use to ensure entered data is sensible. |
| 8. | Identify the data that you will use and the tests that you will perform to check the spreadsheet is working, and write this down in a test plan. |
What To Test
After you have created a spreadsheet you need to test it to ensure that it is working properly. There are three main areas that you need to test. They are :
| 1. | The most important thing to test is that the formulae in the spreadsheet produce the correct answers. |
| 2. | If you have carried out any validation you need to check that valid inputs are accepted and invalid inputs are rejected. |
| 3. | If you have created any macros you will need to test that these perform the correct actions at the correct times. |
There are two different methods that you can use to test your formulae. They are :
The Test Plan
You should created a test plan that lists all of the tests you will carry out on your spreadsheet. In the test plan you should state :
If your spreadsheet is being produced as a piece of coursework it is a good idea to give each test a number.This will allow you to link the tests in your plan to evidence you later produce to show that you have carried them out.
Test Results
When you carry out the tests of your spreadsheet you need to check that the actual results you obtain match the results you expected to get. If they do not then you will need to work out why not and try to correct the problem. You will then need to carry out the test again.
If your spreadsheet is being produced as a piece of coursework you should collect evidence to show that you have carried out the tests and cross-reference this evidence to the descriptions of the tests in the test plan.
GCSE ICT Companion 04 - (C) P Meakin 2004