Notice that the formula changes for each row, so that the Row 5 formula is =G5/C5*100000, and so on. That's what makes Excel so powerful -- the ability to change formulas as you copy down or across. Now, if we sort by Car Theft Rate in descending order, we see the cities with the worst auto theft problems: and sorting in ascending order, the least crime:
Here are some other useful Excel functions that can be used in similar ways: (You can add, subtract, multiply or divide by using the symbols + - * and /) =AVERAGE – calculates the arithmetic mean of a column or row of numbers =MEDIAN – finds the middle value of a column or row of numbers =COUNT – tells you how many items there are in a column or row =MAX – tells you the largest value in a column or row =MIN – tells you the smallest value in a column or row There are also a variety of text functions that can join and cut apart text strings. For instance: If “Steve” is in Cell B2 and “Doig” is in Cell C2, then =B2&” “&C2 will produce “Steve Doig”. And =C2&”, “&B2 will produce “Doig, Steve”. Other text functions include: =SEARCH – this will find the start of a desired string of text in a larger string. =LEN – this will tell you how many characters are in a text string. =LEFT – this will extract however many characters you specify starting from the left. =RIGHT -- this will extract characters starting from the right. =MID -- this will start extract where you tell it to start, and get as many characters as you specify. You can also do date arithmetic, such as calculating the number of days or years between two dates, or hours, minutes and/or seconds between two times. For instance, to calculate on April 24, 2014, the age in years of someone whose birth date is in cell B2, you could use this formula: =(DATE(2014,4,24)-B2)/365.25. The first part of the formula calculates the number of days between the two dates, then that is divided by 365.25 (the .25 accounts for leap years) to produce the years. Another useful date function is =WEEKDAY, which will tell you on which day of the week a chosen date falls. For instance =WEEKDAY(DATE(1948,4,21)) returns a 4, which means I was born on a Wednesday. Excel offers well over 200 functions in a variety of categories beyond just math, dates and text: Financial, engineering, database, logical, statistical, etc. But it is unlikely that you will need to be familiar with more than a dozen or so functions, unless you are a journalist with a very specialized beat such as economics. PIVOT TABLES One of Excel’s best tricks is the ability to summarize data that is in categories. The tool that does this is called a pivot table, which creates an interactive cross- tabulation of the data by category.
You've reached the end of your free preview.
Want to read all 14 pages?
- Spring '18
- Dr. Moez