Welcome to lecture number two in Module four, which is all about signalling without control charts
in the last lecture, what we looked at were some of the limitations of actually producing a physical
visualisation, a control chart as an actual chart within Excel.
But we said we can still apply some logical statements within the worksheet itself to still call attention
to the analyst or to the operations manager when signals occur.
And in this lecture, what we're going to do is build on that.
But rather than looking at data points where they exceed the upper or lower spec limit, we're going
to look at those other types of signals.
The medium sustains the medium sustain signal and the weak, sustained signal.
So let's jump on over to the Signals tab to talk about this topic.
So what we have here is the same data that we just analyzed and lecture four point one and we even have
the signals that we write.
So I haven't deleted anything yet.
But now what we're going to look at is that medium sustained signal.
And if you recall, the medium sustained signal is defined as nine data points consecutively above the
mean or below the mean.
So in this case, what we'll end up doing is creating a conditional statement, just like we did over
here and using the F function.
And then because we're so what we would do to evaluate if one data point is above the mean, we would
say.
We would say this data points number of machines down is their series is greater than the average.
And this is true.
Give us a one and if not, give us a zero.
And what it didn't like is that it put an extra privacy in there.
So here we can see that that's what's going on and any time that a data point is above the mean, we're
getting a signal.
But what we want to do is evaluate that data point, as well as the eight subsequent data points directly
behind it.
So what we'll do is we'll use a and function A and B and then do the same evaluation for each of the
data points separated with a comma in between.
So in this case, this one is greater than the average.
And then this is greater than the average.
Cetera, et cetera.
And if you just bear with me is a always in here.
We can be doing this also in the opposite direction to evaluate any time that hon.
Nine consecutive data points fall below the mean, as well as above the mean, because that signal works
both ways.
It's not just that it falls above the mean.
And there we have it, and we want to give ourselves a one, if that's true and a zero, if it's false.
And what we're going to see is we're going to see a thorough a lot of signals that don't exist.
And if we come down here, we can see I have 492 cell selected and only two of those are actually signaling
so we can come down here.
And find.
That, yep, that's actually happening.
There were actually two instances consecutively where nine consecutive data points fell above the mean.
And likewise, we could do another whole column where we looked at it below the mean and in the next
lecture, what we're actually going to do is nest each of these conditions into one column because as
you might be seeing, I've got a signal for the upper spec limit.
The lower spec limit signal, I've got a signal.
If it if nine data points fall above the mean, I could have another signal, another column for when
they fall below.
And then I could have two more for our week sustain that.
We're just about to do so.
You can see that this is getting pretty unwieldy and what we'll do in the next lecture is consolidate
these to make it a little bit more manageable, even from a worksheet standpoint.
But let's go ahead and do the upper spec limit.
So the upper spec limit is a little bit or the excuse me, the weak sustained.
The USL close is what I'm calling it.
Here is again, any time that three consecutive data points fall closer to the control limit than to
the average.
And here we're just going to evaluate that data point against the upper spec limit as a control, and
we're not going to do both.
So how we would do this is we would say if and then and because we're going to evaluate three consecutive
data points and then that first one, I'm going to say if the distance between the upper spec limit
and the number of machines down is less than.
The distance between the average and the number of machines down.
Give me a one and then give me a zero.
And we'll evaluate one for the time being.
We can see here that several times we're getting we're just evaluating one data point in this case,
but in this case, this data point fell closer to the upper spec limit and indeed it actually exceeded
the spec limit.
So it's clearly falling closer to the upper spec limit than to the average.
But we can also do this several times over for each of the four each of the.
Data points.
Previous, So in this case, same thing just for the cell behind it.
If the distance between there is less than the distance between the average and the number of machines
down.
And finally, one last time for that last one.
That is less than.
And we're all set.
And we should have no issues as we drag this down.
And see that we're getting a data point here, and in total, we have actually five instances where
this happens.
So here we can come up here and look at this data point thirty two and if I come here to.
Data point, I don't know.
Looks like right here, maybe I can, I can come over here and essentially try to map it up to the to
the chart here.
I also know I'm going to go find the other one.
I can come down here and say, Oh, looks like data point 294 or 293, something like that.
I can come over here and and surely match up this the data point here, here to 95 to 94, something
like this.
So we can see over here this is where that series is falling closer to the upper spec limit than to
the average.
Just a little quick note about some of the logic.
Here I'm using the function abs, which is the absolute value.
And this way what it would essentially does is it makes it agnostic whether or not it's positive or
negative.
I don't really care, you know, whether I just care that the distance that the magnitude between the
upper spec limit and the data point is less than the magnitude between the average and the number of
machines down.
I really don't care if it's on one side or the other, and we see here that in cases where this spec
limit is exceeded.
I would actually have a negative number because because the data points falling on the opposite side
as it is most of the time, so the absolute value just essentially assigns the magnitude to that difference.
And then we're evaluating magnitude differences between the upper spec limit and the average.
So in general, both of these can be done again one four below the mean and one for the lower spec limit.
But you can see here that now we've essentially looked at all of the signals we looked at in previous
modules, but we've done it within a worksheet and it's a lot easier to go in here and evaluate exactly
what's happening at a particular data point.
And if I might say it's a lot easier to identify signals, particularly these sustained effects, because
they don't have to count the number of data points.
And as you can see here in a series, that's that's kind of as messy as this is and has as many data
points as it does, it's gets very, very difficult to count nine data points in a row or three data
points in a row.
So this approach really ensures correctness.
It's better quality and it kind of draws our eyes with that conditional formatting that we looked at.
It draws our eyes to when the signals appear.
So for this reason, I really think it's a best practice when you're going to be using signals to manage
operations and look for opportunities for improvement to at least use the worksheet alongside a control
chart, but certainly preferring the worksheet method to the visualization method in total.
So this is it for this lecture, and we'll look at consolidating some of these signals and the next
one.