1 00:00:00,180 --> 00:00:04,030 It's pretty cool how much information you can quickly calculate using a spreadsheet. 2 00:00:04,030 --> 00:00:07,310 Previous generations had to spend a lot more time to answer questions that 3 00:00:07,310 --> 00:00:09,850 spreadsheets let us answer in less than a second. 4 00:00:09,850 --> 00:00:13,570 In this example spreadsheet, we're looking at a lot of different retail transactions. 5 00:00:13,570 --> 00:00:16,120 And say we want to understand, what the average price of the items, 6 00:00:16,120 --> 00:00:17,430 we are selling is. 7 00:00:17,430 --> 00:00:20,100 You might do this to figure out if you have opportunities to change your 8 00:00:20,100 --> 00:00:22,220 pricing strategies, for example. 9 00:00:22,220 --> 00:00:26,260 To calculate the average price, we can use the average function which is similar to 10 00:00:26,260 --> 00:00:29,380 the sum function we introduced in an earlier video. 11 00:00:29,380 --> 00:00:33,858 In the average case, the syntax is very simple and similar to sum. 12 00:00:33,858 --> 00:00:38,360 Let's scroll down to where we had the sum function. 13 00:00:40,520 --> 00:00:45,440 And Instead of sum, we're gonna type out AVERAGE. 14 00:00:47,690 --> 00:00:52,048 Then parentheticals, and then select all the values that we wanna sum. 15 00:00:57,064 --> 00:01:03,560 So the average price transaction for this data set is \$289.71. 16 00:01:03,560 --> 00:01:07,750 The median function is basically the exact same as the average function, 17 00:01:07,750 --> 00:01:10,950 except we're gonna be typing median instead of average. 18 00:01:10,950 --> 00:01:12,570 Before using the median function, 19 00:01:12,570 --> 00:01:15,240 let's note why sometimes an average is misleading. 20 00:01:15,240 --> 00:01:18,533 Let's look at another data set to illustrate this. 21 00:01:18,533 --> 00:01:20,866 We go over to the average median tab. 22 00:01:20,866 --> 00:01:26,660 And this tab, we're looking at the total sales by month for a full year. 23 00:01:26,660 --> 00:01:31,758 So, in January we had \$500,000 worth of sales. 24 00:01:31,758 --> 00:01:36,433 In August, \$100,000 worth of sales, September at \$90,000 worth of sales, 25 00:01:36,433 --> 00:01:38,040 so forth and so on. 26 00:01:38,040 --> 00:01:40,720 Let's say your boss asks you to calculate what the average monthly 27 00:01:40,720 --> 00:01:42,370 sales were for the year. 28 00:01:42,370 --> 00:01:45,520 January sales were much more than other months though. 29 00:01:45,520 --> 00:01:47,960 In this case, the average might skew high and 30 00:01:47,960 --> 00:01:50,290 not reflect the sales of most of the months. 31 00:01:50,290 --> 00:01:52,020 The median is a better choice, 32 00:01:52,020 --> 00:01:55,680 as it helps eliminate outline values that are extremely or low. 33 00:01:55,680 --> 00:01:59,340 In this situation, you might answer your boss's question about what the average is, 34 00:01:59,340 --> 00:02:01,690 and then also point out that you included median as well, 35 00:02:01,690 --> 00:02:05,080 because it may be a better indicator for their underlying question. 36 00:02:05,080 --> 00:02:07,720 Another option is to note that January was an outlier month, and 37 00:02:07,720 --> 00:02:09,880 just remove that from your data set. 38 00:02:09,880 --> 00:02:14,380 So here we've calculated the average for all the different calendar months. 39 00:02:15,850 --> 00:02:18,057 And then below it, we've calculated the median. 40 00:02:20,267 --> 00:02:24,190 The average is \$135,833 and the median is \$100,000. 41 00:02:24,190 --> 00:02:26,570 So that's a significant difference. 42 00:02:26,570 --> 00:02:30,960 What about the average is we remove the outlier month, as we've done here? 43 00:02:30,960 --> 00:02:32,190 We removed January. 44 00:02:33,830 --> 00:02:38,100 And now the average is \$102,727, much closer to the median for 45 00:02:38,100 --> 00:02:40,480 the entire data set. 46 00:02:40,480 --> 00:02:44,961 Note that when I select a group of cells, I can get a handy snapshot of 47 00:02:44,961 --> 00:02:48,672 a bunch of functions without having to write them down. 48 00:02:48,672 --> 00:02:50,630 You can see it here in the bottom right of the screen. 49 00:02:52,610 --> 00:02:58,570 We can see Sum, Average, Min, Max, Count, and Count Numbers. 50 00:02:58,570 --> 00:03:00,440 Just something to keep in mind. 51 00:03:00,440 --> 00:03:03,880 The sum is the total, the average is the average for 52 00:03:03,880 --> 00:03:04,910 the cells that you've selected. 53 00:03:04,910 --> 00:03:08,100 The min is the minimum value in the group of cells that you've selected. 54 00:03:08,100 --> 00:03:10,860 The maximum is the maximum is the maximum 55 00:03:10,860 --> 00:03:13,340 value in the group of cells you've selected. 56 00:03:13,340 --> 00:03:16,830 Count is the number of cells you've selected, and 57 00:03:16,830 --> 00:03:19,530 count numbers will only calculate numbers. 58 00:03:19,530 --> 00:03:26,780 So if I go here and select all these and come back, you can see that count, 59 00:03:26,780 --> 00:03:30,730 there's 14 cells that I've selected, but only 7 of them contain numerical values. 60 00:03:32,240 --> 00:03:34,650 Now that you started using some functions, 61 00:03:34,650 --> 00:03:38,330 let's see what else we can do with this simple example to gain further insights.