ENGINEERING COMPUTING ENG1602

1
ENGINEERING COMPUTING ENG1602
BACHELOR of ENGINEERING
Week 1 Lecture 2 An Introduction to
Spreadsheets Microsoft EXCEL
2
SPREADSHEETS - Introduction 1.

• A Computer program called a SPREADSHEET is an
  applications Program for which the User supplies
  data and instructions in a simple way to quickly
  produce a desired Numerical and Graphical
  Output.
• It is not a new language in which to write
  programs, but a simple TOOL to be used for quick
  and flexible solutions to a wide variety of
  problems.

3
SPREADSHEETS - Introduction 2.

• However modern spreadsheets usually provide a
  MACRO Language which allows a learning and
  programming facility to automate some spreadsheet
  processes.
• EXCEL has a teach/learn facility, and an
  interface for programming in Visual Basic. We may
  look at some of these facilities later.
• Initially Spreadsheets were applied to business
  and accounting applications, where columns of
  figures needed processing, and simple bar or line
  graphs were required.

4
SPREADSHEETS - Introduction 3.

• Spreadsheets are particularly useful for design
  calculations and trying WHAT IF situations
• that is,changing the various variables
  associated with an application and seeing quickly
  the effects or outcomes of the changes
• Modern spreadsheets may also be useful for
  scientific and engineering calculations

5
SPREADSHEETS - Introduction 4.

• Several Spreadsheet programs exist, starting
  from Visicalc on an Apple //e Computer through
  Lotus1-2-3, Borland Quattro Pro and Microsoft
  EXCEL on PCs

6
SPREADSHEETS - Starting Up

• Select the EXCEL Icon with the Mouse and Double
  Click to initiate Execution (or find it from the
  start menu)
• An Empty Spreadsheet Should Appear on the Screen

7
SPREADSHEET Basics - 1

• Spreadsheets consist of a number of cells - into
  which you add data and formulae
• Each Cell has a Unique address- specified by a
  Letter (for Column) and a Number (for Row)
• Cells may also be given a Name if you desire

8
EXCEL - Cell Capacity Reference
A ........ Z AA........AZ .....................
IA ...... IV
1
Cell A1
256 Columns
Cell Count 256 x 16384
4,194,304
16384 Rows
Cell IV 16384
9
Viewing The Spreadsheet

• Only a small portion of the complete spreadsheet
  may be seen on the screen at any one time.
• The current window on the Spreadsheet may be
  moved or scrolled over the entire spreadsheet by
  using
• the arrow keys or
• the mouse and the scroll bars.
• Any chosen cell may be selected by pointing to it
  with the mouse and left clicking - this enables
  the contents to be entered or edited.

10
Spreadsheet TABS

• In addition the TABS along the bottom of the
  screen enable you to select any one of 16 similar
  spreadsheets that may be set up within the same
  main Spreadsheet File.
• These sheets may be independent of each other, or
  may contain references to data from other sheets
  in the same main spreadsheet file.
• The internal sheets are accessed by pointing to
  the appropriate TAB with the mouse, and left
  clicking.

11
Cell Contents - 1.

• Descriptive TEXT Strings (or Labels)
• You can alter the alignment using the alignment
  buttons at the top
• left align
• centre align
• right align

12
Cell Contents - 2.

• Other interesting effects may be achieved by
  
• Selecting an appropriate ICON from the TOOLBAR
  or
• Selecting FORMAT from the Menu Bar followed by
  CELLS and then one of the TABS Alignment, Font ,
  Border or patterns.

13
Cell Contents - 3.

• NUMERIC Constants
• Entered as they appear or
• Entered in Floating Point Form.
• Example 1000 or 1.0E3
• Spreadsheet Stores them as a numeric form and
  they may be used later in numeric calculations.

14
Cell Contents - 4.

• If the standard column width is not sufficient,
  then the numeric value may not display correctly
  - instead will be displayed.
• This may be rectified by
• Changing the Column width by selecting the column
  boundary at the top in the Column Labels region
  and dragging it out to the width is sufficient,
  or
• Selecting the Cell or Range of Cells and
  selecting FORMAT CELLS NUMBER via the Menu Bar
  then selecting category and set appropriately for
  the number of decimal places etc. required.

15
Cell Contents - 5.

• Formulae
• Must start formula entry with an equals to
  ensure that the cell entry is interpreted as a
  formula.
• The formula can be constructed in the Formula Bar
  , and when completed is stored in the current
  cell in the spreadsheet. Can edit in cell or
  formula bar.
• Expressions may be constructed from a sequence of
  Constants, Cell References, Arithmetic Operators,
  Logical Operators and pre-defined Spreadsheet
  Functions.

16
Spreadsheet Arithmetic Operators

• Exponenation - Example enter A34 to raise
  the contents of cell A3 to the fourth power.
• Multiply
• / Divide
• Add
• - Subtract
• - Negation Example -A3 will change the sign of
  the number in cell A3 before using it in a
  formula.

17
Order of Arithmetic Operations
18
Arithmetic Details

• Parenthesis allow nesting of formulae, the inner
  most pair being evaluated first, and then
  continuing working outwards.
• Multiplication is never implied, and always
  requires a operator even when using
  brackets.
• Example 9.81(A1-A3)
  9.81(A1-A3) Wrong Correct

19
Evaluating Equations with Parentheses

• ( P4 Q4 / ( A4 - B4 )) / ( X4 Y4 - Z4)
• let Wi represent intermediate values
• ( P4 Q4 / W1 ) / ( X4 Y4 - Z4 )
• ( P4 W2 ) / ( X4 Y4 - Z4 )
• ( W3 ) / ( W4 - Z4 )
• W3 / W5
• W6 Complete

20
Redundant Parenthesis

• Redundant sets of parenthesis ( i.e. those not
  required to alter operator precedence, or for
  changing the result of a computation) may be used
  to effect clarity and under standing to otherwise
  complicated formulae.

21
Constructing a Formula -1.

• Select a cell by pointing to it with mouse and
  left clicking, or use arrow keys to move the cell
  box to the desired cell.
• To begin the Formula Type
• Continue the formula by typing the remainder of
  the entry.
• Notice that the formula appears both in the
  Formula Bar and the Cell as it is being typed.

22
Constructing a Formula -2.

• If a mistake is made, then use the mouse to
  highlight it and re-type the correction in the
  Formula Bar.
• When Complete, you can
• press Enter or
• Select the Tick in the Formula Bar.
• The Cross in the Formula Bar Cancels the current
  entry .
• The f(x) allows an automatic paste of a
  Spreadsheet function.

23
Constructing a Formula -2.

• If there is a syntax error in the formula you
  will get an error diagnostic on the screen and
  will have to Edit and correct the error and
  re-enter the formula.
• When a new formula is entered, or an existing
  formula changed and re-entered, the contents of
  the cell will be re-calculated and displayed.
• if another cell is changed, then all cells that
  use it in the calculation their values will be
  automatically recalculated and the new value
  displayed.

24
Constructing a Formula -3.

• Note that once entered the cell does not display
  the formula but the value it currently evaluates
  to.
• The only way of viewing the formula is to select
  the cell, which will display the formula in the
  Formula Bar.
• An existing Entry may be edited in the Formula
  Bar similarly to the input procedure by selecting
  the cell.
• This displays the current entry in the formula
  bar where it may be edited and re-entered.