layout:true <div class="header" style="display:block; text-align: left; color:gray; font-size:1em; position: fixed; top: 0px; left: 0px; height: 30px;vertical-align:middle;margin:7px 0 0 0;width:100%;background: #fcfcfc; background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%); background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%); background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%); filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );"> <img src="images/datapolitan_transparent_small.png" style="position: fixed;top: 2px; left: 5px; width: 14%;"> <p style="display:block;text-align:left;color:gray;font-size:1em; position: fixed; top: 0px; left: 155px; height: 30px;vertical-align:middle;margin:7px 0 0 0; ">Excel Tools: Summarizing Data</p> </div> <div class="footer" style="display:block;color:gray;position:fixed;bottom: 0px;left:0px;height:30px;vertical-align:middle;margin:0 0 0 0;width:100%;background: #fcfcfc;background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%);background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%);background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%);sfilter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );"> <p class="footer"> <span xmlns:dct="http://purl.org/dc/terms/" property="dct:title">Excel Tools: Summarizing Data</span> by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" target="_blank" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks</a>, <a href="http://nijel.org/" target="_blank" property="cc:attributionName" rel="cc:attributionURL">JD Godchaux</a>, <a xmlns:cc="http://creativecommons.org/ns#" href="https://www.tinypanther.com/" property="cc:attributionName" rel="cc:attributionURL">Julia Marden</a>, and Beth Pappas </a>is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>.<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png" /></a> </p> </div> <!-- <p class="footer"> <span xmlns:dct="http://purl.org/dc/terms/" property="dct:title">Excel Tools: Summarizing Data - Additional Materials</span> by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks</a> is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>.<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png" /></a> </p> --> --- class:center, middle  # Excel Tools: Summarizing Data - - - ## Instructors: Mark Yarish and Elizabeth DiLuzio ### Follow along at: http://bit.ly/excel-tools #### See the code at: http://bit.ly/excel-tools-code --- class:center,middle # Welcome --- exclude:true # Data Driven Culture  --- # Introduce Yourself to Your Neighbor + Who are you? + Where do you work? + What has been the proudest moment in your job? --- # A Few Ground Rules ??? + Facilitators establish the intention we have for the culture of the classroom -- + Step up, step back -- + Be curious and ask questions! -- + Assume noble regard and positive intent -- + Respect multiple perspectives -- + Listen deeply -- + Be present (phone, email, social media, etc.) --- # Housekeeping -- + We’ll have one 15 minute break in the morning -- + We’ll have an hour for lunch -- + We’ll have a 15 minute break in the afternoon -- + Class will start promptly after breaks -- + Feel free to use the bathroom if you need during class -- + Please take any phone conversations into the hall to not disrupt the class ??? + Set expectations for the class --- # What to Expect Today + 9:45 – Review of Basic Operations + 10:30 – 15 min break + 10:45 – Exploratory Analysis and Basic Summarization + 12:00 – Lunch + 1:00 – Visualizing Data in Excel + 2:00 – Manipulating Data in Excel + 2:30 – 15 min break + 2:45 – Advanced Excel Functionality + 4:30 – Wrap-Up and Dismissal --- class:center,middle # What do you want to learn today? ??? + Give participants an opportunity to jot down questions they have about Excel on post-its + collect, review, and address as class progresses --- # Getting Started + What are spreadsheets? + Why do we use them? ??? + Get students thinking about what operations spreadsheets enable + Understand how spreadsheets differ from other office applications --- class:center,middle # Overview of Excel --- # Benefits of Excel -- + Easy to use -- + Very visual -- + Lots of features and functions -- + Easy to make charts -- + Does a lot of formatting for you ??? + Poll class for their key challenges working with Excel + Discuss the ways in which Excel is beneficial in daily work --- # Drawbacks of Excel -- + Not very intuitive -- + Hard to find what you’re looking for (and they keep moving things around) -- + Lots of features and functions -- + Easy to make (bad) charts -- + Does a lot of formatting for you ??? + Poll class for their key challenges working with Excel + Discuss the ways in which Excel is challenging in their daily work --- exclude:true # Cautionary Tale - Austerity in Europe  .caption[Image Credit: [http://underclassrising.net](https://www.flickr.com/photos/0742/4163565280), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)] --- exclude:true # Cautionary Tale - Austerity in Europe  Source: Mike Konczal, "[Researchers Finally Replicated Reinhart-Rogoff, and There Are Serious Problems](http://rooseveltinstitute.org/researchers-finally-replicated-reinhart-rogoff-and-there-are-serious-problems/)" --- # To Err is Human > Since 1995, 88% of the 113 spreadsheets audited in 7 studies ... have contained errors. ## - Raymond Panko, "[What We Know About Spreadsheet Errors](http://panko.shidler.hawaii.edu/My%20Publications/Whatknow.htm)" ??? + An example of how common Excel errors can have a real-world impact ??? + Contextualize occurrence of errors (they happen a lot) + Instill expectation that errors will happen and to be aware (they will happen to you) --- exclude:true class:center,middle # 88% of Spreadsheets Have Errors Source: [Market Watch](http://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17) --- exclude:true # Some Common Excel Shortcuts + `Ctrl + arrow key` - Go to the end of the text in that direction + `Ctrl + c` - Copy the selection to clipboard + `Ctrl + v` - Paste clipboard contents to the cell(s) + [Click for more shortcuts](http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-in-excel-2010-HP010342494.aspx) ??? + Introduce key shortcuts to make analysis more efficient + Emphasize that these are optional but helpful --- class:center,middle # Let's Get Started ## [Click to download our data](data/InventoryExample.xlsx) --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- # Writing Simple Formulas  --- class:center,middle # Excel Functions ## [More on Functions from Microsoft](http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010342656.aspx) --- # Basic Function Syntax ``` =FUNCTION_NAME(parameter1, parameter2,...) ``` -- ## For Example ``` =SUM(A5:A8) ``` -- + This tells Excel to sum all the values in cells A5, A6, A7, and A8 --- # SUM Function  --- # COUNT Function  --- # COUNT Function  --- # Calculate Discount  --- # Calculate Discount  --- # SUM Function with Negative Numbers  --- # SUM Function with Negative Numbers  --- # Functions in Excel -- + Allow you to do calculations in Excel -- + Also allow you to manipulate text -- + And a whole lot more... --- # Some Categories of Functions in Excel -- + Database functions -- + Date and time functions -- + Financial functions -- + Logical functions -- + Lookup and reference functions -- + Math and trigonometry functions -- + Statistical functions -- + Text functions -- + 13 categories in total -- + Explore all of Excel's functions [here.](https://support.office.com/en-us/article/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb) --- class:center,middle # Let's Tell the Story of NYC between 1970 and 2010 ## [Click to download the data](data/NYC_Population_1970-2010.xlsx) --- # Calculating Total Borough Population  --- # Calculating Total Borough Population  -- + Copy and paste the formula for each decade -- + .red[Can we paste the same formula for all boroughs?] --- # Calculating Population Change Each Decade  --- # Calculating Population Change Each Decade  -- + Copy and paste the formula for each decade starting with 1980 and each borough --- # Calculating Percentage Change Each Decade  --- # Calculating Percentage Change Each Decade  --- # Display Population Change Over Time  --- # Display Population Change Over Time  --- # Display Population Change Over Time  --- # Display Population Change Over Time  --- # Display Population Change Over Time  --- # Display Population Change Over Time  --- # Your Turn -- + In groups, create a chart of population over time in your assigned borough over time -- + Identify key trends or indications in the data -- + Find and articulate the story the data tells -- + Explore different chart types and be prepared to explain your choices --- class:center,middle # Lunch --- class:center,middle # Welcome Back! --- class:center,middle  --- exclude:true class:center,middle # 15 Minute Break --- exclude:true class:middle > Facts do not "speak for themselves." They speak for or against competing theories. Facts divorced from theory or visions are mere isolated curiosities. ## -Thomas Sowell *A Conflict of Visions* --- exclude:true # What is Analysis? > “Analysis is simply the pursuit of understanding, usually through detailed inspection or comparison” ## - [Carter Hewgley](https://www.linkedin.com/in/carterhewgley), Senior Advisor for Family & Homeless Services, Department of Human Services, District of Columbia --- class:center,middle # Looking at Noise in New York City ## [Click to download the data](data/311_Noise_20150601_20150830.csv) --- # 5 Data Analytics Tasks -- 1. Sorting -- 2. Filtering -- 3. Aggregating (PivotTable) -- 4. Manipulating (Transforming) -- 5. Visualizing --- # 1. Sorting -- + Reorganize rows in a dataset based on the values in a column -- + Can sort on multiple columns --- # Sorting by Date --  --  --- # Sorting by Date  --  --- # Sorting by Date  --  --- # 2. Filtering -- + Only show rows that contain some value -- + Can filter by multiple values -- + Can filter by values in multiple columns --- # Filtering  --- # Filtering  --- # Conditional Formatting  --- # Conditional Formatting  --- name:pivottable # 3. Aggregating Data -- + Trends only become clear in aggregate -- + Often where you discover the "so what" -- + Aggregating data meaningfully can be tricky --- # PivotTables -- + A data summarization tool -- + Useful to quickly understand data -- + Can use to graph data totals --  --- # Creating a PivotTable  -- + Should default to all your data .red[unless you have any cells selected] -- + Should default to a new worksheet --- # Creating a PivotTable  ## Drag and drop fields to visualize + Row labels + Values + Filter + Column Labels --- # Creating a PivotTable  --- # Your Turn --  + In your groups, explore the complaints for your assigned borough -- + What are the common types of complaints? -- + Which Community Districts have the most complaints? -- + Identify any other meaningful patterns to the noise complaints .caption[Image Credit: Datapolitan [CC BY-SA 4.0](http://creativecommons.org/licenses/by-sa/4.0/)] --- # Exploratory Data Analysis -- + Goal -> Discover patterns in the data -- + Understand the context -- + Summarize fields -- + Use graphical representations of the data -- + Explore outliers -- #### Tukey, J.W. (1977). Exploratory data analysis. Reading, MA: Addison-Wesley --- exclude:true class:center,middle # Visualizing Data --- exclude:true # Why do we visualize data? [](http://blog.civicdashboards.com/2014/01/vision-zero-visualizing-traffic-related-fatalities/) <!-- http://blog.civicdashboards.com/2014/01/vision-zero-visualizing-traffic-related-fatalities/ --> --- exclude:true # Why do we visualize data?  --- exclude:true # Bar Chart  --- exclude:true #"Barless" Bar Chart  --- exclude:true # Horizontal Bar Chart  --- exclude:true # Ranked Horizontal Bar Chart  --- exclude:true # Grouped Bar Chart  --- exclude:true # Grouped Bar Chart  --- exclude:true # Precognitive Processing  --- exclude:true # Stacked Bar Chart  --- exclude:true # 100% Stacked Bar Chart  --- exclude:true # Stacked Bar Chart + Which of these do you like better?   #####   + Why? --- exclude:true # Line Chart  --- exclude:true # Why use a line chart?  --- exclude:true # Line Chart with Categorical Data  --- exclude:true # Multi-Line Chart  --- exclude:true # Multi-Line Chart  --- exclude: true # Small Multiples  --- exclude: true class:middle  Source: Enrico Bertini, NYU Tandon School of Engineering --- exclude: true # Banking to 45 degrees  Source: Cleveland, William S., McGill Marylyn E., and McGill Robert. ["The Shape Parameter of a Two-Variable Graph."](http://www.jstor.org/stable/2288843) Journal of the American Statistical Association 83.402 (1988): 289-300. --- exclude:true # Pie Charts  --- exclude:true # Pie Charts   "Piecharts". Licensed under [CC BY 1.0](https://creativecommons.org/licenses/by/1.0/legalcode) via [Wikimedia Commons](http://commons.wikimedia.org/wiki/File:Piecharts.svg#/media/File:Piecharts.svg) --- exclude:true # Pie Charts  "Badpie" by Gilgongo - Own work. Licensed under [CC BY-SA 3.0](https://creativecommons.org/licenses/by-sa/3.0/us/legalcode) via [Wikimedia Commons](http://commons.wikimedia.org/wiki/File:Badpie.png#/media/File:Badpie.png) --- exclude:true # Pie Charts  Source: http://simplystatistics.org/2012/11/26/the-statisticians-at-fox-news-use-classic-and-novel-graphical-techniques-to-lead-with-data/ For Analysis: http://peltiertech.com/use-bar-charts-not-pies/ --- exclude:true # Donut Chart  --- exclude:true name:treemap # [Treemap](https://en.wikipedia.org/wiki/Treemapping)  --- exclude:true # For more on Pie Charts 1. Spence, I. (2005). [No Humble Pie: The Origins and Usage of a Statistical Chart](http://www.psych.utoronto.ca/users/spence/Spence%202005.pdf). *Journal of Educational and Behavioral Statistics*, 30(4), 353–368. 2. Heer, J. and Bostock, M. (2010). [Crowdsourcing Graphical Perception: Using Mechanical Turk to Assess Visualization Design](http://hci.stanford.edu/publications/2010/crowd-perception/heer-chi2010.pdf). *CHI 2010*, April 10–15, 2010, Atlanta, Georgia, USA. --- exclude:true # Channels and Marks  <br> From Tamara Munzner, [Visualization Analysis and Design](http://www.cs.ubc.ca/~tmm/vadbook/) --- # 5. Visualizing Data -- + Think about your audience -- + Reduce the amount of effort it takes to understand your visualization -- + Select colors with high contrast -- + There are always ways of improving --- class:center,middle # How do you learn to create good visualizations? #   --- class:center,middle # How do you learn to create good visualizations? # ...Make a lot of bad visualizations --- # Your Turn -- + Take a few minutes with the data you have for your borough -- + With your group members, play with a new or different way to visualize the data -- + Be ready to describe why it works (or doesn't) to the class --- # 4. Manipulating Data (In a good way...) -- + Sometimes available categories don't make sense -- + Values may not be in the format you need (or have mistakes) -- + You always want to have a clean copy of the data to go back to -- + Best to keep track of what you've done --- # Extracting Hour From Timestamp --  --- class:middle,center [](https://support.office.com/en-us/article/HOUR-function-e2833b50-0db0-499b-acc5-e9ae03de8fbb) --- # Extracting Hour From Timestamp  --- # Extracting Hour From Timestamp  --- # Extracting Hour From Timestamp  --- # Extracting Hour From Timestamp  --- # Extracting Hour From Timestamp  --- # Extracting Hour From Timestamp  --- class:center,middle # When Are Noise Complaints Received?  --- # Charting Noise Complaints by Hour  + Refresh PivotTable --- # Charting Noise Complaints by Hour  + Refresh PivotTable + Find `Hour` and add it to "Rows" --- # Charting Noise Complaints by Hour + Refresh PivotTable + Find `Hour` and add it to "Rows" + Style the chart  --- # Charting Noise Complaints by Week Day  --- # Charting Noise Complaints by Week Day [](https://support.office.com/en-us/article/day-function-8a7d1cbb-6c7d-4ba1-8aea-25c134d03101) -- + Not what we want --- # Charting Noise Complaints by Week Day [](https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a) -- + What we want --- # Charting Noise Complaints by Week Day  --- # Charting Noise Complaints by Week Day  --- # Charting Noise Complaints by Week Day  --- # Charting Noise Complaints by Week Day -- + Refresh the PivotTable -- + Add `dow` to Rows -- + Format the chart  --- # Charting Noise Complaints by Week Day -- + You could also use `CHOOSE` --  --- # Charting Noise Complaints by Week Day  --  --- # Your Turn -- + Explore patterns by hour of the day and day of the week for your borough -- + Explore trends by `Complaint Type` and `Descriptor` -- + Explore ways to visual represent this data with charts -- + Refine the story the data tells about your borough --- class: middle, center # 15 Min Break --- class:center,middle # We're Going to Join the Population Data with the 311 Service Requests  --- # VLOOKUP -- + Useful for referencing data from another sheet (reference tables) -- + Basic Syntax ``` =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ``` -- + Example ``` =VLOOKUP(A2,SHEET2$A1:$D500,4,FALSE) ``` --- # Preparing the Data -- + We need a lookup value (key) that matches between sheets -- + The Community Districts are in the format `## BOROUGH` -- + We can use a formula to create this in our population sheet --- # CONCATENATE Function -- + Useful for combining text fields -- + Basic Syntax ``` =CONCATENATE(text1, [text2], ...) ``` -- + Example  --- # CONCATENATE Function + Useful for combining text fields + Basic Syntax ``` =CONCATENATE(text1, [text2], ...) ``` + Example (with space between names)  --- class:center,middle # Let's Get Better Population Data ## [Click to Download Population Data](data/NYC_Population_1970-2010_flat.xlsx) --- # Preparing the Population Data -- + Insert a column -- + Call it `CB_Number` -- + Give it the formula: ``` =CONCATENATE("0",A2," ","B2") ``` -- + Note the space before the borough name  --- # Preparing the Population Data + Copy and paste the formula down  --    --- # Preparing the Population Data + Notice the leading zero for Community Districts 10 - 12  -- + What can we do about this? --- # Preparing the Population Data + The simple method is to delete the `"0"` from the formula  --- class:center,middle # What if we didn't want to manually edit the formula? --- # Nested Functions  + What are we telling Excel to do? --- # Nested Functions  -- 1. Test the value in Cell A11 to see if it's less than 10 --- # Nested Functions  1. Test the value in Cell A11 to see if it's less than 10 2. `IF` it's less than 10, add a 0, otherwise, add an empty string (`""`) --- # Nested Functions  1. Test the value in Cell A11 to see if it's less than 10 2. `IF` it's less than 10, add a 0, otherwise, add an empty string (`""`) 3. `CONCATENATE` the value above with the contents of Cell A11 and B11 (along with the space " ") --- exclude:true # Now One Formula Rules Them All  --- # Preparing the 311 Data -- + Aggregate the number of noise-related complaints by Community District -- + HINT: Use a PivotTable --  --- # Preparing the 311 Data + Add a column labeled `CD Population`  -- + This is where we're going to import the population using `VLOOKUP` --- # Adding the VLOOKUP ``` =VLOOKUP(A5,'[NYC_Population_1970-2010_flat.xlsx]A'!$C$1:$I$60,7,FALSE) ``` -- + Use the value in cell A5 (from our PivotTable of complaints by `Community Board`) -- + Go to Workbook `NYC_Population_1970-2010_flat.xlsx` -- + Find Sheet `A` -- + Match the value from cell A5 in Column C, Rows 1 - 60 -- + Return the value in column 7 (I) -- + Don't do any [range matching (exact match)](https://www.excel-university.com/perform-approximate-match-and-fuzzy-lookup-in-excel/) --- # Adding the VLOOKUP + Verify the result  --- # Adding the VLOOKUP + Copy and paste the formula  --- # Calculating the Complaints per 1000 People  --  --- # Conditional Formatting  --- # Conditional Formatting  --- class:center,middle # WRAP-UP --- # What We Covered Today -- + Formulas -- + Functions -- + PivotTables -- + Charts -- + What Else? --- # Resources -- + [City of New York Department of City Planning NYC Population Page](https://www1.nyc.gov/site/planning/data-maps/nyc-population/census-2010.page) - NYC Population 1970 - 2010 -- + [NYC Open Data Portal](https://data.cityofnewyork.us/) - 311 Noise Complaint data -- + [Microsoft Excel keyboard shortcuts](https://support.office.com/en-us/article/keyboard-shortcuts-in-excel-for-windows-1798d9d5-842a-42b8-9c99-9b7213f0040f) -- + [Microsoft Office Community](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?sort=LastReplyDate&dir=Desc&tab=All&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=All&isFilterExpanded=false&page=1) - Community Q&A Board -- + [7 essential Excel tricks every office worker needs to know](https://www.microsofttraining.net/sharing/11-seven-essential-excel-tricks-every-office-worker-needs-to-know.html) (we covered 6 of the 7 today) -- + [11 Places That Can Turn You Into A Microsoft Excel Power User](https://medium.com/the-mission/eleven-places-that-can-turn-you-into-a-microsoft-excel-power-user-c119f18e138c) -- + [Data Sensemaking](http://fellinlovewithdata.com/teaching/developing-a-data-sensemaking-course) by Enrico Bertini -- + [Datapolitan Training Classes](http://training.datapolitan.com) --- name:contact ## .center[Contact Information] <!--### Ali Holstein <ul> <li style="font-size:35px"> Email: ali.holstein[at]gmail[dot]com </li> <li style="font-size:35px"> Website: <a href="http://www.aliholstein.com">http://www.aliholstein.com</a> </li> <li style="font-size:35px"> Twitter: <a href="https://twitter.com/aliholstein">@aliholstein</a> </li> </ul> ### Richard Dunks <ul> <li style="font-size:35px"> Email: richard[at]datapolitan[dot]com </li> <li style="font-size:35px"> Website: <a href="http://www.datapolitan.com">http://www.datapolitan.com</a> </li> <li style="font-size:35px"> Twitter: <a href="https://twitter.com/datapolitan">@datapolitan</a> </li> </ul> ### Imran Khan <ul> <li style="font-size:35px"> Email: buildwelllabs[at]gmail[dot]com </li> </ul> --> <!--### Julia Marden <ul> <li style="font-size:35px"> Email: julia[at]tinypanther[dot]com </li> <li style="font-size:35px"> Website: <a href="http://tinypanther.com">http://tinypanther.com</a> </li> <li style="font-size:35px"> Twitter: <a href="https://twitter.com/juliaem">@juliaem</a> </li> </ul> --> ### Elizabeth DiLuzio <ul> <li style="font-size:35px"> Email: Elizabeth[at]datapolitan[dot]com </li> <li style="font-size:35px"> Website: <a href="http://www.evallearn.com">http://www.evallearn.com</a> </li> <li style="font-size:35px"> Twitter: <a href="https://twitter.com/lizdiluzio">@lizdiluzio</a> </li> </ul> ### Mark Yarish <ul> <li style="font-size:35px"> Email: mark@datapolitan.com </li> </ul> --- class:center, middle # THANK YOU! ## [PDF Copy of Workbook](workbook.pdf) --- # Datasets for Class + [Inventory Example](data/InventoryExample.xlsx) + [NYC Population Data, 1970-2010](data/NYC_Population_1970-2010.xlsx) + [311 Noise Complaints, 1 June - 30 August 2015](data/311_Noise_20150601_20150830.csv) --- # Key Excel Functions for Summarizing Data + [`=SUM()`](https://support.office.com/en-us/article/SUM-function-043e1c7d-7726-4e80-8f32-07b23e057f89): Calculates the sum for a range of numbers + [`=COUNT()`](https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c): Counts the number of cells containing numbers in a range + [`=COUNTA()`](https://support.office.com/en-us/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509): Counts the number of non-blank cells in a range + [`=HOUR()`](https://support.office.com/en-us/article/HOUR-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7): Extracts the hour from a timestamp + [`=WEEKDAY()`](https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a): Extracts the day of the week from a timestamp + [`=CHOOSE()`](https://support.office.com/en-us/article/CHOOSE-function-FC5C184F-CB62-4EC7-A46E-38653B98F5BC): Uses an index number to return a result from an ordered list of values --- # Key Excel Functions for Summarizing Data + [`=MID()`](https://support.office.com/en-us/article/MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028): Select a specified number of characters from a text string + [`=LEFT()`](https://support.office.com/en-us/article/LEFT-LEFTB-functions-9203d2d2-7960-479b-84c6-1ea52b99640c): Select a specified number of characters from the beginning of a text string + [`=RIGHT()`](https://support.office.com/en-us/article/RIGHT-RIGHTB-functions-240267ee-9afa-4639-a02b-f19e1786cf2f): Select a specified number of characters from the end of a text string + [`=FIND()`](https://support.office.com/en-us/article/FIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628): Find the location of a given character in a text string --- # Key Excel Functions for Summarizing Data + [`=CONCATENATE()`](https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d): Combine characters together into a text string + [`=VLOOKUP()`](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1): An operation to look up a value in another location based on an index value + [`=IF()`](https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2): A function for logical comparison between values to return a desired result given a particular condition --- # Formulas for Class + Extract hour from timestamp `=HOUR(B2)` + Extract day of the week from timestamp `=WEEKDAY(B2)` + Formula to display the name of the day ``` =CHOOSE(D2,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") ``` + Formula to concatenate Community District and Borough ``` =CONCATENATE(IF(A3<10,"0",""),A3," BRONX") ``` + Formula for VLOOKUP of Community District Population (may need to adjust references) ``` =VLOOKUP(A5,'[NYC_Population_1970-2010.xlsx]A'!$B$1:$H$81,7,FALSE) ``` --- # Have trouble with the analysis? + [Inventory Example with Formulas](data/InventoryExample_with_Formulas.xlsx) + [NYC Population, 1970 - 2010 with calculations](data/NYC_Population_1970-2010_calculated.xlsx) + [311 Noise Complaints in NYC with calculated fields](data/311_Noise_20150601_20150830_calculated.xlsx)