Spreadsheets in an information system

Spreadsheets manipulate numerical data. They can be used to obtain information about digital data and binary systems.

Syllabus outcomes

P1.1 Describes the nature of information processes and information technology.

P5.1 Selects and ethically uses computer based and non-computer based resources and tools to process information

P6.2 Generates ideas, considers alternatives and develops solution for defined needs.

Instructions

Formulas are the basis for calculating answers from values entered in a spreadsheet. They have a symbol to show they are a formula followed by a calculation to be carried out and then by the cells to be calculated. In this example the symbol is =

  1. Open a new spreadsheet. Save it to your home drive as binary code
  1. Click in cell A1. type: Binary coded data
  1. Format the cell contents as bold and increase the size by two increments.
  1. Click in cell A3. Type: 1
  1. Click in cell A4. Type: 0
  1. TypeON in cell B3.
  1. Type OFF in cell B4
  1. In cell A6, type: Place values in the binary system.
  1. Format the cell as Bold and increment the size by one.
  1. In cell A7, type: Place values go from right to left (the most significant digit to the least significant digit).
  1. Use the range A9:A11 to add the row labels Power, Base, Place value.
  1. in the block of cells B9:J10 add the following values:

876543210

222222222

Formulas are the most valuable type of data in a spreadsheet because they allow the calculations which are the basis of the spreadsheet itself. These calculations are always carried out in the following order:

data in brackets ()

multiplication * or division /

addition + or subtraction –

formulas are not usually shown in the cell, but can be seen in the entry bar when the cell is active. In our sample spreadsheet the structures we will use to write a formula are:

= the formula sign

* the mathematical operator

2 the value (base value of the binary system)

  1. in the cell B11 enter the following formula using cell references

=b10^b9

This is the base value (2) multiplied by the power (8) which gives a return of 28.

  1. Press Enter or Return or use the ‘ on the Entry bas to see the result of the calculation.

The formula in a cell is relative to its position of the cell. So in the following example, if the formula =B3*A3 is copied from C3 to C4 the formula will change to =B4*A4, that is, the formula will change to show its new location.

  1. To use relative referencing in your spreadsheet:
  1. Highlight cells C11:J11
  2. Choose Fill Rightfrom the pull-down menus
  1. Change the width of columns A to J to auto fit the data entries in these columns (use the Format menu).
  1. Create a 3D column chart or pictogram of the values in cells B11:J11 using the label from A11.
  1. Label the X axis: BinaryCode
  2. Label the Y axis: Place values
  3. Title the chart: Binary values
  4. Save the chart as: Binary values
  1. In cell A14, type the label: Calculating the decimal value of bytes in binary code. Format A14 as bold.
  1. In cell K16, type the label: Decimal value.
  1. In the range B17:J17, type the values

00110110

In the following part of this exercise you will be using two advanced spreadsheet techniques:

  1. Absolute references are constants which always refer to the same cell. In many spreadsheet program an absolute reference is given a @ sign before the column and/ or row reference, for example @A@2. Other programs use a dollar sign before the column and the row coordinates eg $A$2. Absolute references can refer to either the column or the row reference as well as to both.
  2. Functions are a quicker way of writing formulas. Functions name the type of calculation and cell ranges to be calculated. This is very valuable when there are a lot of cells to be calculated and the calculation is difficult. Using a function and a cell range, a formula could be entered using the following steps.
  1. The function you will be using is the IF function. It may be expresses as

=IF(condition, true, false) in Microsoft Excel

OR

=IF(condition; true; false) in other programs

  1. in cell B18 type:

EXCEL (USERS,)

=IF(B17=0,0,B11)

  1. Use Fill Right to complete the cell references from B18:J18
  1. Insert a row between 17 and 18. note that the relative references change in the functions B19:J19.
  1. insert a label Status in cell A18
  1. Enter the formulas for the cell B18:J18 as =IF(B17=0,$B$4,$B$3) and fill right to J18.
  1. Use Format/Column/Autofit to ensure that all data is visible.
  1. Insert a function in K19 as =SUM(B19:J19) using the autosum icon (S) from the menu bar itif is available.
  1. Create a 3D column chart of the values in cells B17:J17 using the label from A11.
  1. Label the X axis: Digital value.
  2. Remove labels from the Y axis if they exist.
  3. Title the chart: Digital data
  4. Save the chart as Digital data.
  1. Use the Draw tools to add labels to the chart.
  1. Most significant digit (the digit to the right)
  2. Leaste significant digit (the digit to the left)
  3. Draw a block around the 8 bits and label these as the size of the typical byte.
  1. Save the file.
  1. Follow the above procedures to calculate the decimal value of each of the following binary numbers and any other you choose to add.

Record your results.

01110110

11010110

11101101

01111111

01111100

  1. Answer the following questions using examples from the spreadsheet.
  2. What id meant by the term recalculation?
  3. Explain the concept of the least significant digit.
  4. Describe the difference between a bit and a byte.
  5. Using the charts you created explain:
  6. How binary data is calculated on place values.
  7. How digital data is represented.

This is to be completed as a 1 page word processed document presented on your template.