## Spreadsheets: Collecting and Analysing Data
### What is a Spreadsheet?
- A **spreadsheet** is a computer application for computation, organization, analysis, and storage of data in tabular form.
- It consists of a table of **cells** arranged into **rows** (numbered 1, 2, 3...) and **columns** (lettered A, B, C...).
- Each cell can contain:
- **Text** (e.g., "hello world")
- **Numbers** (e.g., 5)
- **Dates** (e.g., 10-Sep-97)
- **Formulas** (e.g., =5*3) which calculate and display a value based on other cells.
### Cell References and Ranges
- A cell is referred to by its column letter and row number, e.g., **C10**.
- A **range** is a group of contiguous cells, e.g., **A1:A10** refers to the first ten cells in column A.
- In multi-sheet workbooks, include the sheet name: **Sheet1!C10**.
### Formulas and Functions
- Formulas start with an equals sign (`=`). Example: `=5*C10` multiplies the value in C10 by 5.
- The power of spreadsheets comes from **chaining formulas** – a formula can refer to other cells, which may themselves contain formulas.
- **Functions** are pre-built formulas for common tasks:
- `SUM(range)` – adds up all numbers in a range.
- `AVERAGE(range)` – calculates the mean of numbers in a range.
- `IF(condition, value_if_true, value_if_false)` – returns different values based on a condition.
### Sorting and Filtering
- **Sorting** arranges data in a specific order (e.g., alphabetical, numerical).
- **Filtering** shows only rows that meet certain criteria, hiding others.
### Charts
- Spreadsheets can display data graphically as **charts** (e.g., bar, line, pie) to make patterns easier to see.
### What-If Analysis
- Because you can change any stored value and instantly see the effect on calculated values, spreadsheets are ideal for **what-if analysis** – exploring many scenarios without manual recalculation.
### Workbooks and Worksheets
- A **workbook** is a file containing one or more **worksheets** (or sheets).
- Worksheets are shown as tabs; each sheet contains its own grid of cells.
### Spreadsheets vs. Databases
- A spreadsheet is essentially one table, while a database is a collection of many tables with relationships.
- Spreadsheets and databases can interoperate: sheets can be imported into databases, and query results can be exported to spreadsheets.
### Key Points to Remember
- Always start a formula with `=`.
- Use cell references (e.g., `B2`) instead of typing numbers directly to make formulas dynamic.
- Use functions to save time and reduce errors.
- Sort and filter to organise data.
- Use charts to visualise data.
- Use what-if analysis to test different scenarios.
- Spreadsheet
- A computer application for computation, organization, analysis and storage of data in tabular form.
- Cell
- A single box in a spreadsheet where data or formulas can be entered, identified by its column and row (e.g., C10).
- Range
- A group of contiguous cells, e.g., A1:A10.
- Formula
- An expression that calculates a value, starting with an equals sign (=), e.g., =5*C10.
- Function
- A pre-built formula for common operations, e.g., SUM, AVERAGE, IF.
- SUM
- A function that adds up all numbers within a range.
- AVERAGE
- A function that calculates the mean of numbers in a range.
- IF
- A conditional function that returns one value if a condition is true and another if false.
- Sorting
- Arranging data in a specific order, such as alphabetical or numerical.
- Filtering
- Displaying only rows that meet certain criteria, hiding others.
- Chart
- A graphical representation of data, such as a bar chart or line graph.
- What-if analysis
- Changing stored values to observe effects on calculated values, allowing rapid investigation of many scenarios.
- Workbook
- A file containing one or more worksheets.
- Worksheet
- A single sheet within a workbook, containing a grid of cells.
- Cell reference
- The address of a cell, using column letter and row number, e.g., C10.
- A1 notation
- A system of cell references using column letters and row numbers, introduced in VisiCalc.