Data and Spreadsheet

1
Data and Spreadsheet
2
Data and Spreadsheets

• What are data?
• What are statistics?
• What are spreadsheets?
• How can you analyze data with spreadsheets?

3
Data

• Data are pieces of information that can define
  subjects
• Data can be numbers, words, descriptions
• The word data is PLURAL, datum is singular
• Example of Data about George
• Age 57.2 (years)
• 43rd President
• Male
• Favorite letter W
• Favorite words Bring them on.

4
Types of Data

• Numbers two types
• Real s rational numbers 57 years
• Integers whole numbers 170 lbs
• Letters called characters in programming
• W is a character
• Words called strings in programming
• Bring them on is a strings, can be individual
  words or phrases
• Other Image, Sound

5
Statistics and Data

• Test Scores
• Jeff 88
• Mollie 92
• Marcie 88
• Dave 47
• Karim 99
• Willoughby 6
• Benjamin 0

• What statistics can you calculate to describe
  these data?
• Try to think of four things to describe the data
• stop

6
Statistics

• Statistics are derived from the data
• Statistics are descriptions of data
• Statistics are meant to simplify the data
• Statistics can be misleading

7
Typical Statistics

• Sample Size - number of individuals measured n
• Sum S
• Average or Mean S/n
• Median
• Value of 50th percentile, half of values fall
  above, half below
• Maximum, Minimum, Range (Max-Min)
• Mode - most common value
• Standard deviation
• Variance (SD2)

8
Analyze these data...

• Mean, max, min, range, median, mode
• 18
• 33
• 4
• 47
• 49
• 38
• 29
• 4
• 55

• sample size (n)
• Mode
• Sum S
• meanaverageS/n
• median halfway
• Variance S(x-mean)2/n

9
Calculators Compared with Spreadsheets
Calculators Spreadsheets
Single Calculation Many Interrelated Calculations at Once
Built-in Math Operators Built-in and customized Math Operation
Complex Calculations One Step at a Time Change a Value Repeat the Calculations you Defined
10
Spreadsheets

• Spreadsheets are tables
• Spreadsheets allow calculations and manipulations
  of data
• Calculations mean, standard deviation
• Manipulations sort,
• Visualizations plot, pie chart

11
Spreadsheet Design Questions

• What numbers are you starting with?
• What numbers do you need to calculate?
• What are the relationships among the numbers to
  perform these calculations?
• What text do you need to make the spreadsheet
  clear to yourself and other people?

12
Make a data table

• Fly 1, length 13.4 mm, velocity 27 Kph, age 21
  days
• Fly 2, length 9.4 mm, velocity 0 Kph, age 220
  days
• Fly 3, length 9.3 mm, velocity 44 Kph, age 1 days
• Fly 4, length 13.4 mm, velocity 17 Kph, age 32
  days
• Fly 5, length 17.4 mm, velocity 33 Kph, age 11
  days
• How many columns?
• How many rows?
• s go down or across?

13
Data Table
Fly Length Velocity Age
1
2
3
4
5
14
Microsoft Excel

• Typical spreadsheet program
• VisiCalc is original commercial spreadsheet
• Lotus 1-2-3 took over in the 1980s.
• Has similar controls to MS Word
• Now allows graphing (charts)
• Excel tables and graphs can be copied into MS Word

15
Excel Basic

• Data are stored in cells.
• Cells are name by Letters and numbers e.g. A12.
  This is also called cell reference.
• Cells can hold number, text, image, and most
  important thing Formula.
• Formulas are started with a sign. It can be
  any mathematical operations.

16
Relative Cell Reference
17
Absolute Cell reference
18
Fridays Assignment

• We will work with Microsoft Excel to analyze some
  data
• Groups of two will submit one finished
  spreadsheet for the assignment