Ever since I took an Excel class in high school, I've known it's a program capable of much more than I'd ever be able to use it for. At the time, we learned some of the simple formulas, and then moved on to more specific/complex ones. At one point, we displayed a detailed mortgage plan for a home. What surprised me this time around, however, is how quickly it was able to sort through data.
The ACLED dataset we are using is quite long - over 99,000 rows. This is because each row represents a political violence event in some country, on some particular date. When I wanted to summarize this data to show fatalities by year in each country, I started with the SUMIF statement. This worked but required a bit of manual manipulation. Searching for a way to nest the SUMIF statement, I found out about SUMIFS and how to set multiple conditions. This allowed me to check two whole columns (all >99,000 rows) for matching terms - the country name, year, and to sum up the number of fatalities. Once an entire row was written out, it could be copied and pasted to the row below, then the replace function used to change the country name to search for. This ended up being a great time saver.
-David
No comments:
Post a Comment