1 00:00:00,510 --> 00:00:01,650 - [Instructor] So let's talk about 2 2 00:00:01,650 --> 00:00:04,230 how to find high-level descriptive statistics 3 3 00:00:04,230 --> 00:00:05,910 for our project data. 4 4 00:00:05,910 --> 00:00:07,117 Now, you may be wondering, 5 5 00:00:07,117 --> 00:00:08,820 "What is a descriptive statistic?" 6 6 00:00:08,820 --> 00:00:10,430 Well, descriptive statistics are anything 7 7 00:00:10,430 --> 00:00:13,76 that takes a large column of numbers, 8 8 00:00:13,76 --> 00:00:14,754 and then turns it into a few numbers. 9 9 00:00:14,754 --> 00:00:18,641 So, think sum, min, max, count and average. 10 10 00:00:18,641 --> 00:00:21,690 Those are our classical descriptive statistic numbers, 11 11 00:00:21,690 --> 00:00:24,990 and ones perfect for using with pivot tables. 12 12 00:00:24,990 --> 00:00:27,660 So, first step is whenever we have project data like this, 13 13 00:00:27,660 --> 00:00:29,130 we always want to turn it into a table. 14 14 00:00:29,130 --> 00:00:32,250 So I'm going to hit Control T here to create a Control T table. 15 15 00:00:32,250 --> 00:00:34,380 Make sure your cursor is within that table region 16 16 00:00:34,380 --> 00:00:35,850 for this to work automatically. 17 17 00:00:35,850 --> 00:00:37,650 Make sure that your table has headers, 18 18 00:00:37,650 --> 00:00:39,30 then you can either hit the Enter key, 19 19 00:00:39,30 --> 00:00:43,50 which I'm going to do, or press OK. 20 20 00:00:43,50 --> 00:00:45,80 Next thing is, I got to get rid of those banded rows. 21 21 00:00:45,80 --> 00:00:46,860 It's just a personal thing. 22 22 00:00:46,860 --> 00:00:49,500 Now, under Table Name, I'm going to call this ProjectData 23 23 00:00:49,500 --> 00:00:51,240 just to keep us organized. 24 24 00:00:51,240 --> 00:00:54,60 And I want to add two different metrics to this. 25 25 00:00:54,60 --> 00:00:56,850 So the first metric I want to add is the budget delta. 26 26 00:00:56,850 --> 00:00:58,470 So you can see in Column E, 27 27 00:00:58,470 --> 00:01:00,960 we have the Budget, Column F, we have the Expenses. 28 28 00:01:00,960 --> 00:01:02,622 You can right-click on G and hit Insert. 29 29 00:01:02,622 --> 00:01:04,648 This is going to create a new column, 30 30 00:01:04,648 --> 00:01:08,820 and in this case, we're going to call it Budget Delta. 31 31 00:01:08,820 --> 00:01:11,125 Hit Enter on that, then you can put in equals 32 32 00:01:11,125 --> 00:01:12,430 in this first cell. 33 33 00:01:12,430 --> 00:01:17,130 And in this case, we're just going to do Budget less Expenses. 34 34 00:01:17,130 --> 00:01:18,150 Simple, right? 35 35 00:01:18,150 --> 00:01:19,50 Hit Enter. 36 36 00:01:19,50 --> 00:01:20,310 We have that right here. 37 37 00:01:20,310 --> 00:01:22,800 And then we also want to get the project length. 38 38 00:01:22,800 --> 00:01:25,809 So, in Column K, starting in Row 1, 39 39 00:01:25,809 --> 00:01:31,290 I'm going to type in Project Length like that. 40 40 00:01:31,290 --> 00:01:32,430 Very similar here, right? 41 41 00:01:32,430 --> 00:01:36,330 We want to take the end date, less the start date, 42 42 00:01:36,330 --> 00:01:38,130 and that's going to give us our duration. 43 43 00:01:38,130 --> 00:01:39,510 All right, so once we've done that, 44 44 00:01:39,510 --> 00:01:41,250 we're ready to create a pivot table. 45 45 00:01:41,250 --> 00:01:44,202 Pivot tables love these special columns we've created, 46 46 00:01:44,202 --> 00:01:47,10 so we're not just stuck using static data. 47 47 00:01:47,10 --> 00:01:49,230 The columns we create also go into the pivot table. 48 48 00:01:49,230 --> 00:01:50,940 So what I'm going to do is I'm going to go to Insert 49 49 00:01:50,940 --> 00:01:52,590 and click Pivot Table. 50 50 00:01:52,590 --> 00:01:53,730 In this top box here, 51 51 00:01:53,730 --> 00:01:55,266 you can see it's automatically populated 52 52 00:01:55,266 --> 00:01:57,30 with our table of data. 53 53 00:01:57,30 --> 00:01:58,200 We're cool with a new worksheet, 54 54 00:01:58,200 --> 00:02:00,210 so just go ahead and hit OK. 55 55 00:02:00,210 --> 00:02:03,660 And then I'm going to zoom out just a little bit here. 56 56 00:02:03,660 --> 00:02:06,390 So now that we've created our pivot table, 57 57 00:02:06,390 --> 00:02:10,258 let's see if we can actually understand the budget deltas, 58 58 00:02:10,258 --> 00:02:15,600 and the project lengths in terms of status and by person. 59 59 00:02:15,600 --> 00:02:18,720 So, first thing I'm going to do is find Status on here. 60 60 00:02:18,720 --> 00:02:21,450 I'm going to drag it all the way down to rows over here. 61 61 00:02:21,450 --> 00:02:24,30 So that's our statuses, 62 62 00:02:24,30 --> 00:02:29,640 and then I'm going to drop in the Assigned To here. 63 63 00:02:29,640 --> 00:02:32,10 Now let's look for our special measures that we created. 64 64 00:02:32,10 --> 00:02:32,880 We have Budget Delta. 65 65 00:02:32,880 --> 00:02:34,500 I'm going to drop that in right there. 66 66 00:02:34,500 --> 00:02:38,640 So that is the sum of our budget delta, 67 67 00:02:38,640 --> 00:02:42,540 and next, we're going to drop Project Length here. 68 68 00:02:42,540 --> 00:02:44,220 So that's the sum of the project lengths. 69 69 00:02:44,220 --> 00:02:45,780 Perhaps it doesn't make sense 70 70 00:02:45,780 --> 00:02:47,580 to have some for both of these. 71 71 00:02:47,580 --> 00:02:49,620 So, let's look at Budget Delta here. 72 72 00:02:49,620 --> 00:02:50,519 I'm going to click on this dropdown. 73 73 00:02:50,519 --> 00:02:53,10 I'm going to click Value Field Settings here. 74 74 00:02:53,10 --> 00:02:58,560 And in this case, I want to get the minimum 75 75 00:02:58,560 --> 00:03:00,450 just for my knowledge. 76 76 00:03:00,450 --> 00:03:02,760 So, this is the smallest budget delta. 77 77 00:03:02,760 --> 00:03:04,200 We can see that some of these are negative, 78 78 00:03:04,200 --> 00:03:06,630 so that's not necessarily good. 79 79 00:03:06,630 --> 00:03:08,520 Next, let's take a look at Project Length. 80 80 00:03:08,520 --> 00:03:10,20 I'm going to click on this down arrow here. 81 81 00:03:10,20 --> 00:03:11,340 I'm going to click Value Field Settings. 82 82 00:03:11,340 --> 00:03:14,70 I'm going to change this to Max. 83 83 00:03:14,70 --> 00:03:15,240 Now that you see how to create 84 84 00:03:15,240 --> 00:03:18,197 high-level descriptive statistics using pivot tables, 85 85 00:03:18,197 --> 00:03:20,700 in the next video, we're going to talk about progress charts, 86 86 00:03:20,700 --> 00:03:22,230 so you can actually measure 87 87 00:03:22,230 --> 00:03:23,462 how well you are doing, 88 88 00:03:23,462 --> 00:03:25,00 and see it visually.