spreadsheet

Data and Spreadsheet

Data and Spreadsheets

 What are data?

 What are statistics?

 What are spreadsheets?

 How can you analyze data with

spreadsheets?

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.

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…

Statistics and Data

 Test Scores:  What statistics can you

– Jeff: 88 calculate to describe

– Mollie: 92 these data?

– Marcie: 88

– Dave: 47 – Try to think of four

– Karim: 99 things to describe the

– Willoughby: 6 data

– Benjamin: 0  stop

Statistics

 Statistics are derived from the data

 Statistics are descriptions of data

 Statistics are meant to simplify the data

 Statistics can be misleading

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

2

 Variance (SD )

Analyze these data...

 Mean, max, min,  sample size (n)

range, median, mode  Mode

• 18

• 33

 Sum S

• 4

• 47

 mean=average=S/n

• 49

• 38  median = halfway

• 29  Variance =S(x-mean)2/n

• 4

• 55

Calculators Compared with

Spreadsheets

Calculators Spreadsheets

Single Calculation Many Interrelated

Calculations at Once

Built-in Math Built-in and customized

Operators Math Operation



Complex Calculations: Change a Value: Repeat

One Step at a Time the Calculations you

Defined

Spreadsheets

 Spreadsheets are tables

CostaRica Nicaragua

Rainforest 625,000 3,712,000

Dry Forest 50,000 300,000

Total 675,000 4,012,000



 Spreadsheets allow calculations and

manipulations of data

• Calculations: mean, standard deviation

• Manipulations: sort,

• Visualizations: plot, pie chart

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?

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?

Data Table

Fly # Length Velocity Age



1



2



3



4



5

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

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.

Relative Cell Reference

Absolute Cell reference

Friday’s Assignment

 We will work with Microsoft Excel to

analyze some data

 Groups of two will submit one finished

spreadsheet for the assignment