A spreadsheet package is used for performing calculations and drawing charts. When you open a spreadsheet package the screen looks like 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 pupils 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. Formulas always start with an = sign. Some simple example formulae are :
|=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 formulas 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 :
|=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 want to use a similar formula several times in a spreadsheet. In the spreadsheet below a formula which is nearly the same is used to calculate each students total mark. The formula is changed 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 :
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 :
1, 2, 3, 4, 5, 6, 7
8, 10, 12, 14, 16, 18
January, February, March, April, May
1/1/97, 2/1/97, 3/1/97, 4/1/97
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 find information. The Excel 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 formula =VLOOKUP(A1:C3,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.
Most spreadsheets will also let you sort 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 (A1:C4) was selected. The table was then sorted using the Model field into ascending order.
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. Title can also be centred 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.
Graphs can be created from data in a table in a spreadsheet. To create a graph you will need to :
To draw a graph you will need to set a number of options such as :
When you save a spreadsheet the graphs you have drawn from it will be saved with it. If you change any of the figures on your sheet the graph should change automatically.
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.
A macro can be created by either programming or recording it.
(1) 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 :
SORT USING COLUMN F DESCENDING
Most computer users do not have time to learn how to program macros, so a simpler method of creating basic macros is available.
(2) 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.
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 :
After you have set up a spreadsheet you will need to test whether or not it operates correctly. You can do this in two ways :
For project work you will need to document the tests that you have carried out to convince the person who is marking your work that your spreadsheet operates correctly.
Spreadsheets are used for applications (jobs) which involve calculation or graph-plotting. Some example application areas are :
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 :
You should follow these steps to help you design a spreadsheet :
|(1)||What is a spreadsheet program used for ?|
|(2)||Give an example of an application which you may choose to use a spreadsheet for, and explain why |
a spreadsheet is appropriate.
|(3)||Define these important spreadsheet terms :|
|(4)||What three types of data can be typed into a cell ?|
|(5)||What is the purpose of these three functions ?|
|(6)||Why is it useful to be able to replicate formulas ?|
|(7)||Suggest how a cell containing the price of a meal in a cafe might be formatted.|
|(8)||Look at the spreadsheet below. Jenny is using it to calculate how much money she spends each week on different
items. The rows are named by numbers and the columns are named by letters.|
a) Which cell contains the number £1.60 ?
b) Jenny has selected a range of the spreadsheet using a mouse. This range is surrounded by a box like the one below :
c) What is the range that Jenny has selected ?
d) What are the numbers in the column labelled Quantity used for ?
e) Jenny wants to work out how much money she spends in total each week and put the answer in cell D5. Write down the formula that Jenny should put into cell D5 to do this. You should use the mathematical functions provided by the spreadsheet.
|(9)||Why would you need to change the width of a column ?|
|(10)||What is a macro ? Why are macros useful ?|
|(11)||How would you go about testing a spreadsheet ?|
|(12)||You have two different spreadsheets that you can use at school. Suggest five different criteria you could use to compare the two packages if you had to select one to carry out a task.|
(C) P. Meakin 1998