Learn four spreadsheet functions that clean raw data: todollars and topercent format numbers, N converts text that looks like numbers into actual numbers so you can do math with them, and CONVERT changes units like square feet to square metres. The video shows a real example converting a 1,000 sq ft New York apartment to square metres.
Chapters
There's a British TV show called Find It, Fix It, Flog it, where the presenters find some old junk, restore it and 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. When you have numeric data, it's often easier to comprehend it if it is formatted in a particular way. For example, if the numbers represent US 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 and as their only input and return a formatted number. 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. 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 E average gives you the proportion of true values. 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 brain power. 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. And 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, 1,000 square feet, and convert it into square metres. In this video you learnt 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. And finally, convert changes the units of a number. Now let's fix some data.
