Spreadsheet Topics

1
Spreadsheet Topics 1(Assignment No. 3)

• Slides to accompany an in-class demo of
  spreadsheet technique
• Cell Formatting, Absolute references, and
  Selection of a minimum value from a row.

2
SpreadsheetsGeneral

• Electronic table with columns and rows
• Columns enumerated by letter, rows by number
• Cell may contain numeric or text information
• Cell may contain literal value, ex. 12.4 or
  Bob
• Cell may contain computed values
• based on formula which may be a function of other
  cells contents
• Examples A12B3, or C13PI() or sum(A1A23)
• Display of Cell contents is controlled by cell
  formatting
• Example January 17, 2003 or 37638.499
• Spreadsheets generally have rich set of functions
  suitable for engineers
• Trig sin(), cos(), tan(), PI()
• Financial NPV(), FV(), IRR()
• Plotting capabilities

3
Cell FormattingGeneral

• The FORMAT/CELL function allows
• Setting Cell appearance
• Background color
• Font type (eg. superscript)
• Font Color
• Centering
• Format numeric value in the cells
• Decimal places
• As date
• Special effects
• Merging cells
• Word wrapping text

4
Centering a Title over Multiple Columns

• A common operation is to center text over
  multiple columns
• Type title into first cell of the row
• Select Title and Columns over which to center
• Use the menu to center the title
• FORMAT / CELLS / ALIGNMENT /
• Horizontal center, Merge Cells

5
Copying formulae

• A fundamental operation in building spreadsheets
  is copying formulae
• Normally copying a formula from one position to
  another changes the references in the formula
  with regard to position
• References in formulae are normal relative to the
  position of the formula in the sheet
• A reference in a formula can be made to reference
  an absolute position by proceeding the row and
  column references by a . Preceeding a column
  and/or row reference with a prevents the cell
  reference from being translated and makes the
  reference absolute.

6
Spreadsheet Functions

• Spreadsheets include many specialized functions.
• Simple examples would be the sum( ) function
  which sums the values of the cells referred to in
  the argument of the function.
• You can access a quick list of functions in
  Excel by selecting INSERT/FUNCTION from the
  menu.
• Examples might be the MMULT() which provides
  array multiplication, PI() which provides the
  value of p and SIN() which provides the sine
  function.
• These may be placed in a cell by typing
  followed by the function.

7
Functions continued

• Example Extract and show the minimum value in a
  row
• use IF, MIN and MAX functions to extract and show
  the minimum value in a row
• Use the following IF function to show the minimum
  value of a row in the row below
• IF(B3MIN(B2B7),B3,)
• Which may be read as if the value in column B,
  row 3 is equal to the minimum in the row B3 to B7
  then show B3, otherwise show blank.
• Use the MAX function to select the single value
  shown in this row.