DSC 2006 title - 1

1
Spreadsheet Techniques for Data ProcessingPart
1 - Basics

• Prepared by Dr David Conochie

2
What is a Spreadsheet?

• Data is arranged in 2D or 3D matrix of cells
• (rows x columns x worksheets)
• Each cell may be of one of the following 4 types
• A value (numeric data)
• A formula ( defines the value of the cell in
  terms of other cells and constants. The cell
  contains the formula and displays the current
  value)
• A label (text data or blank)
• A hyperlink
• Each cell may have additional properties
• Formatting of font, background and border
• A comment attached
• Protection
• A variable name associated with the cell

3
Formulae (i)

• Use to start the formula, then type in cell
  reference or click on cell.
• ((C12) (J17)) 29.3
• Use operators - / , before adding next
  cell reference
• ((C12) (J17) 29.3
• Use signs to make cell references absolute
• Use functions
• sum(c1c12, c19c27)
• a colon is used to define a range between and
  including two cells
• a comma is used to separate ranges in the
  summation.
• ranges can be typed in, or drag the cursor across
  a range
• select multiple ranges by holding down the cntrl
  key.
• complete the function with a right bracket.

4
Formulae (ii)

• Exponents and Logarithms
• cell reference 2 is the square of the
  value
• log(cell reference, base) is log base 10 as
  default
• ln (cell reference) is natural log
• Row and Column Insertion
• Insert rows within a range and the formula will
  include your new data.
• Insert row at the end of a range and the formula
  will not change
• Be very careful and use audit tools!

5
Layout
Adopt a tidy layout
Data Input Table
Title
Physical data and constants
Table with column headers
6
Cell Formula and Auditing Tools
E4Gas_ConstantE7/A14
Precedent Dependent Erase Auditing
Toolbar
7
Example of a Finished Spreadsheet
8
How do spreadsheets work?

• A solver automatically works through the
  spreadsheet row by row, cell by cell, then sheet
  by sheet
• Each time you change a value, the spreadsheet
  recalculates.
• Cell references in formulae usually take input
  from earlier in the sheet but there is usually no
  constraint restricting formulae from where input
  data is taken.
• Recalculation is repeated in an iterative manner
  until cells do not change more than a
  predetermined amount (say 1 in 1000) or a maximum
  number of iterations has been reached.
• Cells cannot refer to each other in a circular
  reference Excel will tell you immediately if
  you try to do this.
• During spreadsheet construction Tools \ Options \
  Calculation can be used to set manual
  recalculation if spreadsheets are big and slow
  (seldom used and a big trap).

9
Data Processing

• Spreadsheets are ideal for processing tabulated
  data.
• Presentation of data in formatted tables
• Calculation of averages, totals, standard
  deviation
• Charting data for presentation
• Single parameter data (height of students in
  class) is presented a bar chart.
• Correlated data (fuel consumption, car speed) is
  presented as an X-Y chart.
• Excel offers the bar chart as the default, but
  engineers and scientists mostly use X-Y charts.

10
Charting Data

• Insert , Chart, X-Y (Scatter), then select the
  style
• Points only
• Smoothed line (beware!)
• Lines connecting points
• Insert chart as a new sheet

11
Assignment EM1 is available on Eng Mgt website
Prepare a quality chart starting from a given
data set.
12
Spreadsheet Techniques for Data ProcessingPart
2 - Advanced
Engineering and Management 2006

• Prepared by Dr David Conochie

13
Logic Functions

• You can build logic into your spreadsheet
  functions but use them sparingly.
• Logic functions are typically used to
• avoid !DIV0 errors
• switch on error flags if data is outside a range
• to switch on sections of formulae if options are
  selected.
• If you have much logic in your application, use
  the Visual Basic add-in in Excel.

14
Macros and Visual Basic

• If you wish to design a spreadsheet to process
  data on numerous occasions, you can use a macro.
• Example build a spreadsheet to process data from
  the strain gauges on the cantilever beam and
  produce a chart.
• Work out, or have a trial of the sequence you
  wish to reproduce.
• Switch on tools, macro, record new macro and give
  your macro a name.
• Go through the sequence, then stop the macro.
• Use alt f11 to switch to visual basic.
• Under the heading modules you will find the
  visual basic commands for your macro.
• It is possible to edit these macros, and to write
  them in VB without using the record function.

15
Visual Basic Function Example
Function Enthalpy_Solid_Iron(Temp As Double) '
Function for the heat capacity of solid iron '
Inputs... ' Temp Temperature
Kelvins ' Outputs... ' Enthalpy_solid_iron
Ht-H298 kJ/kg Enthalpy_Solid_Iron
(8.873 (Temp - 298) 0.5 0.001474 (Temp
2 - 298 2) _ - 0 (1 / Temp - 1 / 298)
1 / 3 0 (Temp 3 - 298 3) _ - 56.92
2 (Temp 0.5 - 298 0.5)) _ 4.1868 /
55.85 If Temp gt 1058 Then
Enthalpy_Solid_Iron Enthalpy_Solid_Iron 1000
1.22 4.1868 / 55.85 Else End If
If Temp gt 1187 Then Enthalpy_Solid_Iron
Enthalpy_Solid_Iron 1000 0.16 4.1868 /
55.85 Else End If End Function
16
Visual Basic Spreadsheet Data Transfer
VB Variable Sheet Reference Cell Reference
' Bring in unit costs into model.
Electricity_unit_cost Sheets("Unit_costs"
).Cells(24, 4) Electrode_unit_cost
Sheets("Unit_costs").Cells(25, 4)
Lime_flux_unit_cost Sheets("Unit_costs").Cells(2
6, 4) Coal_unit_cost Sheets("Unit_costs"
).Cells(27, 4) Natural_gas_unit_cost
Sheets("Unit_costs").Cells(31, 4) / 1000
Metallurgical_quality_unit_penalty
Sheets("Unit_costs").Cells(21, 4) 1000 'rate
/t