Calculating Averages and Median Values3:38 with Michael Watson
Learn how to use the AVERAGE and MEDIAN functions to compute the average and median values for a selection of spreadsheet cells.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
It's pretty cool how much information you can quickly calculate using a spreadsheet. 0:00 Previous generations had to spend a lot more time to answer questions that 0:04 spreadsheets let us answer in less than a second. 0:07 In this example spreadsheet, we're looking at a lot of different retail transactions. 0:09 And say we want to understand, what the average price of the items, 0:13 we are selling is. 0:16 You might do this to figure out if you have opportunities to change your 0:17 pricing strategies, for example. 0:20 To calculate the average price, we can use the average function which is similar to 0:22 the sum function we introduced in an earlier video. 0:26 In the average case, the syntax is very simple and similar to sum. 0:29 Let's scroll down to where we had the sum function. 0:33 And Instead of sum, we're gonna type out AVERAGE. 0:40 Then parentheticals, and then select all the values that we wanna sum. 0:47 So the average price transaction for this data set is $289.71. 0:57 The median function is basically the exact same as the average function, 1:03 except we're gonna be typing median instead of average. 1:07 Before using the median function, 1:10 let's note why sometimes an average is misleading. 1:12 Let's look at another data set to illustrate this. 1:15 We go over to the average median tab. 1:18 And this tab, we're looking at the total sales by month for a full year. 1:20 So, in January we had $500,000 worth of sales. 1:26 In August, $100,000 worth of sales, September at $90,000 worth of sales, 1:31 so forth and so on. 1:36 Let's say your boss asks you to calculate what the average monthly 1:38 sales were for the year. 1:40 January sales were much more than other months though. 1:42 In this case, the average might skew high and 1:45 not reflect the sales of most of the months. 1:47 The median is a better choice, 1:50 as it helps eliminate outline values that are extremely or low. 1:52 In this situation, you might answer your boss's question about what the average is, 1:55 and then also point out that you included median as well, 1:59 because it may be a better indicator for their underlying question. 2:01 Another option is to note that January was an outlier month, and 2:05 just remove that from your data set. 2:07 So here we've calculated the average for all the different calendar months. 2:09 And then below it, we've calculated the median. 2:15 The average is $135,833 and the median is $100,000. 2:20 So that's a significant difference. 2:24 What about the average is we remove the outlier month, as we've done here? 2:26 We removed January. 2:30 And now the average is $102,727, much closer to the median for 2:33 the entire data set. 2:38 Note that when I select a group of cells, I can get a handy snapshot of 2:40 a bunch of functions without having to write them down. 2:44 You can see it here in the bottom right of the screen. 2:48 We can see Sum, Average, Min, Max, Count, and Count Numbers. 2:52 Just something to keep in mind. 2:58 The sum is the total, the average is the average for 3:00 the cells that you've selected. 3:03 The min is the minimum value in the group of cells that you've selected. 3:04 The maximum is the maximum is the maximum 3:08 value in the group of cells you've selected. 3:10 Count is the number of cells you've selected, and 3:13 count numbers will only calculate numbers. 3:16 So if I go here and select all these and come back, you can see that count, 3:19 there's 14 cells that I've selected, but only 7 of them contain numerical values. 3:26 Now that you started using some functions, 3:32 let's see what else we can do with this simple example to gain further insights. 3:34
You need to sign up for Treehouse in order to download course files.Sign up