- [Instructor] Okay, so let's build out
our last features of our dashboard
so that when people ask us questions about it,
we can actually give them answers,
and we can change how things look on the dashboard,
and give the ability for people to interact
and to really understand the data.
So the first thing we want to do for our scenario analysis
is I have selected the data in AO through AQ,
so I'll hit Control + C there.
Over here I'm going to do a paste value,
so we're going to paste to complete.
I probably did want to paste, not just the values,
but also the number formatting with it.
Okay, so one of these here
is going to be our scenario,
one of them is going to be our actuals.
So I'm going to type I...
I'm going to type in "SCENARIO" above here,
again I did a merge and center.
If you don't like merge and center,
okay do center across, that's your life,
I won't judge you, okay?
But I do like merge and center, I'm sorry,
I'm sorry, guys.
I'm going to do Control + C here
and then I'm going to do Control + V here,
but I'm going to replace the "Scenario"
with the word "Original."
And it's always a good idea
just to really pay attention to which one is which
'cause I mess it up all the time
when I do these formulas.
So one of these, we want to change,
one of them, we don't.
So in this case, I already made this mistake,
so I'm just going to keep this here.
The original needs to have
the formulas associated with it, okay?
But the scenario is the one
that we want to have as values
because we can always change it back.
Okay, so remember one of these gets hidden,
the other one gets shown.
So, we need to update the formula in here
to reflect whether we are dealing
with the original or the scenario.
But to even do that,
we have to create a mechanism to drive it.
So, what I'm going to do is
I'm going to put my cursor in cell B4,
and I'm going to go to the Data tab over here,
and under this drop down,
I'm going to select Data Validation.
Under Allow, I'm going to select List,
and then under Source,
I'm going to type in "Original,"
and then I'm going to type in "Scenario."
So, if it's on Scenario, we want it to pull from here,
if it's on Original, we want to pull from here.
Let us now go and make that update
to the formula.
So, let's pop this open,
and I'm going to show you how we do this.
Okay, so first thing is,
I'm going to do a Alt + Enter here,
to go to the next line, so that I have a new line here,
and I'm going to type in IsScenario, like that,
so comma, and then what we're going to do
is we're going to test if cell B4, over here,
and I'm going to lock it in for good measure,
and I'm going to type does that equal "Scenario".
Okay, so if that's true,
then that means we want to deal with the data
that's on the Scenario side versus the Original side.
So next, we have IsBetween here,
I'm going to modify this.
Watch how I modify this.
So I'm going to highlight this whole thing here
and I'm going to hit Control + X,
but I am going to reuse it.
So now I'm going to do a Command + Enter,
one, two, three, four,
So I'll do an IF here:
IF (IsScenario),
so if this thing is true here, we'll do a comma,
and in this case, I'm going to say, AND,
and what I want to do is I want to test
if this value here
is greater than or equal to,
and I'm going to use,
since we're doing Scenario,
we want to use this one over here.
So let's hit F4 to lock that in correctly.
Make sure that the dollar sign
is on the last part of this,
and of course, if this happens,
like something pops back up,
just hit Alt + Enter and move that out of the way.
Okay, so now we're going to hit comma on this again,
we want to test if D8,
one, two, is less than or equal to
this value right here,
and make sure to cycle through it correctly, like that.
Okay, so if that test is correct,
we'll know it's between.
Now I'm going to hit Control + V here
'cause I'm just going to reuse
the other one that we had.
Close this off right here.
Okay, so look at this, right?
If it's Scenario, we're going to pull from here.
If it's not this Scenario, we're going to pull from here.
Pretty easy, right?
Okay, so now I'm going to put ProjectCode back
in its place, but we are going to have to do
an additional test.
So, I'm going to do another Alt + Enter,
and move this over here.
And what I'm going to say is: IF (IsScenario),
do a comma here.
Now, if it's this Scenario
we want it to pull from this set of data here,
we want to evaluate AU.
So what you can do is...
I'm a big believer in just reusing stuff.
So I'm going to go one, two, three, four,
one, two, three, four, okay.
And then also I have to move these in too,
just for my own sanity.
Okay, and then let's just keep doing that.
This is if it's not this scenario.
So I'll hit Control + C here,
then I'm going to do a Command + Enter, Control + V.
Don't worry, if you're following along here,
just make sure that your formula looks like mine
at the end of the day.
But I do want to show you
how to build these things quickly
because this actually does make a difference, all right?
So if it's the scenario,
we want to be testing from AU.
So I'm going to replace the AQ here with a U, like that.
I'll just keep going in and replacing that.
And we can remove this here.
So, in this case, what I'd want to do
is I want to add another parentheses and do a comma.
Okay, so let's take a look at this.
If it IsScenario,
that means B4 = "Scenario", that becomes a true.
So, now we want to find out is it between?
So, if it is the scenario, we want to test,
pulling from the scenario start and end dates.
So, we can see AS10, AT10, that looks good
and otherwise, we want to pull from the other side.
All right, so now we want to get the project code.
So, if it's the scenario,
if we're dealing with scenario,
we want to make sure we're pulling
from the correct project codes.
Otherwise, if not, we want to pull
from a different location.
All right, so I'm going to hit Enter here,
and we'll close this up.
I'm going to take this here, drag it over here,
double click it the way down.
It shouldn't matter yet, right,
it shouldn't matter yet.
But if I go here and I change this to 4/1, like that.
Okay, so we see that updates.
Now, what if I change this to Ongoing, like that?
All right, so we see that updates,
and what you can always do here
is you can go and highlight all of this,
and go to Data, do Data Validation here, List,
you can always type these in
or put them somewhere on the spreadsheet
and do a Source to it.
I like making things easy for example purposes,
so I'm going to just keep it like this,
and of course you can just change it and so forth.
Okay, so as we make those changes,
we definitely want to see
some sort of visualization at the bottom.
So I'm going to do an =Sum here.
Remember, this is summing 3, 2, 1,
like that.
You can decide for yourself exactly what that means.
I'm going to apply a new conditional format here.
Let's see if the color scales, I like that.
So in this case
what we're really seeing is that
completed and ongoing projects
are going to have a bigger color
'cause they have a higher number encoding.
Again, you can decide if that's what you really want,
I can see arguments being the other way.
I'm actually going to do this red here,
whereas the ones that I have not started
are going to have a lesser encoding associated with them.
So I'm going to close that up here.
Now the other thing is I'm going to go
to Original over here,
and I'll hit the Data tab and click Group
just so it's not in the way.
You could of course make this smaller
if you want as well.
Okay, so last thing is,
let's hit our pivot table here.
So I just want to give you one last bit of interactivity
that is specific to pivot tables.
So, with this pivot table selected,
I can go to the Insert tab
and I have these two choices,
I have a Slicer and a Timeline.
So, let's hit the slicer first.
We can click on Incident Type here,
and this will just allow us to choose
what kind of type of incident we have.
And then the other one I want to show you here,
is I'm going to Insert,
and I'm going to click Timeline.
This only works on pivot tables.
So, you'll note that the only thing
it gives me a choice for is a date type,
because it only works on dates.
So timelines are really cool
because they allow us to slice on dates, like that.
So now, I can take a look at my different projects,
take a look at the average SPI, CPI,
and Total Safety Count.
And then I can also look
at my safety issues per project and per date.
And with that, we have taken our project management data
and we've added so much more life to it
and so much more data visualization to it.
I can't wait to see
how you're going to use this information
in your own work.