 Welcome to this presentation, Analyzing Quantitative Data. This presentation is part of a larger project made possible by a grant from the U.S. Institute of Museum and Library Services. This presentation will cover some common strategies for analyzing quantitative data, particularly descriptive statistics using Excel and Google Sheets. For more in-depth quantitative analysis, particularly inferential statistics, I recommend using a separate statistical tool, which I'll discuss briefly in the last section of this presentation. To begin, quantitative data are used when a researcher is trying to quantify a problem or address the what or how many aspects of a research question. It is data that can either be counted or compared on a numeric scale. Conversely, qualitative data describes qualities or characteristics, most typically you'll find it appearing in a narrative form. In this presentation, we'll touch briefly on what descriptive and inferential statistics are. I'll show you some basic mathematical or computational functions in Excel that you may find useful. Then we'll spend the majority of our time creating and using pivot tables to aggregate, summarize, and look for trends. Everything that I'll show you in Excel can also be conducted in Google Sheets. When there are differences in how things look or are accomplished between the Excel and Google Sheets, I'll also demonstrate those things in Google Sheets. My primary goals with this presentation are that you will develop a better perspective about how to conduct some basic statistical computations and quantitative analyses using Excel and Google Sheets. And become familiar with some key terminology and concepts that you can use to help make your life much easier as you analyze your data. To begin, you'll often hear people discuss descriptive and inferential statistics. Descriptive statistics are just that they are used to describe and summarize your data set. They include things like counts or frequencies, percent of total, sums or totals, products, which is the result of a multiplication, means, which are averages, median, which is the midpoint of a range, and mode, which is the most frequently occurring item in a range. Common inferential statistical tests are key tests, which allow you to look for significant differences between two groups. Analysis of variance or ANOVA's as they're typically called, which enable you to determine if there are significant differences in the variance between two or more groups. Regressions, which help you determine which variables impact other variables. And correlations, which allow you to test for linear relationships between two or more variables. In this presentation, we're going to focus on descriptive statistics and how to analyze data sets for those stats using Excel and Google Sheets. On the next set of slides, I'm going to demonstrate some basic computational strategies and pivot tables. I encourage you to follow along on your own computers by downloading the Excel file, which you should see located very close to the video file. The Excel file is called sample data for analysis. We'll begin with a few basic common mathematical functions or computations. Some of this may be quite familiar to many of you, so think of this as a warm up if it is or a quick refresher. We'll use some student grades for this quick refresher and we'll do some summing and then means, median and mode. In both Excel and Google Sheets, all equations or formulas or functions begin with an equals sign. For basic computations, you can just use the basic signs plus to add minus to subtract an asterisk to multiply and a slash to divide. If you're doing anything more in depth or using more than a few rows or cells, I recommend using a formula. These formulas again will start with an equal sign followed by a mathematical term, a word, followed by the range of cells that you want to compute. You'll begin by clicking in the cell where you want the end result to go and typing in your formula. Or once you click in that cell, you can click on the function sign, the f sub x symbol to the left of the formula box to activate the formula wizard. Let's go ahead now and navigate to the Excel file, sample data for analysis and click on the student grades tab. I've set up this little worksheet. Let's see here. I've set up this little worksheet so I can show you a few simple computations that I hope you'll find helpful. And the process for all of these is exactly the same in Google Sheets. So I just made up this little data set of student grades. So we have quizzes, and we're going to compute an average quiz grade. Quizzes are weighted at 20% of the total grade. We have homework, which is weighted at 10%, midterm grades weighted at 30, and the final grade is weighted at 40, or the final exam is weighted at 40. So figuring out your average or your mean quiz grade is super, super easy. You just type in equals, and then the word average. I'm just going to go ahead and take advantage of the helper text here. And then I'm just going to highlight all of the cells in the range I'm interested in, and then close the parentheses and hit enter. And so the average quiz grade for that first student is 95 on down. And this little warning, don't worry too much about that. It just tells us the formula omits adjacent cells, meaning column A is probably what they're referring to. We don't really care about that. So we're just going to say ignore. I'm going to go ahead and get rid of that warning sign for all of these things. You could just leave that as is if you didn't want to delete the warning sign. Then remember the quizzes are averaged at 20%. So I'm going to just click equals. Click on the cell of interest, multiply, which is the asterisk, and then point to four, which represents 20%. So now this student, for example, will have 19 points from quizzes that go towards their final grades. I'm going to do the same thing for homework. I'm going to average the homework grades. Notice here I have some variations with the use of decimal points. You could standardize that if you want. You can right click and click on format cells, click on number, and then just reduce that decimal to one if you want. Let's go ahead and calculate the weighted score. So I'm just clicking on average times 0.1 for 10%. So this first student is going to have 7.5 points go towards their final grade. Midterm doesn't have anything. The average is just one grade. So we're just going to do the waiting, clicking on J2 times 0.3. And I'm going to do the same thing for the final, the final exam. This time it's weighted at 0.4 or 40%. So now all of these scores, the weighted scores can be added up or summed. And then you'll have your final score based on 100 points. So there's a couple ways we can do that. We can just do equals. Click on the first cell plus sign. Second cell plus sign. The third one, the midterm weighted and the final exam. So you could do that or you could do equals and use the insert function option. And we're still going to be doing a sum. This time this is the wizard saying what's your first number you want to add. What is this? Whoops, we've got to be right here because we're in the second row of data. This, now we're going to go to the second number we want to add, which is I2 or let's see. Yes, I2 or I3 we're in the third row of data, even though it's the second student. We're going with the numbers for the rows here. That third number we're adding is the midterm grade. And the fourth number we're adding is the final. So two strategies to sum get you the same results. Now, down here at the bottom, let's go ahead and see what the mean or average is for each of these things. That's really simple. You can just type equals, average, open parent. This time we're going to be doing a vertical range of data. Just highlighting everything in this column. So the average quiz grade on the first quiz is 82%. And then you can just simply drag that equation all the way to the right. And now you see the averages for all of these items. So the average total grade is 89.3. If you wanted the midpoint of the range, you just do equals, median, and then the same thing. And then we'll do mode equals mode. And then that same range of data. And then we, I'm going to just highlight both of those things at one time and drag it over to the right. And perhaps I want everything to be set at one decimal point. All of these mean medians and modes. I'm going to go ahead and highlight everything. I'm going to go up here and just play around with these shortcuts. Let's see, we got an MA for this final mode. And that's just telling us that there is no, that isn't working for this equated field or this computed field. I'm just going to delete that because we really don't need it anyway. If you had a truly normal distribution, you would expect your mean median and mode to all line up and be the same information. So this is skewed in one direction or another, a little more towards the higher scores. So hopefully you'll find some of that helpful. I'll go back to the slides. So in the next set of slides, we're going to look at some survey data and use pivot tables to aggregate and conduct some basic analyses. Pivot tables allow you to look at your data in a lot of nuanced ways and are really the most important thing I learned about early on in my library assessment and research career. I'll show you the ropes in Excel, and then I'll show you the same tools in Google Sheets. The concepts between the two softwares are the same, though the layout in Google Sheets differs just a little bit. So pivot tables are really one of the most useful and powerful tools in Excel and Google Sheets for quickly doing calculations, aggregating, summarizing and analyzing. Essentially, it allows you to create a tidy little summary table from what might be a giant spreadsheet with thousands of rows and summarize and analyze the data with only a few keystrokes. It allows you to quickly explore your data and facilitates making comparisons and finding patterns or trends in your data. For example, here are two screenshots. On the left is a portion of a survey data set, and on the right is a pivot table I created that summarizes the number of responses received to the survey in relation to participants college and year in school. The pivot table editors in Excel and Google Sheets work very much the same, though the layout says I noted earlier just a bit different. I'll show you in the demos in just a moment or two. First, you'll see the names of all of your fields or columns that you can, let's see, let me say that again. First, in this top portion of the Excel screenshot, you see all of the names of each of the columns or fields. In Google Sheets, those things are on the right side of your screen. You'll see the filters option filters and filters. This allows you to look more in depth at a particular particular field allowing you to select specific options and not other options. You'll find that you'll drag these different fields, either to rows or columns, or maybe both. And then values is where the magic happens. This is where it quickly calculates counts or percent totals or averages, things like that. And it's all pretty much just a drag and drop functionality. In the moment, I'm going to walk you through two guided practice examples that will follow the basic steps as outlined here on this slide. Don't worry about trying to think to remember these things in your mind. They're just really here for reference if you want to go back later. The first pivot table we'll create will allow us to understand a bit about who completed the survey based on their year in school. It's often a good idea to do this type of quick assessment so you can know a little bit about who filled out the survey. And then you can then compare these breakdowns to the larger campus population. This may also impact how you interpret survey results, especially if there is a large overrepresentation or underrepresentation in any given category in the survey compared to your campus population. In the second pivot table, we're going to look at library satisfaction rating that was calculated on a scale of one to 10, with one being low and 10 being high, with respect to a student's admission status to college and the college that they're enrolled in. And this time we'll use means or average ratings to look at library satisfaction in each of these different combinations. So let's go ahead and open that Excel file backup and then click on the tab for survey data. So to insert, to create a pivot table, it's super easy. Just click in the upper left corner of your screen. Or you can do control a to select all. So everything in the survey is now highlighted. You simply go to insert and pivot table. Select a range or table. This is selecting everything in our survey. That's what we want. So I select OK. And that pops us up to this screen that looks like this. So we're over here on the right. This is what we're just looking at in the slide presentation. So remember, we were interested in knowing a little bit about who completed the survey, the year in school. So we can simply click on year in school with my left mouse key. Let's drag that down to rows. And you can see here, just going to make this a little bit bigger. You can see here we have the different years in schools noted as role labels. If really you wanted to put them in columns, you could just drag year in school over to columns and now it looks like this. Either way, we'll get you the same results. Now I'm going to go back into the pivot table fields and take year in school and drag that same thing down into values. And so here you can see it's counting. It's just counting how many respondents were in each category. And that might be helpful. And I'm going to pull it down one more time into values, which is just to repeat of the first thing we did. But this time maybe I want the percent total. Let's go ahead and do that. I'm just going to click in on count of them year in school too. And I'm going to right click and that brings up a pop up box. And then I say show values as I'm going to hover down and say percent of column total. So what we're looking at here is 289 freshmen completed the survey, which equates to 15.7% of all the survey participants. Now I'm looking at my role labels here. Maybe I don't want this blank, this thing that says blank, that's meaningless to us. We could filter that out. I'm just going to right click or click and then right click. And then I'm going to say filter and hide this. Just is cluttering things up for me. And then the no responses. There's really nothing we can do with that information. So I'm going to delete that as well or filter it out. So that all looks pretty good. And we do have these others. I wouldn't know what to do with the others. If I was doing analysis, they're probably people who are working on certificate or poach post back types of programs. So these things really that you see here are all really functions running in the background. If we wanted to save this kind of turn it into static information. In case we wanted to use it in use it again in some other way, like in a presentation, you could just highlight everything. Copy it. You could put it in a new worksheet. You could put it in a report. All kinds of things. I'm just going to paste it as values that way we're not having the equations running in the background. We're just have the actual numbers here. We lost a little bit of our formatting, but that's no big deal. Like these percent totals look kind of messy now we can just turn those into percents with a click of a button just highlighted everything. Then up here. I'm going to just click on this percent. If I wanted it to have one decimal point. I could just use that little shortcut. And now we have that same data is just static not formulas. I'm going to go ahead and delete that for now to accomplish it. Let me try that again to accomplish accomplish this task in Google Sheets very, very similar. And let me see if I can find my way I have a sample Google Sheet data. Just exactly the same in a mirror copy. So to do a Google or a pivot table here. I'm going to click in that upper left corner to highlight everything. I'm going to go to insert on the drop down. I'm going to select pivot table. It's going to go into a new sheet. That's what we want it to do. So we are going to do that same exercise we just did. We're going to take your in school and put it under rows. We're just dragging. You're in school under values. I'm going to do you're in school under values again. Whoops. I missed something there. Let's see. I'm going to turn this into percent totals. This time instead of clicking on the. In the pivot. The pivot table. We're going to over here under values show as default. We're going to change that to percent of column. And so there we have that. And then let's say I didn't want these no responses in there. To filter them out in Google Sheets. We would drag your and school down under filters. Then I'm going to click to see what those are. And I am just going to deselect no response. And so now we have a pivot table that looks very much like our Excel file. We have a table. I'm going to pop back over to our Excel file. And so here you go. Let's do one more pivot table so you can kind of get the gist of things. We can, you could create a whole new sheet. And create a whole new pivot table if you wanted to keep this one intact. Or you could just overwrite it, which is what we're going to do here. I am going to. Deselect. That year in school that was we'd used for all of the other things. This time. Remember, we're going to look at the library satisfaction rating. That was a scale of one to 10. Based on admission status and college. So let's see. Let's go ahead first with admission status. Let's put admission status in the rows. Let's just have some fun and put college over here in columns. So now you can see what we're working with. We have. We have our, our year. Our admission status and then colleges going across the top. This time we're not necessarily counting the people who fits these categories, but the average responses to average satisfaction rating. So let's look for that up here in our pivot table field. I see library satisfaction. We're going to pull that down under values. We are actually seeing the counts here of how many people fit each category, which is helpful to scan. You can see some of these categories had very few, few people. While others had a lot. We're not really interested in the counts here. This time we're looking more for the average or the mean. So we're going to under here in values, we're going to just click the downward arrow. We're going to say value field settings. And then we're going to select the average. And then let's go ahead and take care of the number format while we're here. I'm going to click number format. And maybe I want my averages to have one decimal point. And I'm going to say, okay, and okay. And so now you can see the average library satisfaction rating for all of these different combinations of students. And once again, we have blank. I don't really want to look at the blanks. I'm going to filter them out. I'm going to just, I'm clicking on that and saying filter hide. Let's see what else we have here. We got these no responses. I'm not very interested in those either. Let's go ahead. I'm just left clicking then right clicking filter hide. Let's see. I'm going to smash some of this together so we can see it all on one screen. All right. So one fun thing to do, if you just kind of wanted to see where the high scores are or the low scores. This not isn't necessarily a pivot table function, but it works. It's you can highlight all of the values here. Then you can go to we're under home. Conditional formatting. We can give them all color scales. So I'm just hovering over color scales and I'm going to select this first option, the green, white color scale. So this shows us where the darker colors are or where they're higher values, where the lighter or white colors are lower values. So we can see that in general, transfer students in the college of engineering are less satisfied with the library than some of the other categories. So that's just a fun way to take a quick glance and look for trends. Now let's go back and do that all again in Google Sheets. It'll only take us a moment or two if I can find my way back to where we need to be. Not there. Oh, here we go. This time rather than overwriting our pivot table, maybe we just want to insert a whole new one. So I'm going to go into the upper left corner of our survey data, insert pivot table. So now we have another pivot table. We could actually call this library satisfaction pivot if you think you're going to want to return to it again. So just like we did before, let's take year in school down to Rose and college over the columns. And let's do, we're looking for library satisfaction. Let's pull library satisfaction down to values. And then we're going to want to filter out like the no responses and things like that. So we're going to pull down those same fields year in school. Or actually we did admit status last time, didn't we? Sorry, I'm just going to get rid of all of that. We're going to start over. We've got admit status, college under columns, library satisfaction under values. And then I'm going to put admit status down here as a filtering option and college as a filtering option. So right now we're just looking at counts of things just like before. But under values, let's change this to average. Just clicking on that downward carrot by some and selecting average instead. And the formatting of that is kind of wild and crazy. So I am just going to use this decreased decimal points. Well, maybe I'm not going to do that. I'm just going to hover over everything and go to format number. And just select number here. Now if I wanted to decrease the decimal point, we could do that using that shortcut. So that all looks pretty good except we do have some information maybe that we're less than interested in. Maybe it's mostly these blanks. We don't want to have that cluttering up our information. Blanks under admit status. So under filters, I'm going to go to admit status. And I'm going to deselect blanks. And then let's get rid of the no responses under college to under filters. I'm going to just look for no response. And say okay. So now you can see our results should mirror that which we just did in Excel. And it does. I'm going to stop there with the with the demonstrations. So while Excel and Google Sheets will allow you to conduct inferential statistics, things like those t tests and analysis or novice and regression analysis. I really recommend one of the more robust statistical tools like SPSS, SAS or R. They will allow you to be more confident in your results and to conduct some other tests to check for strength of findings, things like effect sizes. And to conduct post hoc or after the fact analysis to dig a little deeper and understand your findings better. So Excel is really good. And I usually use Excel when I'm getting started. And then if I'm doing something more in depth, then I will then move into a stat package. So I use both. So Excel is really good for cleaning and data management and preprocessing and doing some of those basic descriptive stats. Your step packages can also do those things for you too. But Excel is really great for that cleaning, data management and preprocessing. So I've just highlighted a few of the most common statistical packages that I hear folks talk about. These are ones that I have personal experience with. And if you plan to let's see, I've lost my train of thought here. So among the more common are these three tools here. These ones, your universities or colleges may typically have site licenses for. If you have to purchase SPSS and SAS on your own, the license can be pretty expensive. But like I said, universities usually have at least one of these options because students need to learn it as well. And the researchers need to use them. R is an open source tool. So you don't need a license for that. Overall, a lot of people will say the SPSS is the easiest to use. Sometimes though, SPSS can take a bit of time processing large data sets. It can do it. But sometimes, you know, it has to do it. People tend to like SAS or SAS for the stability and reliability, and that it handles large data sets well. Both SPSS and SAS have pretty user friendly interfaces. R is very robust. And like I said, it's open source. So that means you don't have to worry about buying a license. Though there is kind of a steep learning curve. When you're just getting started, it requires a little bit of programming. But it has a very robust and large range of functionalities. Of course, there are many other different stat products as well. Things like Python, Stata and MATLAB. And I will let you know that people tend to have their favorite program and they stand by it for a long time. I tend to gravitate towards SPSS. And that's mostly because I'm familiar with it, the layout. And I've been using it for decades. Others will stand their ground for their favorite program and consistently sing the praises of their own tools that they they prefer. Any one of these tools can do most anything that you want to do. Any one of these tools can do most anything that you're probably going to want to be doing related to library work, a library researcher assessment. So let's recap a little bit. In this presentation, we talked about descriptive and inferential statistics. Excel and Google Sheets are really powerful and easy to use for computing those descriptive statistics that describe your data set. We worked through some basic computations in Excel with some student grades just to warm you up or serve as a refresher. And then we took a large data set and created pivot tables that allowed us to quickly summarize and do some basic analyses. And finally, I recommend building fluency in both Excel or Google Sheets. And if you plan to dip your toes into inferential stats, making predictions, testing hypotheses, then I also recommend trying out one of those robust, more robust statistical packages like we talked about on the previous slide. That completes this presentation, analyzing quantitative data. I hope you found the content helpful. And I encourage you to complete this brief survey. Thank you.