Spreadsheets

1
Spreadsheets

• First "programming" example in this course.
• You are telling the computer what you want it to
  do, with a series of commands. The commands are
  implied, but are none the less a set of
  instructions.

2
Spreadsheet Basics

• Derived from computerized, real-time responsive
  form of the accounting ledger. Its applications
  have moved far beyond accounting applications.
• Widely used for analysis, presentation and
  plotting of sets of many variables.

3
Spreadsheet Format

• The data is arranged in rows and columns.
• A row is horizontal numbered top to bottom
• A column is vertical identified by letters of
  the alphabet ordered left to right

A B C
1
2
3
4
4
?

• Intersection of a row and a column defines a
  "cell". A cell can hold text, numbers, or things
  calculated using a formula.
• Cells are named by their locations.
• A1 is cell in leftmost column (A) and topmost
  row (1)
• D5 is cell in column D and row 5
• Always give column first, then row
• Addresses are not case sensitive (cell A1 and
  cell a1 refer to the same cell)

5
Spreadsheet Elements

• Data is presented in worksheet windows. These
  windows are bordered by row and column labels
• "Tabs" are used in Excel for multiple worksheets
  in the same file. These can be seen at the
  bottom of the spreadsheet area.

6
Cell Contents

• ??Numbers
• These numbers can have adjustable precision and a
  variety of styles (e.g. Scientific, Dates,
  Currency).
• 3.14159 2.59 1,000,000 24 6.23e-10
• 1200 2/28/2009

7
?Cell Contents

• Text (Labels)
• These text entries can also be formatted in most
  modern spreadsheets.
• Feet Barr ECE 1331 1234

8
Cell Contents

• Formulas are expressions which include numbers,
  operators, defined mathematical and other
  functions, and/or the values obtained from other
  cells.
• In Excel, formulas must begin with an equal sign
• Using formulas provides one of the great powers
  of the spreadsheet. The key to this power is to
  know how to address the cell desired.

9
Mathematical Operators

• Addition () 52
• Subtraction (-) 7-3
• Multiplication () 2A (NOT 2A)
• Division (/) 4/3 (NOT )
• Exponent () 53 (NOT 53)

10
PrecedenceOrder of evaluation when several
operators appear in a single expression

• Parenthesis
• Function evaluation
• Exponentiation
• Multiplication Division (left to right)
• Addition Subtraction (left to right)

11
Precedence Examples
(3(a1))/(d-2)
25(1/3)
12
Intrinsic Functions

• There are many predefined functions in Excel. A
  few examples are
• AVG, SUM, MIN, ABS,SQRT
• COS, SIN, LN, EXP
• IF,ISBLANK,ISTEXT
• Refer to Excel Reference Sheet in Course
  GuideGiven on exams bring to class for quizzes!

13
Cell Addressing in Formulas

• Relative Addressing - the addressed cell is with
  respect to where the cell containing the formula
  is.
• A3B4 entered in cell A4 means add value in
  cell above current cell to value to the right of
  current cell so when copied to cell C5 becomes
  C4D5

14
??.
Cell Addressing in Formulas

• Absolute addressing - the addressed cell is with
  respect to the spreadsheet
• In Excel, precede column and/or row with sign
• B2 means col B is absolute but row 2 is
  relative
• C3 means col C is relative but row 3 is
  absolute
• A1 means both col A row 1 are absolute

15
Example
A1 B1 C1
A2 B2 C2
A3 B3 C3
A1A2A3
C1C2C3
Copy to C4
16
Example
A1 B1 C1
A2 B2 C2
A3 B3 C3
A1A2A3
A1A2C3
Copy to C4
17
Ranges

• A rectangular range of cells can be indicated by
  giving the address of the upper leftmost cell
  followed by a colon followed by the address of
  the lower rightmost cell
• sum(a3a6) is the same as a3a4a5a6
• max(b2d2) is the same as max(b2,c2,d2)
• min(b3c4) is the same as min(b3,b4,c3,c4)

18
Named Cells

• Rather than always referring to a cell by its
  address, you can define a more meaningful name,
  making formulas more readable
• For example, if cell C3 contains the value of the
  cost per unit, and cell D5 contains the number of
  units, then cpunum would be more meaningful
  than C3D5

19
Named Cells

• By default, named cells are always absolute
• The formula cpunum would actually be
• C3D5
• Names are not case sensitive
• Must begin with letter or underscore
• Must not conflict with built-in names

20
Named Cells

• If we are entering a students 33 homework grades
  in cells B2 through B34, then we could enter in
  cell B50, average(B2B34) or, if we name the
  range B2 thru B34 as HW, use the formula
  average(HW)

21
Data Manipulation

• We have a variety of ways of manipulating data in
  a spreadsheet. These methods include
• Data Fill - where the same kinds of data or
  formulas are used to fill up a range of cells
• Sorting - where groups of data are arranged in
  order, usually by row or by column

22
Graphing

• While not originally a spreadsheet function,
  plotting of data has become an important feature
  of all spreadsheet programs Excel calls them
  charts!
• Types include column, line, pie. scatter and
  many, many more
• Note The usual plot of a mathematical function
  is a scatter typeNOT a line!

23
Data Import/Export

• It is possible to bring data into a spreadsheet
  (import) and produce files for use of other
  applications (export).
• Class rolls are now being made available to
  faculty as comma separated value files,
  suitable for import into Excel
• Excel can save a sheet in space delimited text
  form, suitable for use by a C or Fortran or
  Matlab program