Spreadsheet

A spreadsheet can be used for storing information, analysing information and performing calculations. It's useful for displaying and analysing survey results.

What is a spreadsheet?

A spreadsheet is software used for storing lists of information, analysing information and performing calculations.

Let’s have a look at a spreadsheet.

This spreadsheet has nine rows and six columns

• A spreadsheet is made up of rows and columns.
• Rows go across the page and are labeled with numbers.
• Columns go down the page and are labeled at the top with letters.

Spreadsheet cells

Each box in the spreadsheet is called a cell. Each cell has its own reference label, which is named by the column and row that intercept to form the cell. Look at the spreadsheet below. The shaded cell is where column B and row 3 meet. This cell’s reference is B3.

B3 is in column B and row 3

Autofill

Autofill is found in most spreadsheet packages. Autofill helps to quickly enter frequently used series of data. Examples include:

• dates
• days of the week
• series of numbers

Autofill works by dragging the bottom right-hand corner of a cell. As you hover the mouse over the corner, a cross will appear. Click and drag either down, or across to fill other cells.

Advantages of using spreadsheets

The great thing about spreadsheets is information can easily be updated:

• prices can be updated
• new items added
• complex calculations can be made

Difference between function and formula

A formula is statement written by the user to be calculated. Formulas can be as simple or as complex as the user wants. A formula can contain values, references to cells, defined names, and functions.

All formulas must start with the equals sign.
Example: =(A1+A2+A3)

A function is a piece of code designed to calculate specific values and is used inside formulas. Functions and to calculate the current time is built into excel. Additional functions can be defined using Visual Basic.
Example: =Sum(A1:A3)

Calculations using simple formulae

Below is a spreadsheet showing the cost of computer equipment from three shops.

A spreadsheet showing the cost of computer equipment from three shops

You might want to find out how much a computer and printer from Shop 1 would cost. To do this, you need to use a simple addition formula.

• price of computer = cell B2
• price of printer = cell D2
• to add these two together, the formula would be B2 + D2

You need to type this in as = B2+D2

All formulae in spreadsheets need to begin with the = sign. This tells the cell to expect a formula.

Other simple formulae you should know are listed in the table below.

Spreadsheet formulae.

Calculation	Symbol	Example
Subtract	-	=A1-A2
Multiply	*	=A1*A2
Divide	/	=A1/A2

Simplifying formulae by using Functions

If you wanted to find the total cost of buying the computer, computer desk and printer from Shop 3, you could use the formula:

=B4+C4+D4

Sum (=B4+C4+D4)

However, typing in every cell you want to add together can be quite time consuming, so you would be better to use the SUM function:

=SUM(B4:D4) (B4:D4) just means all the cells from B4 to D4.

=SUM(B4:D4)

When you need to perform a calculation using several cells next to each other, a quick way to select cells is to:

• Select the first cell you want to add.
• Click on the bottom right-hand corner of the cell.
• Drag across to include all the other cells.

Another function you might need to know is the average function. If you wanted to find out the average price of a computer, you would use:

=AVERAGE(B2:B4)

Input / Output Devices (Modem)

Input / Output Devices

Some devices fulfill both input and output functions for a computer system. They act as an interface for data going in as well as out and sometimes they do this simultaneously. One such device is the modem.

Modem

The modem is used to connect the computer to the telephone line. The telephone network operates using analogue voice signals, while a computer operates on digital signals. The modem is the bridge between the digital and the analog signals, and therefore provides an interface between computer and telephone systems.

The modem converts on and off digital data into an analogue signal by varying, or modulating, the phase and frequency of an electronic wave. On the receiving end of a phone connection, a modem does just the opposite: it demodulates the analogue signals back into the digital code. These two terms MOdulate and DEModulate give the modem its name.

Output Devices

Output Devices: Information for the User

Output devices provide the results after processing, in a suitable form. In many cases this will be in the form of a hard copy (printout), or on screen (soft copy). With the widespread use of electronic mail (e-mail), output could be in the form of an electronic message to another computer.

Display Screens (Monitors)

Today the computer monitor is the most common form of output, which is also referred to as the VDU or Visual Display Unit that is the most prolific form of output.

Pixel is the name given to a picture element and refers to the smallest area of the screen. The entire screen is made of a lot of pixels. The clarity or clearness of a computer screen is measured in terms of resolution. Screens are normally classed as low resolution, medium resolution or high resolution. High-resolution monitors use more pixels than low resolution.

The quality of a computer monitor is based on the following properties:

• The resolution
• The number of possible colors
• The size (normally the monitors come in sizes of 15" and 17")
• Energy consumption and radiation

Different types of display screens:

Standard television set - An ordinary home television can be used for computer output.

Standard computer monitors - These have better resolution than TV monitors. Larger monitors with high resolution are used for specialized applications such as Desktop Publishing and CAD.

Liquid Crystal Displays - These are screens made up of two glass plates with liquid in between.

Printers

There are many types of different printers used for computer output. We will consider 3 types in this section.

• Dot-matrix printers
• Ink-jet printers
• Laser printers

Dot-Matrix Printers

Dot-matrix printers are impact printers, which can transfer print through layers of paper. This means that they are able to print multi-part stationery.

Example:

If you want to print a multi-part sheet where, the white top sheet goes to the customer, the yellow sheet goes to accounts and the blue sheet to the store sheet, then you will need to use a dot-matrix printer. (similar to the blue, yellow and green layers of bills found in shops when they write the bill).

A dot-matrix printer is very cheap and has the lowest running costs of any printer. It has a matrix of small pins in its print head. As the head moves across the paper the correct pins are fired out to hit an inked ribbon and the shape of the letter required is formed. The greater the number of pins, the higher the quality of the print.

Ink-Jet Printers

An ink-jet printer is a non-impact character printer. They are silent in operation, have good quality printing and have become a very popular printer for PCs. The print head of an ink-jet printer consists of nozzles (usually 64). The ink flows through the appropriate nozzle, where it is heated and a bubble forms. This expands and breaks, releasing a very small ink droplet. These dots are much smaller, and there are more of them, than in a dot-matrix printer. These printers produce printouts that are almost comparable to that produced by laser printers and therefore can print high quality text and graphics.

Laser Printers

These are non-impact page printers. A laser beam is used to form an image on a rotating charged metal drum. Laser printers have toner cartridges, which contain a fine powder called toner. The charged image then picks up the toner particles, which are transferred to the paper, which is also charged. Once the image has been transferred, heat and pressure are used to stick it to the paper permanently.

Since they are page printers they are very fast. The speed of a laser printer is typically about 8 ppm (pages per minute). Color laser printers are now available. Laser printers offer both high speed and excellent print quality of text and graphics. Although they are expensive, they have become widespread in many offices where quality printouts are needed because of the many advantages it has.

Graph Plotters

A plotter is a device commonly used in producing printing plans, maps, line diagrams and three-dimensional drawings, which are particularly line diagrams or graphical output on paper.

Graph plotters use pens to produce images and different pens containing different colored inks may be used. Plotters are generally classified as pen plotters or as pen less plotters. Pen less plotters use various kinds of different technologies. At the moment high quality work for publication is done on electrostatic plotters.

Voice Synthesis

Voice synthesis is used when you get the output as sound. This method is usually very useful for handicapped people.

Example:

Visually handicapped people, find the spoken word from a computer invaluable. For instance, each letter as it is typed can be heard on headphones when special software with a word processor is used.

Projector

These are special devices, which can be connected to the computer and used as a substitute to the monitor. These are used when the output has to be shown to a large audience by projecting the computer output on a white screen or wall.

Special Input Devices

Special Input Devices

Bar Code Readers

A bar code is a set of parallel lines of varying thickness, which are alternately black and white, which represents a number. The number represented by the bars is also printed above or below the bar code. Bar codes may be read by a hand held scanner, which is passed over the bar codes. The scanner is attached to a computer terminal or a stationary scanner, which scans the bar code as it is passed over it.
When bar codes are used for products in shops the coded number usually contains:

• Country of origin
• Manufacturer
• Item number for the product

We have to note that the price is not included in the bar code. This is because the prices change often. Instead the price is stored in the computer and when the price is needed it can be retrieved. The expiry date is also not included since it too may change.

Bar codes are used in products in supermarkets, books and magazines in libraries, luggage at airports and warehouse stock control systems. Bar code systems are now at an advanced stage and readers are able to read the bar codes at distances of five meters or more. This fact has increased the number of applications.

Magnetic Ink Character Recognition (MICR)

Magnetic ink characters printed using magnetic ink, are the numbers that you see at the bottom of bank cheques. As the document is passed through the reader the ink is magnetized and the characters are recognized by their strength of magnetism.

Most banks use the MICR to encode the following information from the magnetic ink characters at the bottom of the cheques:

• the cheque number
• the branch number of the bank
• the customer's account number

Optical Character Reader (OCR)

The document is first scanned using a scanner. The software used to scan the document would be an Optical character recognizer. Then the document can be saved in ¬a word processor. i.e. As a MS Word document.

An optical character reader recognizes characters from their shape. Text is input using a scanner and special OCR software. OCR involves scanning the image of a page of text with a scanner, and then using special software, it converts the scanned image into standard ASCII code, which recognizes each of the characters separately, so that they may be altered if needed, using a word processor. Scanners often have OCR software included in their price.

Optical Mark Reader (OMR)

Optical mark readers are able to sense marks made in right positions on a special form. These forms include multiple choice answer sheet marking (MCQ papers), questionnaires and enrolment forms which are data capture forms for OMRs.

Voice Recognition

Using a microphone, human speech is coded into a sequence of electrical signals and the computer searches a set of stored patterns for the sound, which has been the input.

Voice recognition is useful where only a few different commands are required and the hands are busy to type or use the mouse. On some advanced jet fighters the pilot has a small display of some of the instruments. Using one of a number of simple pre-stored voice commands this display can be changed. Relatively few words can be recognised and the error rate is high. The system is not suitable for use in noisy places.

Digitizer

Digitizers are rather like electronic tracing paper and, like paper they come in all sizes from a modest A4 size to a very large AO size. A cursor or pointer is used on a graphics tablet to trace over technical drawings put on the screen using a computer- aided-design package such as AutoCAD.

Smart Cards

A smart card is a plastic card, which has its own processor and memory chips. One card can store about 8000 characters. The holder's identification data is stored in the card.

These cards are used in a variety of applications such as:

• Checking authorization for accessing security sites - The user inserts the smart card into a reader when prompted to enter the password. If the password is correct the user will be granted entry to the particular site. (The user is usually asked to reconfirm the password). The processor inside the smart card can be programmed to self-destruct if the wrong password is entered too many times.

• Telephone cards - The card is inserted into a slot found in the telephone box and the relevant amount will be reduced from the value of the card after the call.

• Credit cards - The card conceals the holder's identification data and the credit limit, which are used in supermarkets and leading shops. It holds the details of transactions made by using the card.

Point-of-Sales (POS) Systems

In a typical POS/cash transaction, a cashier passes the bar-coded product over a scanner or hand-held 'wand'. The computer can store the data for stock control and data analysis. The terminal is usually connected to a central computer, which would be the central database, which reads and records details, looks up the price of the product and displays the price on a lighted panel. It also computes the amount due and prints an itemized receipt.

This method of data is beneficial:

• To the customer - It forms the basis for a computerized checkout system. Customers get a quicker and more accurate service, (no time wasted or mistakes made when entering prices) as well as an itemized receipt.

• To the supermarket - The result is that the supermarket management can get instant or continuous stock checks and adjusting orders precisely to the flow of goods, thus improving efficiency. The cost of adding a bar code to a label is tiny and it saves time.

Input Devices

Input Devices: The user connection

Most of the data comes in hand written forms that cannot be directly input into the computer. These documents need special data preparation devices that translate the source document into a medium that the computer can read, such as a magnetic disk. Input devices are used to input or capture data into a computer system. The ideal input device would be able to get data into a system as accurately as possible in the least amount of time.

There are wide ranges of input devices used today:

Keyboard

The keyboard of all input devices is the oldest and most familiar. Keyboards are devices that contain their own chips. Basically each key in the keyboard acts as a switch, which is switched on when the key is pressed.

The microprocessor scans the keyboard hundreds of times a second to see if a key has been pressed; if it has, a code that denotes which key has been pressed is sent to the processing unit. The CPU translates this code into an ASCII code (the code that computers use to represent characters on the computer keyboard), which is then used by a computer program.

Mouse

A mouse is an input device that usually has one, two or three buttons, which are used to make selections on the screen. It is a pointing device. A mouse translates its movements on the desktop into digital information; this is fed to the computer, which in turn causes the cursor to move on the screen. The cursor is the blinking line on the screen, which indicates that the computer is ready to type. Underneath the mouse is a rotating ball and as the mouse is moved on the desk, and sensors pick up this movement to move the pointer on the screen. (The pointer helps the user point and click on an icon or menu on the screen).

Trackball

What do you get when you turn a computer mouse upside down? A trackball! The ‘mouse' stays still while the user rotates the ball. Trackballs are often seen on laptop computers because it has the advantage of taking up much less space compared with a conventional mouse. It is a pointing device.

Joystick

A joystick is similar to a trackball. Whenever the stick is moved, the pointer moves in a similar direction on the screen. Joysticks are commonly used for games, but you can also see them being used for scanning purposes in hospitals.

Scanner

Scanners are used to scan text or pictures into a computer's memory. Then the scanned image is manipulated in some way before being printed. There are cheap hand held scanners and or flatbed A4 sized ones. Both black and white and color scanners are available.

Touch Screen

A touch screen is a special kind of screen. This is sensitive to touch. A selection is made from usually a menu present on the screen, which is activated by touching part of it. These screens are ideal for use in banks, restaurants and bars, where customers who are not use to keyboards can obtain information about the services offered.

Light Pen

A light pen is a pen-shaped device held in the hand. The presence or absence of light is detected by it. The pen is used to select a particular point on the screen. The screen is refreshed about every 1/50th of a second by a point of light travelling rapidly across it. The pen detects this point of light and the computer can work out by precise timing where the pen is. Light pens need special software to make them work and are mainly used for design work using CAD packages.

Digital Camera

Digital cameras allow the user to transfer the photographs taken, directly into the computer. Previously, if a photograph had to be input into the computer, it had to first scan using a computer scanner and then viewed on the screen. Digital cameras eliminate the requirement for a scanner. No film or photographic paper is needed. The quality of photographs obtained even from low cost digital cameras is quite good. Digital cameras are very efficient to use especially if you want to take photos and edit them later on the computer.

Sensors

For industrial computer applications, such as process control, the computer is required to interact directly with the plant. Since the computer can only communicate with the outside world by using electrical signals, any devices connected to it must be made compatible using analog to digital converters (ADC) and digital to analog converters (DAC).

Sensors are designed to pick up analogue values of the physical variables and feed them into the computer.

Example:

• temperature
• pressure
• intensity of light
• electrical voltages
• electrical currents
• switch positions

Depending on the values received by the computer, it activates other output devices like motors, valves etc.

What is a Computer?

What is a Computer?

A computer is a machine that can be programmed to accept raw data, which is input, process it into meaningful information, which will be output, and store it away in a secondary storage device for safekeeping or later reuse.

Computers work under the control of a stored program, which can be changed.

To function, a computer system requires four main aspects of data handling:

• input
• processing
• output
• storage

Input

Input devices accept data in a form that the computer can use and then send the data to the processing unit.

Processing

The central processing unit (CPU) has the electronic circuitry that manipulates input data into information people want. The CPU executes program instructions.

Output

Output devices show people the processed data in a form that they can use easily.

Storage

Storage consists of secondary storage devices such as magnetic disks, tapes and CD ROMs, which can store data and programs outside the computer itself. These devices supplement memory, since memory can hold data and programs only temporarily.