1. Convert or die!
There's a British TV show called "Find it, fix it, flog it", where the presenters find some old junk, restore it, then sell it for profit.
In the previous lesson, you saw how to find junk data. In this lesson you'll see the next step, how to fix it up.
2. Formatting numbers
When you have numeric data, it is often easier to comprehend if it is formatted in a particular way. For example, if the numbers represent U.S. currency, then it can be useful to show the dollar sign, and set the number of decimal places to two, to display the cents. The TO_DOLLARS() function provides a shortcut for this. Similarly, TO_PERCENT() displays numbers as a percentage. They both take a cell address of a number as their only input and return a formatted number.
3. Converting text to numbers
Another problem is when you have text data that contains a number, and you want it to actually be a number. Here, the value in cell A2 starts with a single quote, making it text. To convert this text to a number, you call the N() function.
4. Converting logicals to numbers
To change logical values to numbers, you again use the N() function. This changes TRUE to 1 and FALSE to 0. Why would you want to do this? Well, converting them to numbers allows you to perform calculations on them. In particular, the SUM() of these converted logicals gives you the count of TRUE values, and the AVERAGE() gives you the proportion of TRUE values.
5. Converting units of numbers
As a British person living in America, I spend a lot of time mentally converting temperatures from Fahrenheit to Celsius. Fortunately, using spreadsheets can save some brainpower. The CONVERT() function lets you change the units of a number. As well as converting temperatures, you can convert weights, distances, times, and many other things that have units.
CONVERT() takes three arguments. The first argument is the cell containing the number. The second argument is the existing unit of that number. Finally, the third argument is the unit that you'd like to convert to. Those two units are written inside double quotes, and the special values that they can take are described in the documentation.
In this example, we take the area of the largest apartment in New York, 1000 square feet, and convert it into square meters.
6. Summary
In this video, you learned to deal with four situations where you want to change the contents of a cell. Functions with names starting with "TO" format numbers. N() converts text to numbers. IF() can be used to convert logicals to numbers. Finally, CONVERT() changes the units of a number.
7. Let's fix some data!
Now, let's fix some data!