hi there Jamie, here today at Teachers Tech hope you're having a great day
today I want to show you how to use Microsoft Excel to create a bell curve
if you'd like to follow along with today's lesson I'll put a link to this spreadsheet
Down Below in the description so you can click on it and download it open it up and follow
along and practice with me let's get started on how to create this bell curve in Microsoft Excel
you can see in today's example we're going to be creating a bell curve in Microsoft Excel based on
student Mark so these are just random numbers I put in and these are just some names to connect
with them what I'd like to do before we get with putting in using our functions for mean standard
deviation and normal distribution I like to sort my data here from smallest to largest so
what I'm going to do is just select what I need to be sorted go up to sort right here and then
sort smallest to largest and when you're when this prompts you hit make sure you expand the selection
because I want the names to be connected also so I hit sort and that's the first step right here
for me I could have done this at a later Point as well but I just like to sort it to begin with so
we have this sorted smallest to largest my next one thing is going to be to find the mean now
we have a function for mean and that's going to be our average with any of these functions that we're
going to be using we need to start with the equal sign I'm going to start typing in average and see
it comes up right here so I'm going to select this what do I need to select well I need to select my
range here of all my different marks and I'm just going to hit enter at this point I I don't want
to have all these decimal places so I'm just going to make it a whole number here round it up here we
can see to 66. so that's the first step is finding out the mean for this for this this data set the
next thing is going to be standard deviation and we have a function for that as well so I'm going
to start with my equals and if I just put in St you can see D we have two different ones we could
possibly use I'm going to be using the stdev.p because I know my data set is complete this is all
the marks that I have if you if it wasn't complete if it was just a sample you would use the next
one down here at the dot s so I'm using dot p and again just like average all I have to do is Select
what I want to apply the standard deviation to hit enter and again I'm going to just round this up so
it should turn to 21 just like that so I have my mean my standard deviation this allows me to use
the function for normal distribution now so if I go to this cell right here and if I just put
again put my equals in and I start typing normal this is going to be it right here so this Norm dot
dist I'm going to select this one now I'm going to walk you through all the different arguments
in this using this function so when it says X on the first one what you need to do is select the
first Mark over here so it's going to be this right here we need to put a comma after and it
says select the mean well we select we already we did the mean it's right here so that's D2 and I'm
going to put a comma and what's going to be the next thing standard deviation so I still have one
last thing last thing to do we have an option here of true or false so it's going to be false for us
probability Mass function you can see there's cumulative just produced distribution function
but we're going to be picking this one right here so I'm selecting that one and my bracket hit enter
and I get my first one right through here so I do need to copy this down and I want to point
out we need to I could have done this before we do need to use F4 or we put the dollar signs around
a couple different things because if I copy this down I need these ones locked in place so I could
double click on this or go up into the formula bar up here and what I'm going to do is go just click
in D2 here and I'm going to use F4 so function F4 uh on this one so I just hit the wrong key here
let me just click on here again and function F4 and I put the dollar signs around those ones and
I need to under standard deviation as well so I'm going to use my function F4 and just like that so
and just hit enter nothing really changed here of the number but what it's going to do now if I
just I'm going to just double click on this little square in the corner or I could drag down but I'll
just double click it fills this all the way down and notice if we look at our just distribution
already we have our first number and last number uh being very similar so it looks like it will be
a nice bell curve this is the point that we're at where we can actually add our bell curve in
a chart to Microsoft Excel so Microsoft Excel makes it really easy now to create a bell curve
using a scattered chart and how we do this is the first thing I'm going to do right now is actually
select what I want to chart or graph so this is going to be the information right through here
I'm going to go up to insert and it's going to be scattered to other scatter chart that we're
using and it's this one right here I'm going to show you a couple different ways you can get to
it so you can see that there's scatter charts here if I go to recommended right here this is
going to be the one we're going to be using this scatter but if I go to all charts if we take a
look we have the XY scatter right through here and I can pick probably smooth lines is what I'd
want so I'm selecting this and it gives me kind of an example of what it would be so I'm going
to go ahead and hit OK on this and I get my chart at this point we can go through and we can format
our chart so if you wanted it looking a little different we can apply different designs that
are already created so in the intro I had this one so I can select very quickly to make those
changes I can start changing customizing this chart so if I was just going to call this bell
curve just like this whereas an example I can go through and change colors and everything but
I have a whole different video about using charts in Microsoft Excel and I'll put a link to that in
below and up in the card above but you can see the bell curve in this case looks nicely distributed
from uh from the class I do want to point out though it's not a perfect bell curve and that's
because that the data sets of the students marks it's not normally distributed so the mean medium
mode of the data set aren't the same on this and you can see also if I go ahead and make a a change
to one of these marks so if I was going to change it to 17 you can start to see how the distribution
isn't quite the same on this on but I'm just going to go back now this format as I said before you
can go through and apply this to other information you just need to have your data set to work from
and remember when you're doing the standard deviation whether it be from the complete set
or just a sample on this so I hope you like this how to create a bell curve in Microsoft Excel let
me know if this has worked for you if you want to learn more about graphing or Excel thanks
for watching this time on teachers Tech I'll see you next week with more Tech tips and tutorials