The primary purpose of a spreadsheet is to store, analyse and process numeric data using mathematical techniques. Traditional paper-based spreadsheets were used to maintain the financial records necessary to operate a business. These paper spreadsheets required users to manually calculate totals and other statistics every time an entry was changed or a new entry was added; computer-based spreadsheets automate this process. They must be able to accomplish mathematical calculations quickly and results must accurately reflect the current data, that is, any changes to the data must be reflected in all results that use that data.
The above requirements mean that the organisation of data within spreadsheets is fundamentally different to the organisation of data within most other applications.
Within both paper and computer-based spreadsheets, the input, data, processing and output are integrated within a single form or screen. Even a single cell within a spreadsheet is the basis for multiple information processes. For example, in a spreadsheet application a cell can be used to collect formula data, the data in this cell is then used to analyse other data and also display the result of the analysis. Most other applications separate collection, analysis and displaying into distinct processes.
Spreadsheets organise numeric and other data into an arrangement of columns and rows; columns are generally labelled alphabetically and rows labelled numerically.
The intersection of a column and a row is called a cell. For example, the cell address B7 refers to the cell at the intersection of column B and row 7. Each cell contains a particular data item; the method used to represent each of these data items changes based on the type of data within the cell. In general, cells contain numeric, text or formula data, each being represented differently.
Consider the sample Microsoft Excel spreadsheet shown in Fig 4.24 below. The cells in row 1 contain text data that is used as headings for the data contained in each column. In spreadsheet terminology, cells containing text data are known as labels. A label identifies and gives meaning to something, in most cases cells containing text do just that, they identify and give meaning to the numeric data. A range of cells is specified using the address of the top left hand cell, a colon, and the address of the
GROUP TASK Activity
Examine the available features for controlling text spacing within a word processor and then within a desktop publishing application. Create a table comparing the features found in each application.
bottom right hand cell, for example in Fig 4.24 the cells containing all the surname and first name data are within the range A2:B13.
The formula within cell J15 calculates the average of all the IPT marks, that is the average of all the values in the cells J2:J13. The formula =AVERAGE(J2:J13) is the data in cell J15, the result of evaluating this formula, namely 64.4, is displayed in the cell. The spreadsheet application, in this case Excel, knows that the data in J15 is a formula as it commences with an equal sign. Spreadsheets determine formula, numeric and text data automatically as data is entered. If the data commences with an equals sign it is presumed to be a formula, if it
contains only combinations of the characters 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e then it is presumed to be a numeric, all other data is presumed to be text. For example, 2*3 is treated as text and not evaluated, whereas =2*3 is treated as a formula and is therefore evaluated.
In most spreadsheet applications, including Excel, numeric data and formula results are analysed and represented using the double precision floating-point system. This means that individual values will be accurate to approximately 15 significant figures; remember floating-point is not completely accurate, so it is possible that repeated calculations will magnify errors resulting in less than 15 figure accuracy. The sample spreadsheet in Fig 4.25 illustrates how quickly such errors can propagate.
Each cell in the range C2:C26 contains a formula
Fig 4.25
Repeated calculations can significantly magnify
floating-point precision errors.
Fig 4.24
Sample spreadsheet created in Microsoft’s Excel spreadsheet application.
that multiplies 11 by the value in the cell above and then subtracts 2. Since C1 contains the value 0.2, one would expect each cell in the range C2:C26 to calculate 11*0.2-2 and display the result 0.2; in Fig 4.25 this is clearly not the case.
The ability to copy formulas and have their cell references change to reflect their new location is a powerful feature present in all spreadsheet applications. In Fig 4.25 the formula in cell C2, namely =11*C1-2, was copied, or filled down, into cells C3 to C26. As a consequence the formula data in cell C3 is =11*C2-2; the reference to C1 in the original has changed to C2. How has this happened? The reference to C1 in the original formula actually refers to the cell directly above cell C2, C1 is an example of a relative reference. When a formula is copied to a new location, all relative references will point to cells relative to the new cell’s location. If this is not desirable then an absolute reference should be used in the original formula. Absolute references are specified in most spreadsheets using dollar signs. For example, in a formula the reference $C$1 always refers to cell C1, $C1 always refers to column C but allows the row to change relative to the new position,
and C$1 always points to row 1 but allows the column to change relative to the new position.
Numeric data, including the results from formulas, can be formatted in various ways, for example as currency, dates, percentages, or fractions. Altering the format of the data does not alter the underlying raw data;
rather it organises the data in preparation for display. This makes the data understandable for humans. Fig 4.26 shows some of the various number formats included in Excel; it is also possible to define custom formats to suit specific requirements.
In summary, spreadsheets organise data according to the following:
• Data is arranged in columns and rows; the intersection of a column and row determines a cell.
• Any cell can hold and represent text, numeric or formula data.
• Formulas refer to other cells using their cell address. Such references can be relative or absolute references.
• Numeric data, and the results from formulas, are represented using the double precision floating-point system.
• When formatting is applied to cells it has no effect on the actual data held within the cells.
• The organisation of data in a spreadsheet, in particular formulas within cells, allow collecting, analysing and displaying to be integrated processes.
Fig 4.26
Numeric cell formats included in Excel.
GROUP TASK Activity
Reproduce the spreadsheet shown above in Fig 4.25. Observe the effect of changing the format of the cells to alter the number of decimal places displayed. Explain your observations in terms of data organisation.
Consider the following:
The Excel spreadsheet in Fig 4.27 is used to determine the number of surnames commencing with each letter of the alphabet. For example, there are 11 surnames that start with the letter A. Currently the spreadsheet contains a total of 234 names sorted into alphabetical order.
The following formulas have been used within the spreadsheet:
• Cell F2 contains the formula =LEFT(A2,1)
• Cell H3 contains the formula =H2+1
• Cell I2 contains the formula =CHAR(H2)
• Cell J2 contains the formula =COUNTIF(F2:F235,J2)
• Cell J28 contains the formula =SUM(J2:J27)
Fig 4.27
Spreadsheet analysing the frequency of surnames commencing with each letter of the alphabet.
GROUP TASK Activity
Reproduce the spreadsheet in Fig 4.27 using your own data, and based on the above screen shot and information.
GROUP TASK Discussion
Classify each cell on the spreadsheet as containing text, numeric or formula data.
GROUP TASK Discussion
Analyse the formulas used and determine which cell references could (or should) be absolute and which should be relative references.
SET 4B
1. Which process alters the precise distance between pairs of characters?
(A) tracking (B) kerning (C) leading (D) font size
2. An animation, for use on a web page, that has a small number of frames contained within a small screen area would most likely be stored as a(n):
(A) animated GIF file.
(B) MPEG file.
(C) flash file.
(D) quick time file.
3. The dictionary, maintained by a flash player, is used to:
(A) hold the meaning of each tag used within a flash file.
(B) store a sequential list of all the definition tags in the current flash file.
(C) create each frame of the animation prior to its display.
(D) maintain a description of each character that can be used in the animation.
4. Responding to user actions is possible in:
(A) animated GIFs.
(B) MPEG files.
(C) flash files.
(D) All of the above.
5. For a video file to support streaming:
(A) the data must all be received prior to the first frame being displayed.
(B) all the data for each complete frame must be received in the order the frames are to be displayed.
(C) the video data should be compressed.
(D) each frame needs to be stored as an ordered sequence of independent bitmaps.
6. Most video editing software:
(A) creates the final video data as editing takes place.
(B) organises the ordering and transitions between video clips.
(C) produce the final video data after editing has been completed.
(D) Both (B) and (C).
7. Font tables are used by word processors:
(A) to make understanding the organisation of the data difficult.
(B) to describe the precise shape of each character within the text.
(C) because they save specifying the detail of each font every time it is used.
(D) to specify the font used for each particular block of text.
8. Desktop publishing applications use the CMYK system for representing colour because:
(A) there are less colours available compared to the RGB system.
(B) CMYK represents the ink colours used during the four colour printing process.
(C) the resulting files are smaller in size.
(D) computer monitors reproduce CMYK colours more accurately.
9. Formatting a cell in a spreadsheet as a date will:
(A) alter the underlying data.
(B) not alter the underlying data.
(C) only change the way the data is displayed.
(D) Both (B) and (C).
10. If the formula =B1*$D$2 is copied from cell A2 to cell C3 then C3 would contain the formula:
(A) =B1*$F$3 (B) =D2*$F$3 (C) =D2*$D$2 (D) =B1*$D$2
11. Compare the organisation of data in a flash file with that in a video clip collected using a digital video camera.
12. List and describe the essential differences between the organisation of data in a word processor and a desktop publishing application.
13. “Spreadsheets integrate many information processes, including the organising process.” Explain how the organisation of spreadsheet data facilitates this integration of processes.
14. Define each of the following desktop publishing terms: four-colour process, spot colour, colour separation, kerning, leading and tracking.
15. Create a spreadsheet that contains names and marks out of 100 for an assessment task. Develop a formula to convert each mark to a performance band. Band 6 for 90 and above, band 5 from 80-89, band 4 from 70-79, band 3 for 60-69, band 2 for 50-59 and band 1 for marks less than 50.
Create a table to determine the number of students in each performance band.