1 00:00:00,05 --> 00:00:01,08 - [Instructor] Okay, so let's create 2 00:00:01,08 --> 00:00:04,00 our Gantt chart dashboard. 3 00:00:04,00 --> 00:00:07,00 So I actually added something extra in here, 4 00:00:07,00 --> 00:00:09,00 the status report, 5 00:00:09,00 --> 00:00:12,05 and this is going to change our formula just a little bit, 6 00:00:12,05 --> 00:00:14,02 so just follow along with me, 7 00:00:14,02 --> 00:00:16,01 but we're also going to gain some interactivity. 8 00:00:16,01 --> 00:00:18,08 So what I'm going to do is I'm going to type in, 9 00:00:18,08 --> 00:00:20,01 starting in cell D10, 10 00:00:20,01 --> 00:00:21,09 I'm going to type in a let. 11 00:00:21,09 --> 00:00:23,06 So let allows you 12 00:00:23,06 --> 00:00:25,08 to create a formula 13 00:00:25,08 --> 00:00:29,02 and then give it a variable name within that formula 14 00:00:29,02 --> 00:00:32,04 and you get to keep doing that except for the last line 15 00:00:32,04 --> 00:00:33,02 of the let is 16 00:00:33,02 --> 00:00:36,04 whatever you want this entire formula to return. 17 00:00:36,04 --> 00:00:38,09 So if that doesn't quite make sense, just follow along, 18 00:00:38,09 --> 00:00:40,05 don't worry, it'll all come together. 19 00:00:40,05 --> 00:00:42,01 So I'm going to type in equals let. 20 00:00:42,01 --> 00:00:46,01 Then I'm going to do a Alt+Enter on my keyboard. 21 00:00:46,01 --> 00:00:47,04 That puts me at the next line, 22 00:00:47,04 --> 00:00:49,08 one, two, three, four to indent. 23 00:00:49,08 --> 00:00:52,05 And I'm going to type in IsBetween, that's going to be the name 24 00:00:52,05 --> 00:00:53,07 of my variable, 25 00:00:53,07 --> 00:00:56,08 and in this case it's going to be and. 26 00:00:56,08 --> 00:00:57,09 We're going to use the and. 27 00:00:57,09 --> 00:01:04,03 We're going to test whether this D8 is greater than 28 00:01:04,03 --> 00:01:06,07 and less than the start date and the end date. 29 00:01:06,07 --> 00:01:09,07 So as long as we're in that region, we should be good. 30 00:01:09,07 --> 00:01:12,01 So in this case, we're going to test if D8. 31 00:01:12,01 --> 00:01:13,01 And I just want to make sure. 32 00:01:13,01 --> 00:01:14,07 I'm going to move this out of the way real quick. 33 00:01:14,07 --> 00:01:15,07 I just want to make sure here 34 00:01:15,07 --> 00:01:18,01 that I'm thinking about this correctly. 35 00:01:18,01 --> 00:01:20,00 So we want the D to vary, 36 00:01:20,00 --> 00:01:21,03 but we don't want the eight to vary. 37 00:01:21,03 --> 00:01:22,04 So this looks good. 38 00:01:22,04 --> 00:01:24,00 It is locked in correctly. 39 00:01:24,00 --> 00:01:27,05 This needs to be greater than or equal to the start date. 40 00:01:27,05 --> 00:01:30,06 In this case, I'm going to hit F4 three times. 41 00:01:30,06 --> 00:01:33,04 So we want the AO to lock in, 42 00:01:33,04 --> 00:01:35,05 but we want the drag down to be 43 00:01:35,05 --> 00:01:37,03 for the cell to follow with it. 44 00:01:37,03 --> 00:01:38,08 Alright, we're going to do this again. 45 00:01:38,08 --> 00:01:42,02 I'll click D8, one, two. 46 00:01:42,02 --> 00:01:48,06 And we want that to be less than or equal to the end date. 47 00:01:48,06 --> 00:01:51,07 And I'll hit that one, two, three. 48 00:01:51,07 --> 00:01:54,04 Okay, so here's how we're going to do this. 49 00:01:54,04 --> 00:01:56,08 Next, we're going to use what I call a project code. 50 00:01:56,08 --> 00:01:59,01 And this is super easy, okay? 51 00:01:59,01 --> 00:02:00,03 So it's going to work like this. 52 00:02:00,03 --> 00:02:02,01 So I typed in project code 53 00:02:02,01 --> 00:02:04,00 and then I'm going to hit Alt+Enter just 54 00:02:04,00 --> 00:02:07,03 to give myself a little bit more space to type this up. 55 00:02:07,03 --> 00:02:08,06 So I went to the next line 56 00:02:08,06 --> 00:02:10,05 and then I indented another four spaces. 57 00:02:10,05 --> 00:02:13,08 So in this case, I'm going to use an ifs like this. 58 00:02:13,08 --> 00:02:16,01 So what we're going to do is we're going to test 59 00:02:16,01 --> 00:02:22,07 if this here, and again, we want it to drag down. 60 00:02:22,07 --> 00:02:25,09 So I'll hit F4 until it looks like that. 61 00:02:25,09 --> 00:02:28,09 So I'm going to say if this is equal to completed, 62 00:02:28,09 --> 00:02:33,02 like that, comma, return a three. 63 00:02:33,02 --> 00:02:34,06 So we're going to do another comma. 64 00:02:34,06 --> 00:02:39,01 I'll hit Command+Enter again. 65 00:02:39,01 --> 00:02:40,09 And again, We're going to test this. 66 00:02:40,09 --> 00:02:42,02 So if this here, 67 00:02:42,02 --> 00:02:45,05 and make sure that you are following your dollar signs 68 00:02:45,05 --> 00:02:47,01 so everything is aligned. 69 00:02:47,01 --> 00:02:49,06 If this is equal to ongoing, 70 00:02:49,06 --> 00:02:51,08 we're going to give it a two like that. 71 00:02:51,08 --> 00:02:54,02 Then we'll do another Alt+Enter. 72 00:02:54,02 --> 00:02:56,09 Same deal. Okay? 73 00:02:56,09 --> 00:02:59,05 And you can just type it in if you want this time. 74 00:02:59,05 --> 00:03:03,09 If this is equal to not started, 75 00:03:03,09 --> 00:03:07,01 that's going to become a one. 76 00:03:07,01 --> 00:03:09,09 And then I'll do another Command+Enter. 77 00:03:09,09 --> 00:03:13,05 And we're just going to close this whole thing off. 78 00:03:13,05 --> 00:03:19,02 So put a comma there and I'll give us more room like that. 79 00:03:19,02 --> 00:03:21,06 And finally we'll do another Command+Enter. 80 00:03:21,06 --> 00:03:23,09 Okay, so how do we get the final result? 81 00:03:23,09 --> 00:03:25,01 Well, it's really easy. 82 00:03:25,01 --> 00:03:27,06 What we do is we take project code 83 00:03:27,06 --> 00:03:32,07 like this and we multiply it by is between, okay? 84 00:03:32,07 --> 00:03:33,08 So just think about this. 85 00:03:33,08 --> 00:03:38,01 If it's not between, this becomes a zero or a false. 86 00:03:38,01 --> 00:03:40,06 And in Excel, ones are treated as trues 87 00:03:40,06 --> 00:03:43,02 and trues are treated as ones and zeros are treated 88 00:03:43,02 --> 00:03:46,00 as falses and falses are treated as zeros. 89 00:03:46,00 --> 00:03:47,03 So all we have to do is 90 00:03:47,03 --> 00:03:49,06 take whatever project code comes back 91 00:03:49,06 --> 00:03:51,06 and multiply it by is between. 92 00:03:51,06 --> 00:03:54,09 And this case, because we're not assigning a variable to it, 93 00:03:54,09 --> 00:03:57,09 this is our last formula in this entire let, 94 00:03:57,09 --> 00:04:02,04 then all we have to do is close the parentheses like that 95 00:04:02,04 --> 00:04:05,00 and you'll see that it works. 96 00:04:05,00 --> 00:04:06,08 So look at our beautiful formula. 97 00:04:06,08 --> 00:04:07,07 That's all you need. 98 00:04:07,07 --> 00:04:09,06 Okay, I'm going to take this here 99 00:04:09,06 --> 00:04:11,05 and I'm going to double click it. 100 00:04:11,05 --> 00:04:15,09 Or actually I'm just going to drag it down like that. 101 00:04:15,09 --> 00:04:17,06 Okay, so now that I've done that, 102 00:04:17,06 --> 00:04:21,04 I can actually assign different colors to each of these. 103 00:04:21,04 --> 00:04:22,05 So we can use a little color 104 00:04:22,05 --> 00:04:23,09 in coding to understand 105 00:04:23,09 --> 00:04:26,02 where our projects are in terms of status. 106 00:04:26,02 --> 00:04:28,06 So what I'll do is I'll have 107 00:04:28,06 --> 00:04:29,07 this whole thing highlighted here. 108 00:04:29,07 --> 00:04:31,02 I'm going to create a new rule. 109 00:04:31,02 --> 00:04:33,07 In this case, I'm just going to use format only cells 110 00:04:33,07 --> 00:04:36,08 that contain, we're going to give it an equal to 111 00:04:36,08 --> 00:04:40,02 if it's a three, if it's good, it's completed, 112 00:04:40,02 --> 00:04:41,07 let's give it a green 113 00:04:41,07 --> 00:04:44,05 because we don't really have to care that much about it. 114 00:04:44,05 --> 00:04:46,01 So I'll hit okay on that 115 00:04:46,01 --> 00:04:48,01 and I'm just going to keep adding new rules. 116 00:04:48,01 --> 00:04:49,07 So I'll hit new rule here. 117 00:04:49,07 --> 00:04:54,02 Format only cells that contain equal to a two. 118 00:04:54,02 --> 00:04:55,02 We'll go to format here. 119 00:04:55,02 --> 00:04:57,04 I'll give that a yellow. 120 00:04:57,04 --> 00:04:58,06 I don't like this yellow here. 121 00:04:58,06 --> 00:04:59,07 So I'm going to go to more colors. 122 00:04:59,07 --> 00:05:02,07 We're going to give this a better yellow, like a lighter, 123 00:05:02,07 --> 00:05:04,05 more pleasant yellow. 124 00:05:04,05 --> 00:05:06,00 Okay, so I'm going to do that. 125 00:05:06,00 --> 00:05:07,09 Hit okay. 126 00:05:07,09 --> 00:05:09,05 Hit okay again. 127 00:05:09,05 --> 00:05:12,06 And then finally the ones that aren't started. 128 00:05:12,06 --> 00:05:16,01 Perhaps we should make those red. 129 00:05:16,01 --> 00:05:18,05 Just as an idea. 130 00:05:18,05 --> 00:05:20,02 Of course you can make them whatever you want. 131 00:05:20,02 --> 00:05:21,03 It really doesn't matter. 132 00:05:21,03 --> 00:05:23,06 So we want to assign these to a one. 133 00:05:23,06 --> 00:05:26,01 I'm going to make those kind of a peach color like that. 134 00:05:26,01 --> 00:05:27,00 I'll hit okay. 135 00:05:27,00 --> 00:05:29,04 Now we're called to get rid of all the numbers in here. 136 00:05:29,04 --> 00:05:31,01 We click on this number pop out here. 137 00:05:31,01 --> 00:05:32,05 We go to custom. 138 00:05:32,05 --> 00:05:38,03 And where it says general, we change that to one semicolon. 139 00:05:38,03 --> 00:05:43,04 Now at this point, you can set this up however you want. 140 00:05:43,04 --> 00:05:47,03 Like I said, I do vary my grid lines quite a bit depending 141 00:05:47,03 --> 00:05:49,08 on when I'm making it and how I feel artistically. 142 00:05:49,08 --> 00:05:50,09 So I'm going to go to view 143 00:05:50,09 --> 00:05:52,03 and just turn off the grid lines here. 144 00:05:52,03 --> 00:05:53,05 And then the degrees 145 00:05:53,05 --> 00:05:55,08 to which you want grid lines in this thing is up to you. 146 00:05:55,08 --> 00:05:58,01 So I'm just going to make it simple on us here 147 00:05:58,01 --> 00:06:01,09 and I'm just going to make it a square pattern like that. 148 00:06:01,09 --> 00:06:03,08 Now, some final things I want to do here is 149 00:06:03,08 --> 00:06:06,02 before I close this part out, 150 00:06:06,02 --> 00:06:08,04 up top I would like to add in some metrics. 151 00:06:08,04 --> 00:06:12,02 So in cell E1 through J1, I'm going to make 152 00:06:12,02 --> 00:06:13,09 that emergent center here, 153 00:06:13,09 --> 00:06:17,01 and I'm going to call that average SPI like that. 154 00:06:17,01 --> 00:06:18,05 I'll hit Control+C. 155 00:06:18,05 --> 00:06:21,00 I'm going to move over one. 156 00:06:21,00 --> 00:06:25,09 I'm going to call this average CPI over here like that 157 00:06:25,09 --> 00:06:30,06 and Control+C on that one. 158 00:06:30,06 --> 00:06:31,04 Move this over. 159 00:06:31,04 --> 00:06:36,05 I'm going to write total safety count. 160 00:06:36,05 --> 00:06:39,00 Now I'm going to hold control and highlight all of these. 161 00:06:39,00 --> 00:06:42,00 Give them kind of a nice peach background. 162 00:06:42,00 --> 00:06:45,01 And then I'm going to highlight these areas under them. 163 00:06:45,01 --> 00:06:47,00 So I'll do emergent center there 164 00:06:47,00 --> 00:06:49,07 and also add a border around that. 165 00:06:49,07 --> 00:06:52,08 And what I would like to do in this formula bar 166 00:06:52,08 --> 00:06:56,06 is I'll type in equals average. 167 00:06:56,06 --> 00:06:59,05 And I will go back to project data. 168 00:06:59,05 --> 00:07:01,04 Over it says SPI over here, 169 00:07:01,04 --> 00:07:03,05 highlight that whole thing. 170 00:07:03,05 --> 00:07:05,00 Now I highlighted that whole thing, 171 00:07:05,00 --> 00:07:07,00 but I don't like how it referenced that. 172 00:07:07,00 --> 00:07:08,08 So I'm actually going to open this back up. 173 00:07:08,08 --> 00:07:13,01 Project data, type that in, open square bracket. 174 00:07:13,01 --> 00:07:17,07 In this case, we are going to use the SPI like that. 175 00:07:17,07 --> 00:07:19,05 I like that a lot more. 176 00:07:19,05 --> 00:07:22,05 Okay, make that centered, make it bold, 177 00:07:22,05 --> 00:07:24,03 and make it a number. 178 00:07:24,03 --> 00:07:26,05 Make it bigger like that. 179 00:07:26,05 --> 00:07:29,06 We're going to do the same thing over here. 180 00:07:29,06 --> 00:07:32,05 I'll highlight that emergent center 181 00:07:32,05 --> 00:07:35,05 and then I'm going to do average. 182 00:07:35,05 --> 00:07:37,07 I'm going to say project data 183 00:07:37,07 --> 00:07:40,03 like that, open square bracket, 184 00:07:40,03 --> 00:07:45,09 and we're going to do the CPI in here, close. 185 00:07:45,09 --> 00:07:48,05 I'm just going to click on this here and do the format painter. 186 00:07:48,05 --> 00:07:49,03 Click that. 187 00:07:49,03 --> 00:07:50,03 Click that there. 188 00:07:50,03 --> 00:07:54,04 Finally, let's get the total safety count. 189 00:07:54,04 --> 00:07:56,05 So I'm going to do an equals here. 190 00:07:56,05 --> 00:08:02,09 And I'm going to do a count 191 00:08:02,09 --> 00:08:05,07 and I'll go to our project data. 192 00:08:05,07 --> 00:08:08,03 I'm just going to type it in here, left square bracket, 193 00:08:08,03 --> 00:08:10,06 and then we'll do safety incident counts. 194 00:08:10,06 --> 00:08:13,00 Close that up. 195 00:08:13,00 --> 00:08:14,07 Make this smaller. 196 00:08:14,07 --> 00:08:16,05 I actually think I wrote count all, 197 00:08:16,05 --> 00:08:18,00 but actually this should be a sum. 198 00:08:18,00 --> 00:08:20,08 So let's change that to sum like that. 199 00:08:20,08 --> 00:08:23,06 All right, so now we have a nice Gantt chart. 200 00:08:23,06 --> 00:08:25,09 We have some reporting metrics about our projects, 201 00:08:25,09 --> 00:08:27,09 and in the final video 202 00:08:27,09 --> 00:08:29,04 we're going to do some scenario analysis 203 00:08:29,04 --> 00:08:30,03 and I'm going to show you how 204 00:08:30,03 --> 00:08:34,00 to add some additional interactivity to your pivot table.