The things you need
to know about spreadsheets are summarised here, with particular reference to
Microsoft Excel, though note that much of what is
written for Excel will apply to most other "Windows" spreadsheets as
well.
Absolute cell references
See Relative cell references
Average The average function, which typically may be AVERAGE(A1:A5) or
AVR(A1:A5), works out the mean average of the numbers in cells A1 to A5.
Boolean operators
Most spreadsheets include the operators IF, AND, OR, NOT,
TRUE and FALSE which can be used in the construction of conditional functions.
Cells
See also Copy cells, Relative cell
references, Selecting cells, Range
Obviously, you need to know what a
spreadsheet actually is and what it looks like before you use one. A
spreadsheet consists of a grid of rows and columns. Each box in this grid is
called a cell.
The columns are labelled with letters
(A, B, C ...) and the row are numbered (1, 2, 3 ...). Thus any box or cell in
this grid can be referred to in terms of its column and row labels, for example
C3, D17, AA45 etc.
The cell at the extreme bottom right
in Excel is IV16384 - so there are 256 columns and 16384 rows. These
notes will keep to the A1 style of cell referencing.
Charts
Modern spreadsheets usually contain a
wide selection of types of charts useful for displaying data, particularly
Scatter graphs or xy graphs.
First select the cells to be charted.
(If the two columns of cells are not side by side, you must hold down
Ctrl in between selecting columns.) Now, select Chart. The way
this is done depends on whether you are using Excel 3, 4
or 5. In Excel 4 there is a "chart wizard" on the menu bar that
will take you through the steps. You can then select the type of graph you want
from:
Area, Bar, Column, Line, Pie, Radar,
XY (Scatter), Combination, 3-D
Some spreadsheets (including Excel)
claim to be capable of producing histograms but in fact they display bar
charts. Are there any exceptions to this...?
Circular cell references
See also Formulas, Relative cell
references, Selecting cells
Any cell in a spreadsheet may contain
a formula that refers to any other cells in that spreadsheet. However, suppose
the formula in cell B17 is B16+B17, then the cell is refering to itself. This
called circular cell referencing and in many spreadsheets constitutes an
error. Some other spreadsheets can make sense of circular referencing and can
be programmed to incorporate it into a recursive or iterative prodecure.
Columns Data in spreadsheets is arranged in columns (and rows). The
columns are usually lettered, A, B, C etc.
Copy cells
You can copy the contents of one cell
to one or more other cells. Copying formulas is the single most effective way
of using the calculating power of a spreadsheet. As a simple example, suppose
we wish to put the whole numbers from 2 to 12 in row 1 . Proceed as follows:
- In the first cell, A1, enter 2
- In the second cell, B1, enter the
formula A1+2. The cell will display the value 4. This formula has then to be
copied from cell B1 and pasted to the range of cells C1 to F1.
- Select cell B1 and Copy, then
select cells C1 to F1 and Paste.
- The cells A1 to F1 should now
display the values 2 to 12.
Look at the contents of cells C1
onwards. You will find they contain the formula, adjusted to fit. This is
called relative cell referencing. For example, C1 will contain B1+2, D1
will contain C1+2, etc.
Note for users of Excel 4 and
above: as an alternative to step 3, select the cells B1 to F1, then, from the
menu, select Edit... Fill right; or, as a handy shortcut, move the
cursor to the bottom right corner of cell B1. The cursor will change to a small
cross. Now drag across cells C1 to F1.
Errors
Most modern spreadsheets can tolerate all kinds of complicated formulas and
very large or small numbers too. However an error will be produced by formulas
that attempt to:
- divide by zero
- add a number to text
Formulas
See also Statistics functions
The essential quality of a spreadsheet
is that in any cell you can enter numbers, text or formulas. These formulas
will probably refer to the contents of other cells in the spreadsheet. If 5 is
entered in cell B12 and 6 in cell D15, and the formula B12+D15 is entered in
cell F20, then that cell will display the value 11. To enter a formula in a
cell the usual method is:
- select the cell (by clicking on it)
- type =
- type the formula
- press ENTER
You must use * (the asterisk) to
indicate multiply, e.g., 2*A3 or A5*(B1+B2). In most spreadsheets nowadays you
can enter formulas more easily using the mouse. For example, to enter the
formula B12+D15 into cell F20:
- select cell F20 (by clicking on it)
- type =
- click on cell B12
- type +
- click on cell D15
- press ENTER
Besides the basic mathematical
operators, a variety of special mathematical functions is usually available,
including:
- ABS(A1) the absolute value of the
number in A1
- AVERAGE(A1:A5) calculates the mean
average of the data
- COS(A1) the cosine of the angle in
cell A1 (Excel works in radians)
- INT(A1) the integer part of the
value in cell A1
- MAX(A1:A10) the maximum value in the
cells A1 to A10
- MIN(A1:A10) the minimum value in the
cells A1 to A10
- SIN(A1) the sine of the angle in
cell A1 (Excel works in radians)
- SQRT(A1) the square root of the
value in A1
- SUM(A1:A10) the sum total of cells
A1 to A10
- TAN(A1) the tangent of the angle in
cell A1 (Excel works in radians)
- ^ the power function, for example
A1^3 means the cube of A1.
To use Excel to plot a trig
function, the angle given must first be converted from radians to degrees. To
do this, use, for example SIN(A1*PI()/180) instead of SIN(A1).
For a more extensive list of functions
and formulas, Select Formula... Paste function and choose from the list
given.
Functions
See Formulas and Statistics functions
- G
-
Graphs
See Charts
Help
All Windows programs include Help files. The Excel Help files are very detailed
and make a printed manual unnecessary.
IF
The IF function is useful in many
tasks in this book to make the contents of a cell conditional on the value of
something else.
IF(relation,outcome1,outcome2)
e.g., IF(A3<10, A3,2*A3)
relation may be something like
A2>10 or B3=20, i.e., a mathematical relation that must be either true or
false. If relation is true then outcome1 becomes the value of the
function, otherwise outcome2 is the value.
The example shown above will produce
the value of A3 if A3 is less than 10 and twice A3 otherwise.
Load
In order to load files that you saved
previously, select File... Open. Then choose the file required from the
list shown.
Macros
Macros are short cut routines which allow you do carry out a number of steps at
once, usually by pressing a button or specified key.
Mouse
The mouse is used to select cells and carry out instructions. For example, the
formula A1+B1+C1 can be entered in a cell either by typing of all the letters
and numbers. Alternatively, you must still type the + signs but the cell
references can be entered by clicking on the cell required. Similarly a range
of cells such as A1:A10 may be entered by clicking on cell A1 and then
dragging the mouse (holding the button down) over the remaining cells.
Move
You will to need to move from one cell
to another and possibly from one group of cells to another, left or right, up
or down. This can be done by using the mouse either to click on the arrows in
the bars at the bottom and right hand edges of the window, or by moving the
rectangles in these bars. You can also use the cursor keys to move from one
cell to the next.
Names
See also Range
Cells or ranges of cells can be named.
This makes them easier to refer to and can make use of relative and absolute
cell references clearer. The teacher's files accompanying this book make
extensive use of named cells. To name cells, first highlight the range of cells
you wish to name, then select Formula... Define name and enter a name in
the indicated space.
As an example, here is a simple
spreadsheet that calculates the area of a rectangle given its length and width.
|
A
|
B
|
C
|
|
1
|
length |
width |
area |
|
2
|
5 |
7 |
35 |
The formula in cell C2 could be
"=A2*B2". However, if cell A2 was named length and B2 named
width, then the formula in cell C2 could read "=length*width". The
spreadsheet thus becomes easier to read and interpret.
Negative numbers
In many spreadsheets you can customise the display so that
negative numbers are displayed in red.
Number
To enter a number in a cell, select the cell, type the number, then press
ENTER.
Number format
The way numbers are displayed on your screen can be
controlled. You can have more or less decimal places, or have standard index
form, or many other formats. To do this, first select the cells that contain
numbers whose format you want to change.
Select Format... Number. Select
the Number (or Scientific) category and then the required Format code. The code
0 means integer format; 0.00 means two decimal places. To
customise this code simply add zeros as required in the Code window at the
bottom.
Note that you may need to alter the
column width to accommodate large, or very small, numbers.
Operators
See also Formulas The conventional mathematical
operators will be available, namely +, -, / (divide), * (multiply) and ^
(power).
Range
See also Names
It is often useful to refer to a range
of cells at once, rather than just one single cell. Suppose all of the cells A1
to A10 need to be referred to in a formula, for instance when finding the mean
average. In this case, we use the notation A1:A10 to refer to this range. A
rectangular grid can also be referenced, for example A1:C5 refers to the 15
cells in the 5 by 3 array with A1 at the top left and C5 at the bottom right.
These cells can be selected by dragging the mouse over them.
Relative cell references
The use of relative and absolute cell referencing is
essential if the spreadsheet is to be fully adaptable and efficient.
The meaning of the terms relative and
absolute cell referencing can be difficult to understand at first. Look at the
two diagrams.
|
A
|
B
|
|
1
|
4 |
|
|
2
|
11 |
44 |
|
3
|
12 |
48 |
|
4
|
13 |
52 |
|
|
|
A
|
B
|
|
1
|
4 |
|
|
2
|
11 |
=A2*$A$1
|
|
3
|
12 |
=A3*$A$1
|
|
4
|
13 |
=A4*$A$1
|
|
The one to the left shows the
values as calculated by some formulas and the one to the right shows those
formulas. Each formula refers to the cell to its left, i.e., A2, A3 or A4, and
also, in all cases, to the cell A1. A1 is actually referred to as $A$1 which is
the way to indicate an absolute cell reference. The first formula was typed in
manually as A2*$A$1 but the others below it were created by replication (or
copying cells). This replication process will not alter an absolute cell
reference in any way. To summarise:
Relative cell reference: A3
Absolute cell reference: $A$3 or by
using names
Rows
Spreadsheet data is arranged in rows (and columns). The rows are usually
numbered.
Save
In order to keep your work to return to it another time, you must know how to
save it. Select File... Save and type the name of your file in the space
indicated.
Selecting cells
There are two principle ways of selecting, or highlighting,
any cell, or range of cells, on the spreadsheet:
- If the spreadsheet uses a mouse,
then click on the required cell. Hold the mouse button down to select a range
of cells.
- If the spreadsheet uses the cursor
keys, then move the highlighted cell appropriately using those keys.
Sort
You may need to rearrange the cells in a column or row into a different order,
perhaps alphabetical or according to size. Suppose you want to sort the cells
in a column:
First highlight the whole area that
you want included in the rearrangement. Select Data... Sort. Then click
in the space headed 1st key and then on the column you wish to sort by.
If you make a mistake, select Edit... Undo at once.
Statistics functions
See also Formulas
All modern spreadsheets contain a
range of functions for working out statistical measures. Assume data has been
entered in cells A1 to A5. To produce a tally count (or frequency
distribution) of this data, enter the upper limits of each category into cells
B1 to B5, then use:
FREQUENCY(A1:A5,B1:B5)
(only available in Excel Version 4 and
above)
The usage of these two functions will
require further explanation. Suppose the frequencies are to be put in cells C1
to C5:
- highlight cells C1 to C5
- type in=FREQUENCY(A1:A5,B1:B5)
- hold down the two keys CTRL and
SHIFT and press ENTER
- the frequencies should now appear in
all 5 cells.
Text
(Also referred to as labels.) Apart from formulas and numbers, the most
common type of data to enter into spreadsheet cells is text. Select the cell,
type the text, then press ENTER.
Windows The mouse is used to select functions and options. Icons and
buttons are clicked on to perform standard procedures.
|
