When you’re working with any spreadsheet (and especially one with a few thousand entries, like our WVU crime stats), it’s helpful to know some ways to work with that data. The real power of Excel is the way it allows you sort, count, and otherwise crunch those piles of data into more manageable pieces. Here are a few we’ve gone over in class that should help you winnow your selections down to something more comprehensible: Sort, COUNT, SUM, and WEEKDAY.
Before we start: Several of these techniques require the use of functions. In Excel, any cell that uses data in other cells must start with an “=” sign. For example, if you wanted a cell to show the value of cells A1 and B1, you’d enter “=A1+B1” (without the quotation marks). Functions work like this but are more powerful and have special names. For example, if you were using the “SUM” function, you’d enter “=SUM()” – the specifics of the SUM function would be included in those parentheses, which will be explained below.
First, select all of your data (Command+A), then go to Data > Sort. This gives you the option to sort by any of your data columns. Make sure you click the “Header Row” box (you don’t want your headers to get moved off the top row). Depending on the version of Excel you have, you will then be able to sort by the name of the column (e.g., “Date Occurred”) or by its letter (e.g., “Column A”) in either Ascending or Descending order.
Next steps: Once you’re comfortable with basic sorting, multi-level sorting can help you really break down the data. For example, if you sort by “Location,” then by “Date,” then by “Time,” you’ll get an alphabetical listing of each recorded location grouped by date, then time (for example, you’d get chronological listings for Boreman Hall, then Braxton, and so on).
2. COUNT functions
Once sorted, the next thing you’ll want to do is count the kinds of data you’re interested in. This will be your first function. There are two kinds of Count functions: “COUNT” and the more powerful “COUNT.”
COUNT: This returns how many cells in a range (e.g., from cell A1 to A100) have a value. To find this, you’d enter “=COUNT(A1:A100)” (no quotes). The formula will then tell you how many cells in that range are not blank.
COUNTIF: This more powerful version of COUNT requires a range and a condition. Let’s say you wanted to know how many cells in the A1 to A100 range are Liquor Law violations. You’d enter “=COUNTIF(A1:A100,”Liquor Law”)”. IMPORTANT: The second argument (“Liquor Law” in this case) MUST have quotes around it!
Next steps: You can do numerical conditional statements as well. For example, if cells B1 to B100 listed numbers of reports, and you wanted to count every cell with more than five arrests, you’d enter “=COUNTIF(B1:B100,”>5″)”. Once again, those quotes MUST be around “>5” and you MUST include the comma between the range (B1:B100) and the conditional statement (“>5”).
[You can also use this to count entries after a certain time – to count all Times after 8 p.m., you could enter “=COUNTIF(A1:A100,”>20:00:00″). Note, however, that this only counts from 8 to midnight, so if you wanted all overnight hours, you’d also need to do a COUNTIF for 00:00:00 to 00:06:00, then add the two counts together.]
3. SUM functions
Once you’ve done your counting, you might want to add up some cells. For example, let’s say you’ve counted Towing Assists separately for each month in cells C1 to C12. You could add these one-by-one (e.g., “=C1+C2+C3+…C12”), but it’s a lot easier to use SUM. To do this, enter “=SUM(C1:C12)”. The formula reports the sum for all cells in the range.
There’s also SUMIF, a conditional formula that works a bit like COUNTIF but requires three arguments: range, condition, and range to sum (that means two different columns). It’s a little more complicated, so use it carefully. Let’s say cells D1-D100 is Locations and cells E1-E100 is number of citations issued. If you wanted to see how many citations had been issued for Summit Hall, you’d enter “=SUMIF(D1:D100, “Summit Hall”, E1:E100)”.
[How’s that different from SUMIF? COUNTIF only counts the number of cells in your range that have values; SUMIF totals the values in those cells. For example, if your cell values were “2 2 3 4 2” COUNTIF would return “5” but SUMIF would return “13”.]
Next steps: Excel offers many similar mathematical functions of this type: AVERAGE reports the average of a range, MAX and MIN report the largest or smallest value in that range, and so on. I won’t list them all here, but if there’s a numerical function or calculation you’re looking for, there’s a good bet Excel has it.
4. WEEKDAY function
You’ll like this one. Right now our data has dates but not days of the week, something you’d probably like to know (for example, if you’re listing all Friday and Saturday crimes). If cell A1 is a date, you’d enter the formula “=WEEKDAY(A1)”. The formula would return 1-7, with 1=Sunday through 7=Saturday.
To find this for all your data is also easy. In the first cell of a blank column on your data spreadsheet, enter the formula for that row (e.g., “=WEEKDAY(A1)”). Select the cell and copy it, then use shift to select that column from its first cell to the last, then Paste; Excel will copy the formula to all cells and will automatically update it for each row. Now you can sort by day of the week as well!
That seems like enough for you to do some damage with. We’ll talk more about what you’re working on this Tuesday.