- [Instructor] Okay, so let's create
our Gantt chart dashboard.
So I actually added something extra in here,
the status report,
and this is going to change our formula just a little bit,
so just follow along with me,
but we're also going to gain some interactivity.
So what I'm going to do is I'm going to type in,
starting in cell D10,
I'm going to type in a let.
So let allows you
to create a formula
and then give it a variable name within that formula
and you get to keep doing that except for the last line
of the let is
whatever you want this entire formula to return.
So if that doesn't quite make sense, just follow along,
don't worry, it'll all come together.
So I'm going to type in equals let.
Then I'm going to do a Alt+Enter on my keyboard.
That puts me at the next line,
one, two, three, four to indent.
And I'm going to type in IsBetween, that's going to be the name
of my variable,
and in this case it's going to be and.
We're going to use the and.
We're going to test whether this D8 is greater than
and less than the start date and the end date.
So as long as we're in that region, we should be good.
So in this case, we're going to test if D8.
And I just want to make sure.
I'm going to move this out of the way real quick.
I just want to make sure here
that I'm thinking about this correctly.
So we want the D to vary,
but we don't want the eight to vary.
So this looks good.
It is locked in correctly.
This needs to be greater than or equal to the start date.
In this case, I'm going to hit F4 three times.
So we want the AO to lock in,
but we want the drag down to be
for the cell to follow with it.
Alright, we're going to do this again.
I'll click D8, one, two.
And we want that to be less than or equal to the end date.
And I'll hit that one, two, three.
Okay, so here's how we're going to do this.
Next, we're going to use what I call a project code.
And this is super easy, okay?
So it's going to work like this.
So I typed in project code
and then I'm going to hit Alt+Enter just
to give myself a little bit more space to type this up.
So I went to the next line
and then I indented another four spaces.
So in this case, I'm going to use an ifs like this.
So what we're going to do is we're going to test
if this here, and again, we want it to drag down.
So I'll hit F4 until it looks like that.
So I'm going to say if this is equal to completed,
like that, comma, return a three.
So we're going to do another comma.
I'll hit Command+Enter again.
And again, We're going to test this.
So if this here,
and make sure that you are following your dollar signs
so everything is aligned.
If this is equal to ongoing,
we're going to give it a two like that.
Then we'll do another Alt+Enter.
Same deal. Okay?
And you can just type it in if you want this time.
If this is equal to not started,
that's going to become a one.
And then I'll do another Command+Enter.
And we're just going to close this whole thing off.
So put a comma there and I'll give us more room like that.
And finally we'll do another Command+Enter.
Okay, so how do we get the final result?
Well, it's really easy.
What we do is we take project code
like this and we multiply it by is between, okay?
So just think about this.
If it's not between, this becomes a zero or a false.
And in Excel, ones are treated as trues
and trues are treated as ones and zeros are treated
as falses and falses are treated as zeros.
So all we have to do is
take whatever project code comes back
and multiply it by is between.
And this case, because we're not assigning a variable to it,
this is our last formula in this entire let,
then all we have to do is close the parentheses like that
and you'll see that it works.
So look at our beautiful formula.
That's all you need.
Okay, I'm going to take this here
and I'm going to double click it.
Or actually I'm just going to drag it down like that.
Okay, so now that I've done that,
I can actually assign different colors to each of these.
So we can use a little color
in coding to understand
where our projects are in terms of status.
So what I'll do is I'll have
this whole thing highlighted here.
I'm going to create a new rule.
In this case, I'm just going to use format only cells
that contain, we're going to give it an equal to
if it's a three, if it's good, it's completed,
let's give it a green
because we don't really have to care that much about it.
So I'll hit okay on that
and I'm just going to keep adding new rules.
So I'll hit new rule here.
Format only cells that contain equal to a two.
We'll go to format here.
I'll give that a yellow.
I don't like this yellow here.
So I'm going to go to more colors.
We're going to give this a better yellow, like a lighter,
more pleasant yellow.
Okay, so I'm going to do that.
Hit okay.
Hit okay again.
And then finally the ones that aren't started.
Perhaps we should make those red.
Just as an idea.
Of course you can make them whatever you want.
It really doesn't matter.
So we want to assign these to a one.
I'm going to make those kind of a peach color like that.
I'll hit okay.
Now we're called to get rid of all the numbers in here.
We click on this number pop out here.
We go to custom.
And where it says general, we change that to one semicolon.
Now at this point, you can set this up however you want.
Like I said, I do vary my grid lines quite a bit depending
on when I'm making it and how I feel artistically.
So I'm going to go to view
and just turn off the grid lines here.
And then the degrees
to which you want grid lines in this thing is up to you.
So I'm just going to make it simple on us here
and I'm just going to make it a square pattern like that.
Now, some final things I want to do here is
before I close this part out,
up top I would like to add in some metrics.
So in cell E1 through J1, I'm going to make
that emergent center here,
and I'm going to call that average SPI like that.
I'll hit Control+C.
I'm going to move over one.
I'm going to call this average CPI over here like that
and Control+C on that one.
Move this over.
I'm going to write total safety count.
Now I'm going to hold control and highlight all of these.
Give them kind of a nice peach background.
And then I'm going to highlight these areas under them.
So I'll do emergent center there
and also add a border around that.
And what I would like to do in this formula bar
is I'll type in equals average.
And I will go back to project data.
Over it says SPI over here,
highlight that whole thing.
Now I highlighted that whole thing,
but I don't like how it referenced that.
So I'm actually going to open this back up.
Project data, type that in, open square bracket.
In this case, we are going to use the SPI like that.
I like that a lot more.
Okay, make that centered, make it bold,
and make it a number.
Make it bigger like that.
We're going to do the same thing over here.
I'll highlight that emergent center
and then I'm going to do average.
I'm going to say project data
like that, open square bracket,
and we're going to do the CPI in here, close.
I'm just going to click on this here and do the format painter.
Click that.
Click that there.
Finally, let's get the total safety count.
So I'm going to do an equals here.
And I'm going to do a count
and I'll go to our project data.
I'm just going to type it in here, left square bracket,
and then we'll do safety incident counts.
Close that up.
Make this smaller.
I actually think I wrote count all,
but actually this should be a sum.
So let's change that to sum like that.
All right, so now we have a nice Gantt chart.
We have some reporting metrics about our projects,
and in the final video
we're going to do some scenario analysis
and I'm going to show you how
to add some additional interactivity to your pivot table.