Richie covers the four data types in spreadsheets: numbers, text, dates, and logical values. He shows why AVERAGE fails when a cell contains the letter I instead of 1, then teaches IS-family functions to check what's actually in a cell, plus keyboard shortcuts to copy formulas across large datasets.
Chapters
Hi, I'm Richie, a curriculum architect at datacamp. The main purpose of this course is to expand your spreadsheet vocabulary. You're going to see a lot of new functions that will come in handy in future courses. Let's start with an easy Calculating the mean of five numbers you type equals to denote a formula, call the average function and pass it the range of cells containing the numbers. Unfortunately, something's gone wrong. The answer ought to be about 33, not 34. Pause the video and see if you can spot what the problem is. Did you find the problem in the fourth number? At the sixth decimal place? The number one is actually the letter I. That means that the spreadsheet ignores that cell when it makes the calculation. When you only have five rows of data, it isn't too tricky to locate the problem. If you have 5,000 rows of data, it's much harder. If you have to stare at every single cell in a workbook that big, you'll quickly go spreadsheet blind. Before we get to the solution, let's take a look at the most common types of data that you'll encounter in a spreadsheet. There are only four of them, namely numbers, text, dates, and logical. Logical data contains true and false values. You can test for different types of data using functions that begin with is. For example, isNumber takes a cell location as an input and returns true if that cell contains a number and false otherwise. This allows you to be sure that your cells contain the type of data that you thought they did. Similarly, ISTEXT checks for text data, ISdate checks for date data, and islogical checks for logical data. Easy. In addition to checking for the four common data types, there are some other checks that you will encounter in this chapter. You can check if the text in the cell is a web address using isURL, and you can check for formulas using isFormula. One Google Sheets technique that you need to know throughout this course is how to copy a formula across cells. When you select a cell, it has a blue square in the bottom right hand corner. Click that square and drag it to copy the formula to new cells. Alternatively, you can select both the cell containing the formula and the cells that you want to copy to, and press Control R to copyright or Control D to copy down. If you use Mac os, the equivalent commands are CMD R or CMD D. Alrighty, let's get started.
