How-To: Some Basic Excel Formulas

September 16, 2012

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.

1. Sort

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.

Advertisements

Team Data Collection Project – WVU Campus Crime

September 6, 2012

Thus far, our forays into data have dealt with simple summary data from the 2010 U.S. Census. Next week we’ll start working with data of our own collection. You’ve probably heard about the unsettling number of public beatings here at WVU this semester – it’s only been a few, but it seems like an unsettling trend. But IS it a trend? To learn more, we’ll be working with WVU Campus Police incident data.

For class next Thursday (Sept. 13), you’ll need to do the following:

  1. Create an Excel Spreadsheet titled “WVU Campus Crime for [your assigned month and year – listed below]
  2. Create the following seven headings in row 1:
    • Date Occurred (including a.m. or p.m.)
    • Time Occurred (including year)
    • Case Number (e.g., “11-0011264” – leave out the “C” at the start, if there is one)
    • Crime Title (e.g., “Theft (Building)”
    • Description (this may include the names and details of individual actors)
    • Location (listed in description prior to February 2012; if unlocatable, list as “N/A” – if locatable but not reported, note this on paper!)
    • Disposition (status – this is listed differently before and after February 2012, so be consistent)
  3. Go to the University Police crime statistics page.
  4. Find your assigned month.
  5. Go through the reports for EVERY day included in that month and enter these into your spreadsheet.
  6. SAVE & PRINT (don’t forget your name)
  7. Once your spreadsheet is complete, go to our group spreadsheet at Google Docs (“WVU Campus Crime 2011-2012“)
    • You need to send me your gmail address so I can invite you to this.
  8. Copy and paste your spreadsheet data at the bottom of the list

This must be completed by the start of class on Thursday, Sept. 13. In addition, you must bring a printout of your completed Excel spreadsheet (not the master Google docs spreadsheet) with your name and assigned month on it.

Assigned months:

  • Kirk Auvil: Aug. 2012
  • Eliza Donney: July 2012
  • Brittany Furbee: June 2012
  • Omar Ghabra: May 2012
  • Hunter Homistek: April 2012
  • Elizabeth Hurst: March 2012
  • Dan Matarazzo: Feb. 2012
  • Matt Murphy: Jan. 2012
  • Candace Nelson: Dec. 2011
  • Mary Power: Nov. 2011
  • Rachelle Purych: Oct. 2011
  • Dr. Britten: Sept. 2011