Spreadsheets

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 :

Numbers : Any number.
Labels : Combinations of letters and numbers used as titles or labels to make the sheet clearer.
Formulas : Used to carry out calculations with the numbers that are entered into the sheet.

Whenever any numbers are changed the calculations worked out by the formulas will be Recalculated.

A Simple Example

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.

Formulas

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 :

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.

Functions

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 :

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.

Filling / Copying Formulae

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 student’s 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 :

Type the first (base) formula =B2+C2 into cell D2.
Select the cells from D2 down to D5.
Choose the Fill Down option.

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.

A Relative Reference changes when it is copied to another place in the spreadsheet.
An Absolute Reference does not change when it is copied to another place in the spreadsheet.

As well as Fill Down and Fill Right, the usual Cut, Copy and Paste operations can be used to copy (or replicate) formulas.

Fill Series

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.

Condition Functions

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

Database Functions

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.

Sorting

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 :

Select the area of the spreadsheet that contains the data to be sorted.
Choose which piece of information in the table you want to use to order the data.
Select whether you want to put the data in ascending (going up) or descending (going down) order.

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.

Formatting

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 :

Set the number of decimal places the value is displayed to.
Display the value as a currency. E.g. display 2300.5 as £2300.50.
Display the value as a percentage. E.g. display 0.876 as 88%.
Display the value as a date.

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.

Charting

Graphs can be created from data in a table in a spreadsheet. To create a graph you will need to :

Enter or calculate the figures for the chart and put them into a table.
Select the table.
Choose the graph drawing option.

To draw a graph you will need to set a number of options such as :

What type of graph (e.g. line, bar, pie) do you want ?
How should the graph and axis be labelled ?
Do you want gridlines or a border on the graph ?
Do you want a legend to label different bars or lines on the chart ?

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.

Macros

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 :

Select the area of the spreadsheet that contains the data to be sorted (the league table).
Choose which piece of information (e.g. points) in the table you want to use to order the data.
Select whether you want to put the data in ascending or descending order.

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.

(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 :

SELECT A1:F6
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 :

Turn on the macro record feature.
Carry out the actions that the macro must follow manually using a mouse and keyboard.
Turn off the macro record feature.

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 :

From A Menu : If you choose the macro's name from the spreadsheet's macro menu it will automatically be executed.
From A Button : Macros can usually be assigned (linked) to buttons which you can create on the spreadsheet. If you press a button it will run the macro that has been associated with it.

Testing A Spreadsheet

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 :

By hand (or calculator!) calculate the results that you expect to get from your spreadsheet if it works. Then compare these to the results that your spreadsheet actually produces. Do they agree or not ? You can help yourself out by putting figures into your spreadsheet that will make the calculations easy to carry out manually.
Try changing some figures in your spreadsheet. When you do this check that other figures change as you would expect them to. For example if you increase the number of chocolate bars sold in a week do the profits for that week increase correctly ?

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.

Typical Applications

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.

Why Use a Spreadsheet ?

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 :

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.
Information presented using a spreadsheet is usually neater than handwritten calculations. The results of such calculations can be easily graphed with a spreadsheet.
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.
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.

Designing a Spreadsheet

You should follow these steps to help you design a spreadsheet :

Decide why you are setting up the spreadsheet - why is it needed ?
Decide what outputs you will want the spreadsheet to produce - what must it calculate ?
Use the information about the outputs you want to get to decide inputs you must put into the spreadsheet.
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.
Plan the layout of the spreadsheet including where information will be placed and how it will be formatted to make it easy to use.
Create the spreadsheet you have designed.
Using simple figures test that the spreadsheet is calculating the outputs correctly.
Collect the real data to be used as inputs to the spreadsheet.
Put in the real data to use for the calculations to obtain the correct outputs.

Summary

A spreadsheet is a package used for performing calculations and drawing graphs.
A spreadsheet is divided into cells. Rows run across the screen and columns run down the screen. Each cell had a name called a cell reference. A group of cells is known as a range.
Numbers, labels and formulas are the three different types of information that you can put into a cell.
Formulas such as =A1+B1, =A7*A8/100 can be used to get the spreadsheet to perform calculations.
Functions such as SUM, MIN, RAND can perform calculations on ranges of data.
Most spreadsheets contain many functions. They are divided up into classes such as mathematical, financial, statistical and database functions.
You can replicate (copy) formulas using Fill Down, Fill Right and Copy and Paste.
Relative references change as they are copied. Absolute references do not change as they are copied.
A series of numbers such as 1,3,5,7 can be generated using Fill Series.
A condition function like IF can use data in the spreadsheet to make a decision.
The VLOOKUP and HLOOKUP database functions can be used to search through a table in a spreadsheet as if it were a database.
Data in spreadsheet cells can be formatted (e.g. bolded, underlined, left and right justified). Numbers can be formatted to fixed numbers of decimal places or to be displayed as currency.
Extra rows and columns can be added to a spreadsheet and unwanted ones can be deleted. The width of columns and height of rows can be adjusted to fit the data in them.
Spreadsheet data can be used to plot graphs.
Some spreadsheets will let you create macros to automate common tasks.
Spreadsheets should be tested when they have been set up. Comparing the output of a spreadsheet with manual calculations using the data or noting the effects of changing the data in the spreadsheet are both methods of testing a spreadsheet.
Typical applications of spreadsheets include simulations, optimisation, book-keeping and presentations.
Spreadsheets are useful because, once you have set up the formulas, they will carry out calculations quickly without making calculating errors. The results of spreadsheets calculations can be presented more neatly than handwritten ones and as charts. If you change any data the calculations will be automatically carried out again, so spreadsheets are particularly useful if the data is likely to change or if the same calculations have to be performed on several sets of data . For some very complicated simulations it would be impossible to perform all the necessary calculations by hand.

Questions

(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 : a) Cell b) Cell Reference c) Range d) Formula e) Relative Reference f) Absolute Reference g) Cell Format h) Data Format
(4)	What three types of data can be typed into a cell ?
(5)	What is the purpose of these three functions ? a) =SUM() b) =AVG() c) =MAX()
(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