I.T. - What You Need to Know About Spreadsheets

Chapter 4 – Spreadsheet
- What You Need to Know -
Important Symbols Used in Spreadsheet Formulae:
= equal sign: used at the beginning of each formula entered (e.g. =A2 + B2 – C2)
+ Addition sign: Adds values
-subtraction sign: subtracts values
* Asterisk: multiplies values
/ slash: divides values
: Colon: used to indicate a consecutive range of cells in a row or a column (e.g. =SUM(B6, C12, D15), indicating the sum of the value of the values in cells B6, C12 and D15
, comma: used to indicate a series of non-consecutive cells in formula (e.g. =SUM (b6, c12, d15), indicating the sum of the values in cells b6, b12, d15
() Parentheses: used in functions to indicate a range of values or cell reference to be calculated (e.g. =average (f1:f6), indicating the average of the values in cells f1 through f6)

Charts
Chart: a graphical or visual representation of numerical data
Labels: words or numbers, often found along the X axis and Y axis which identify information in a chart.
Legend: the area in a chart in which the colors or patterns used in the chart are displayed with the items they represent
Series: the basic unit of information in a chart often contained in a single row or column in a spreadsheet.
Values: numeric entries within a spreadsheet
X series: the labels and data charted along the X axis or horizontal axis
Y series: the labels and data charted along the Y axis or vertical axis.

Types of charts
Bar Chart: compares data or values horizontally without reference to trends over time. Bar charts show individual figures at a specific time or comparisons between different figures. The categories in a bar chart are organized vertically while the values are arranged horizontally.
Column Chart: compares data or values vertically without reference to trends over time. Column charts show individual figures. The categories in column are organized horizontally while the values are arranged vertically.
Line Chart: shows trends or changes in values over time. They show trends or changes in data at even intervals over periods of time. Although similar to an area chart, a line chart emphasizes the rate of change more than the amount of change.
Pie Chart: shows the ratio of individual values to a total, or parts to a whole. A pie chart contains one data series.
Why Use Cell Referencing?
A very important concept to use a spreadsheet is the issue of the cell referencing defined earlier. For example say we have the spreadsheet below.
Errr.
If we wanted to calculate the Sum for Jean Brown we would apply simple math as: =97 + 68
However when we use the spreadsheet we apply cell referencing and therefore our formula becomes: C2 + D2
When writing your formula in a spreadsheet, remember to always begin with an equal sign except when using the formula wizard. [fx] We also use cell referencing as much as possible instead of writing the values explicitly. This is because the formula should be built in such a way so that is you need to change a value in cell C2 but also the value in the formula in cell E2. Using cell referencing as outlined above all you would need to do is change the value in cell C2 and the formula will automatically update the value in cell E2.
Remember that you place the formula in the cell where you wish your result to be.
Another reason for using cell referencing is that it allows you to enter a formula in one cell and copy into another while maintaining its accuracy. For example, to calculate the Sum of the other persons we do not need to type in the formula again for each person. All we need to do is copy cell E2 to all the other cells. If we did not use cell referencing then the copy would not work. You would copy the same values from cell e2 to all the other cells.
How does cell referencing work?
Cell referencing works because each time you perform a copy and paste on a formula using cell referencing the spreadsheet performs a calculation. We can use this information to predict our result when a formula in a cell is copied and pasted.


A
B
C
D
E
F
1
First Name
Surname
Term
Exam
Sum

2
Jean
Brown
97
68


3
Paul
Harris
74
52


4
Michelle
Sinclair
82
88


5
Marcia
Flick
71
75
=A2

6
Mary
Brown
74
65


7
Carla
Morgan
73
98


8






9



=$A5


10





=$C$6








Using the diagram of the spreadsheet above, the value displayed in cell E5 as a result of the formula, ‘=A2’ is Jean. When cell E5 is copied into F5 the value displayed in cell F5 is Brown. Build the spreadsheet above and verify that this is correct. You would expect that is you copy E5 into F5 you would get Jean but this does not happen due to the calculation that occurs.
So how can you predict the result without using the computer?
1. Examine the location of the cell from which you are doing the copy and the cell where you want to paste the cell.
2. Count the number of columns between where you do the copy and where you paste the result.
3. Count the number of rows between where you do the copy and where you paste the result.
4. Add the number of columns to the column value if you moved to the right when doing the copy and paste. Subtract if you moved to the left.
5. Add the number of rows to the row value if you moved down when doing the copy and paste. Subtract if you moved up.
6. Combine the resultant column and row values to give the resultant formula and hence the value displayed.
Let’s apply the steps above to the copying of E5 to cell F5. Moving from cell E5 to F5 we moved 1 column and 0 rows. E5 contains the formula = A2. The calculation done is always performed on the formula = A2. The calculation is always performed on the formula in the cell and not the cell copied. The counting is done from the cell the formula is in not the formula. Therefore:
A+1 (column from A) = B
2 + 0 (row from 2) = 2
The result therefore is =B2 and the value displayed in the cell B2 is Brown.
If you copy E5 into for F7 we moved one column and 2 rows, therefore the result is:
A+1 (column from A) = B
2 + 2 (row from 2) = 4
The result therefore is =B4 and the value displayed in the cell B4 is Sinclair.
Using absolute cell referencing.
If each time you try to copy the formula in a cell a calculation occurs how do I actually get the same value in another cell by performing a copy and paste. This is achieved by using absolute cell referencing. Essentially what this does is to tell the spreadsheet not to perform the usual calculation and just give the formula the same as before. So if we copy F10 to F11 we would obtain 74 as usual calculation that is performed when a cell is copied and pasted.
The absolute cell reference sign ($) occurs before the column and or row and will only be applied on the row or column that it precedes. It may appear before the column only, row only or before both. Therefore if copying a cell with the formula $B2 means that the absolute cell referencing will only be applied to the column and not to the row.
Example
What is the resultant formula when D9 is copied into E10?
Copying D9 into E10 we move 1 column and 1 row. The formula in cell D9 is ‘=$A5’, the $ in front of the A means that the calculation is not applied to column A. The row however has no dollar sign therefore the calculation is applied to the row.
If you copy D9 into E10 then the result is:
$Aà $A
5+1à 6
The result therefore is =$A6 and the value displayed in the cell A6 is Mary.