How-To: Some Basic Excel Formulas

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.

Team Data Collection Project – WVU Campus Crime

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

 

Welcome to Graphics and Data Journalism!

This blog will function as our living syllabus for JRL 593F, Graphics and Data Journalism. It was originally created for the Blogging and Interactive Journalism course (offered every spring – talk to me for details), but this class is a close cousin to that one. Also I got tired of the blog being dormant every fall.

There are two major (and overlapping) parts of this course. As the title suggests, you will learn how to work with 1) graphics, and 2) data. Here are some points about those two areas and how they are alike/different. Please note: These are all generalizations to help you thinking about these two closely related areas of journalism – you will find many exceptions as we progress through the course.

• Although both have been around for a while, information graphics as a newsroom institution is probably a bit older, tracing to the 1980s advent of desktop publishing.
• Data journalism has been around in a variety of forms for decades (Philip Meyer was doing computer assisted reporting in the 1960s!), but its current form can reasonably be tied to the (late) 2000s.
• If the name of the job is important to you: Both forms can exist as part of departments or as skills tied to more traditional reporting jobs. In general, however, the infographics path tends to lead to the graphics/news visuals department, and the data path is more likely to be applied as a skillset by a traditional reporter.
• That said, both deal heavily in visualization. The infographics side tends exclusively visual, while the data side visualizations may be text only (e.g., interactive tables). Tables are visual, of course, but they lack the illustrative component more commonly seen in infographics, and a data journalist can do his/her job without ever learning Adobe Illustrator.
• The role of linear storytelling is also a point of distinction. Although both are nonlinear to an extent, infographics are more likely to take a narrative path with clear beginnings, middles, and ends (such as artist Wayne Dorrington’s retelling of Star Wars in icon form). Data journalism, on the other hand, often takes a form where Reader X can obtain an entirely different story than Reader Y (e.g., ProPublica’s Opportunity Gap).
• Regardless of the above distinctions, however, BOTH require a sense for visual, nonlinear storytelling.

So that should help you to think a bit about what we’ll be doing in here. In addition to the readings on eCampus, we’ll be using The Digital Journalism Handbook. This is new, and we’re going to try it out in the data components of this course. It’s a tremendous source of case studies and tips to what we’ll be doing.

This blog will continue to be a source of course material, so bookmark it, RSS it, do whatever you need to check it regularly. We’ll also be using the Twitter hashtag #WVUdataJ to share information with the group. Post a comment there, or to this page, as we get rolling.

See you in class!

 

Knight News Challenge 2012

We have spent this semester applying new tools to the news and exploring a number of innovations in communication. Now it’s your turn. You will submit an innovation project to the Knight News Challenge. This project “seek(s) innovations that use new or available technology to distribute content in local communities,” and it offers millions of dollars in development awards to make your project a reality.

The Rules

For your project, you’ll come up with a short, simple writeup and presentation of your innovation project and why it deserves some of that money. The latest round of the Knight News Challenge had a focus on networks (you can see the specifics here). Although the March 17 submission deadline is now closed, we’ll use this as our format too. From the KNC site:

The Networks challenge round seeks projects that use the best of existing software and platforms – those already integrated into people’s lives – to find new ways to convey news and information. Winners will be announced in June. Future categories will be announced later this year. Each of the three rounds will be eight to 10 weeks long, for shorter, more focused contests that better mirror the pace of innovation. Anyone, anywhere can apply.

The rules are available here (READ THIS), but overall you should:

1. Use digital, open-source technology.
2. Distribute news in the public interest.

You do NOT need to be a technical maestro (but it helps if you can find one to work with). You just need an idea that meets the above criteria and a pitch for why it’s worth funding – the Knight money will cover development and promotion. Past entrants have incorporated SHORT video pitches – this is not required, but you might want to consider it.

The Proposal

Your proposal will be a short writeup with seven sections – please note the word counts (they’re serious about these):

0. Name of your project (e.g., “Factlink,” “Bias Map”)

1. What do you propose to do? [20 words]

2. Is anyone doing something like this now, and how is your project different? [30 words]

3. Describe the network with which you intend to build or work. [50 words]

4. Why will it work? [100 words]

5. Who is working on it? [100 words] – NOTE: You probably don’t have an existing team, so just discuss who might be involved.

6. What part of the project have you already built? [100 words] – NOTE: Again, you probably don’t have anything built yet, so you can discuss a little about any existing resources you may have here.

7. How would you sustain the project after the funding expires? [50 words]

Requirements:

• A writeup of your proposal. These are not long (a page or two) but must be detailed and address each of the seven KNC questions (see above). See past proposals on the site for examples. DUE IN CLASS APRIL 17
• A presentation of your proposal. These, likewise, are not long – 5 to 10 minutes – and use of visual and digital techniques is STRONGLY encouraged. We’ll discuss these in the weeks before the due date to help firm up your ideas. DUE IN CLASS APRIL 24

Useful links:

• Full information from Knight
• FAQs about the challenge
• Sample entries:
  • Bias Map
  • latakoo
  • Factlink
  • MANY MORE!

A final note: The requirements of this assignment have changed to what’s listed here now. If you’ve already started on the previous assignment (rules listed here) and don’t think you can change to fit this format, you can continue with that format – just let me know so I’m not surprised by your submission.

Read & Respond – Week 13

If it wasn’t clear before, I haven’t assigned you any online readings this week because you’ve got two chapters from Briggs to read already. Address these in adequate detail in your response – how does what he has to say on the audio/visual side of blogging inform your work? It’s our last week of Briggs readings, so it wouldn’t hurt to provide some overview on the text as well; I take your feedback into account in my book selection.

Okay, there’s just one more thing. We’ll have grad student/course alumnus/broadcast jack-of-all-trades Corey Preece in class this week discussing his German travels last year, courtesy of (*ahem*) this course. To get up to speed, have a look through this blog, which consists of posts he made/facilitated while over there, and come prepared with questions.

Your responses (say it with me, now) are due as a comment posted to this blog by noon Tuesday, April 10. Even though the only readings are assigned in the syllabus, I thought I’d give you an extra day just because I like you.

Digital Tools and Meme extra credit

As promised, here are the links to the visual tools we discussed in class. My original source for this is Poynter, if you’d like to check out their listing.

• Wordle.net (word clouds)
• Dipity.com (interactive timelines)
• Batchgeo.com (interactive maps)
• Many Eyes (data visualization)

Requirements:

You need to add one of these tools to at least one blog post (personal or group) by the end of next week, Friday, April 13 (!!!). You must post the link to your post in a comment to this assignment.

Extra Credit:

Also, for 2 points of extra credit (that’s a blog or readings post), you can create an SOJ-specific meme and upload it as a comment to THIS post. Quickmeme is an easy to use site, but do what works for you. Please keep it reasonably friendly – we’re reflecting the School of Journalism here, after all.

 

Read & Respond – Week 12

Here are some reasonably light visual readings for your return from spring break. Mainly, you need to read through the assigned Briggs chapter 6 on visual storytelling. Think about his advice and note the example experts he gives. Some of you have been incorporating visuals into your work from the start, others have come around to it, and a few are still pounding out mostly text articles each week. Use these readings as a way to start thinking about more creative forms of visual storytelling. How might your blogs tell a story that is more visual than text (and while “use lots of photos” is surely an answer, it’s not the ONLY answer).

Second, I’d like you to look at some photoblogs. What’s a photoblog? Find out for yourself. You’ve probably seen some photoblogs – post an example in your response (and use an in-text link, please – full URLs are so tacky).

A great home for this kind of thing is Tumblr. This site (and others like it) are becoming prominent examples of quick-hit, visual blogging (actually, I’m not even sure if “blogging” is the right word for whatever Tumblr is, but it’ll suffice for now). This Huffington Post list of “33 Tumblrs you NEED to follow” is a good place to explore. It may not be your thing, but resist the urge to gripe’n’grumble and remember: Look past the content to what’s behind it. What ideas are here that we as journalists can use?

Finally, something that you’ll either get a kick out of or really hate: Memes. The meme is a basic cultural unit (much like the gene is a biological one). Like a virus, it lives to spread from carrier to carrier, mutating and adapting as it goes. Successful memes thrive, unsuccessful ones die out. Go to quickmeme (or your own favorite meme creator, if you have such a thing) and browse the current and most popular images. Then go to the Facebook WVU Memes page and scan back a few weeks/months (if you weren’t aware of this, I’m sorry/you’re welcome). Yes, Marshall and Pitt have their own meme pages, and the college meme trend is already showing signs of burning out, but take a look anyway. Resist the urge to say “this is dumb” (which it surely is), and consider what’s going on here. I won’t ask you to come up with a journalistic application for memes, but how might the kind of sharing and creativity going on here be useful in more valuable mass communication?

Remember, your responses are due by noon Monday, April 2 (after spring break) as a comment to this post.