If you have ever studied Statistics, you may recall the importance of summarizing data and looking at how values are distributed. This tutorial covers a handful of spreadsheet formulas that will allow you to perform basic Statistics functions. Along the way, I use real-life numbers and address why the formulas and functions used are meaningful.
How to Use Basic Math Functions in a Spreadsheet
This tutorial covers how to perform basic math operations and is aimed at beginning-level users of Excel and other similar spreadsheet programs such as LibreOffice Calc and Google Sheets.
Personally, I prefer using a spreadsheet over a calculator. The reason is simple. Spreadsheets allow me to scan for and correct typos much easier than do most calculators. This allows me to enter long series of numbers if needed.
The Mystery of Dollar Signs in your Spreadsheet, or How Do Cells Find Each Other?
Spreadsheets allow us to point to other cells when entering formulas. This is called a reference. A reference can point to a single cell (C1) or to a range of cells (C1:C10). References can use relative location (one cell down and two to the right) or absolute (C1). This post provides an overview and examples of these two methods for specifying cell location.
Cell references are relative references by default. That is, they identify other cells based on distance in both horizontal and vertical directions. This boils down to something like two cells to the right and one cell down from my current location.
The cell A1 has a formula that references the cell C2.
Use Advanced Filter to get rid of duplicates in your spreadsheet
Having duplicate rows or entries in your spreadsheet can drive you up the wall. It could be that you need to clean up sloppy contact info from your address book, or maybe you need to consolidate several tables into one. There are a lot of possible scenarios where the Advanced Filter tool is the solution for getting rid of duplicates in your spreadsheet.
Advanced Filter
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Date | Issue | |
| 2 | Walter | blysky@gmail.com | 10/1/2012 | Health Care Reform |
| 3 | Tyrion | theimp@gmail.com | 10/1/2012 | Health Care Reform |
| 4 | Dan | dandority@gmail.com | 10/1/2012 | Health Care Reform |
| 5 | Glenn | go-to-town@gmail.com | 10/1/2012 | Health Care Reform |
| 6 | Tyrion | theimp@gmail.com | 9/24/2012 | Foreclosures |
| 7 | Glenn | go-to-town@gmail.com | 9/24/2012 | Foreclosures |
| 8 | Kalinda | investigator@gmail.com | 9/24/2012 | Foreclosures |
| 9 | Walter | blysky@gmail.com | 9/24/2012 | Foreclosures |
| 10 | Glenn | go-to-town@gmail.com | 9/17/2012 | Schools |
| 11 | Dan | dandority@gmail.com | 9/17/2012 | Schools |
| 12 | Tyrion | theimp@gmail.com | 9/17/2012 | Schools |
| 13 | Tyrion | theimp@gmail.com | 9/10/2012 | Immigration Reform |
| 14 | Tyrion | theimp@gmail.com | 9/3/2012 | Fracking |
Merging data in a spreadsheet with multiple columns
This post builds upon “How to merge data sets using an Excel or Calc spreadsheet.” A reader, Tina, asked “What if you have more than one column to lookup to get the match value?” This post tries to answer that question.
The key thing about merging data sets is that they MUST share at least one column in common. If this is not the case, then you will have to make it so!
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | First Name | Last Name | Address | Name | Phone | |||
| 2 | Sly | Stone | 123 Main St | George Clinton | 555-555-6969 | |||
| 3 | George | Clinton | 1 P-Funk Ln | Sly Stone | 555-555-1212 | |||
| 4 | Stevie | Wonder | 22 Talking Book Ln | Stevie Wonder | 555-555-2323 |
How to merge data sets using an Excel or Calc spreadsheet
The scenario
Let us imagine two related data sets in Excel or LibreOffice’s Calc. One contains a list of names and corresponding phone numbers, and the other is a list of names and email addresses. The two data sets have a unique id in common (like a Social Security number). We want to merge the two data sets together into a single table. This is a common problem with an easy solution.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID | Tel Number | Name | ID | Name | |||
| 2 | 01 | 555-1313 | Dennis Osorio | 06 | Jesse Pinkman | capncook@heisenberg.com | ||
| 3 | 02 | 555-4242 | Carrie Mathison | 09 | Tom Haverford | tom@entertainment720.com | ||
| 4 | 03 | 555-2323 | Saul Berenson | 05 | Walter White | blue@heisenberg.com | ||
| 5 | 04 | 555-5353 | King Joffrey | 01 | Dennis Osorio | dposorio@fake.huh | ||
| 6 | 05 | 555-3636 | Walter White | 10 | Andy Dwyer | just-the-tip@pawnee.gov | ||
| 7 | 06 | 555-6464 | Jesse Pinkman | 07 | Hank Schrader | hank@thefuzz.gov | ||
| 8 | 07 | 555-4747 | Hank Schrader | 02 | Carrie Mathison | carrie.mathison@the-cia.gov | ||
| 9 | 08 | 555-7373 | Saul Goodman | 08 | Saul Goodman | saul@bettercallsaul.com | ||
| 10 | 09 | 555-5858 | Tom Haverford | 02 | Saul Berenson | saul@notevenlefthanded.net | ||
| 11 | 10 | 555-8484 | Andy Dwyer | 04 | King Joffrey | no-pain-no-gain@thelannisters.com |