hey everyone this is the first video in a series covering Microsoft Excel tips
and tools that every engineer should know in today's video I'll be showing
you how to create a histogram in Excel 2016 I'll talk about why histograms are
useful and some applications for so here we have a bunch of data and this data is
actually a bunch of measurements taking from different production runs of this
particular shaft and we're looking at this diameter so we've taken a bunch of
measurements throughout the runs to understand what the variation is on that
shaft diameter and if we wanted to understand this we could take an average
and look at all the data points and say all right well that's our average and
let's say we had a two thousand seven inch tolerance on the shaft diameter
we'd say all right on average we're within tolerance and some people might
look at that and say okay that's just fine
but if we really wanted to understand how well we're doing at actually
achieving this diameter then we'd have to make a histogram now Excel 2016 is
make it made it really easy to do this we just have to go to the insert tab
select our data go back up to the top here and then there's these new
statistical charts as of 2016 that includes a histogram and there we go
there is our histogram which is essentially a frequency distribution of
the data so it's telling us how many samples are within these different bin
ranges so it defaulted to 1 2 3 4 5 6 7 bins and between 0.99 37 and 0.995 3 it
looks like there was about well it looks like there was 7 measurements in that
bin I'm not exactly sure how Excel calculates the number of bins or the bin
ranges but we are able to go in here and manually set up our bin ranges it sets
to automatic by default we can use a bin width or a number of bins and what
that's kind of interesting if we change it to 7 bins even though it had 7 bins
before it it changes the way the histogram looks so just kind of be aware
of that just in general the number of bins is going to change the way the
distribution looks so if I just jump this up to 10 we can see that's our
distribution looks like the equation I usually use to
select a number of Bin's is this okay is your number of Bin's and then you take
the square root of the count of data samples and in my case I have 100
samples so if I took the square root of 100 that'd be 10 so I would actually use
10 for my bid number now this is a good rule of thumb so this will work just
fine for our case now if you look at this and ask the question how are we
doing at actually achieving this one inch diameter and remember we said we
have a two and two thousandths of an inch tolerance so if we keep that in
mind we can see that it actually looks like there's one value here that's above
that tolerance or outside of that range and then here we actually have well
seventeen plus seven that are for sure outside and maybe even a few more here
so even though on average the data is within a tolerance range because of
variation the data actually is distributed and that makes some of the
data outside of the tolerance range so let's take a look at another example of
how we can use a histogram now we're gonna take a look at how many shafts
we're completing per shift so if we grab our data and go to the insert tab and
make another histogram you can see that now it actually defaulted to five bins
again I don't know how Excel is calculating the bin number I'll have to
do some more digging on that but let's let's bump up the number of bins and see
what the distribution looks like we'll use the square root of M method again
and we can see that we actually have a bimodal distribution meaning that
there's two modes or most common values in our histogram so I mean ask the
question well why is that it could be that there's two different operators or
two different shifts that are producing different amounts of shafts per shift so
this is a really good example again of why things like an average don't give
you the whole picture because our average is actually right here but most
of the values are either higher or lower than that let's take a look at one last
example so here we have some job data and we're going to compare the due date
of the job to the completion date of the job to see how well we're doing at
hitting our due dates so if we want to look at that
we'll grab our column where we're looking at the difference of the due
date and completion date so here's a histogram of that and we can see that it
has kind of this right skew let's actually change the bins so they give us
a better idea what's going on let's use the bin width in this case and let's set
it to one just because we're talking in terms of days so here's the distribution
again we can see that has this right skew where it looks like most of the
time we're hitting the due date but it kind of tails off and then there's some
jobs that are being completed way after the due date so rather than just kind of
asking the question why are we always later why are customers always mad at us
we can begin to understand how often relate and to what degree are we late
just by looking at this visual diagram of the frequency distribution of the
difference between due dates so hopefully these three examples have
given you an idea of how to use histograms why they can be useful and
how to create one for yourself let me know in the comments below how you use
histograms in your business I'd love to hear about some other applications and
if you're new to using histograms let me know how you plan to incorporate them
into your engineering toolbox thanks for watching alright I hope you
all enjoyed that one to get more videos as they are released make sure to hit
that subscribe button if you like this video make sure to like and share and if
you didn't like it well then leave me a comment and let me
know how I can improve last but not least this channel is for you so if you
have any suggestions for topics you want me to cover make sure to let me know
thanks for watching we'll see you next time