Difference between revisions of "MIT The World of spreadsheets and simple graphing"

Revision as of 07:59, 21 October 2015

Spreadsheets

give a simple explanation

• As explained by Wikipedia
• Best application for data gathering/collection
• Easiest entry into "application development" --you don't even have to be a developer/programmer

The Good, The Bad, and the Ugly

The Good

• Spreadsheets is a low entry point for an application framework, in the same way that WordPress is a website framework
  • You don't have to be a developer/programmer

The Bad

• Spreadsheets are used to make applications
  • You don't have to be a developer/programmer
  • Complex spreadsheets suffer from errors; become difficult to troubleshoot

The Ugly

• On the Numerical Accuracy of Spreadsheets

The World of Spreadsheets

• Gnumeric most powerful spreadsheet program
  • Single editor environment
  • Size Limit: Max rows: 16,777,216; Max Columns: 16,384; Max Cells: 274,877,906,944
  • Project doesn't provide Windows binaries; lag for latest version to be ported to Windows or Macintosh
• Libre Calc
  • Single editor environment
  • Size Limit: each sheet can have a maximum of 1,048,576 rows and a maximum of 1024 columns.
    • Each document has a size limit of 4 GB
• Google Sheets
  • Multi-editor environment
  • Size Limit Up to 2 million cells
• Microsoft Excel
  • Single editor environment
  • x32 bit vs. x64 bit
  • Size Limit: 1,048,576 rows by 16,384 columns
    • x32 bit: Each document limited to 2GB
    • x64 bit: Each document limited by system physical memory
• Microsoft Excel Online in Office 365
  • Multi-editor environment --Maximum of 5 concurrent editors
  • Size Limit: 250 MB

Tidy Data

Tidy Data by Hadley Wickham

1. Each variable forms a column,
2. Each observation forms a row,
3. Each table (or file) stores data about one class of experimental unit.

Basics

• relative vs. absolute cell reference
• dragging cells to auto-fill

Tips & Tricks

Keyboard shortcuts (Navigation)

• Excel
• Calc

Formulas

• With complex formulas, work backwards, or right to left:
  • LEFT(ROUND(SUM(A:A))/COUNT(A:A),3)

Graphing (Charts)

The Top Ten Worst Graphs

Learning from others mistakes
The Top Ten Worst Graphs

Anatomy of a Graph/Chart

• Graph/Chart type
  • Column
  • Bar
  • Pie
  • Area
  • Line
  • XY Scatter
  • Bubble
  • Net
  • Boxplot
  • Mixed
• Chart/Graph Title
  • Chart/Graph Subtitle
• Chart/Graph X-Axis Label
• Chart/Graph Y-Axis Label
• Chart/Graph Legend

Tips & Tricks

Data Manipulation

• Swap X Y or switch row/column
  • "Select Data..." from Chart filter

Color

• Solarized
• Color Brewer
• Color Blind considerations

Visualization Data

TED TALK's

• David McCandless: The beauty of data visualization
• Hans Rosling
  • "let my dataset change your mindset"
• Manuel Lima: A visual history of human knowledge

Visualization

• Information Is Beautiful
  • Bubble Race visualization
• Gapminder --requires Adobe Flash plugin :-(

Shout out to DuckDuckGo search engine for bursting my filter bubble