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.
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.
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.
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.
|2||Walteremail@example.com||10/1/2012||Health Care Reform|
|3||Tyrionfirstname.lastname@example.org||10/1/2012||Health Care Reform|
|4||Danemail@example.com||10/1/2012||Health Care Reform|
|5||Glennfirstname.lastname@example.org||10/1/2012||Health Care Reform|
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!
|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|
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.
|2||01||555-1313||Dennis Osorio||06||Jesse Pinkmanemail@example.com|
|3||02||555-4242||Carrie Mathison||09||Tom Haverfordfirstname.lastname@example.org|
|4||03||555-2323||Saul Berenson||05||Walter Whiteemail@example.com|
|5||04||555-5353||King Joffrey||01||Dennis Osoriofirstname.lastname@example.org|
|6||05||555-3636||Walter White||10||Andy Dwyeremail@example.com|
|7||06||555-6464||Jesse Pinkman||07||Hank Schraderfirstname.lastname@example.org|
|8||07||555-4747||Hank Schrader||02||Carrie Mathisonemail@example.com|
|9||08||555-7373||Saul Goodman||08||Saul Goodmanfirstname.lastname@example.org|
|10||09||555-5858||Tom Haverford||02||Saul Berensonemail@example.com|
|11||10||555-8484||Andy Dwyer||04||King Joffreyfirstname.lastname@example.org|