How to Calculate Mean, Standard Deviation, and Create Charts in Excel

okay today I'm going to go through how to calculate the mean and standard de deviation and uh make a simple chart uh

demonstrating the values so what we have here is uh the observations over four minute just four minutes for an of for

an example of of some scan sampling for uh the number of fish that are performing an

activity so what we have are our four Behavior categories in in four minutes so first I'm going to show how to

calculate the mean the mean's very simple to do it's just a simple formula equals

average parenthesis highlight your data set with end

parenthesis then go down and that calculates that similarly you can calculate standard deviation the same

way equals stde parenthesis highlight the data you want

to calculate the standard deviation from and hit the parenthesis again and then go down below and it calculates it now

simply dra copy this and drag this formula across to calculate these values for everything next we want to do is

actually make a graph so what we're going to do is actually uh we could do it in the with the data in this format

like this but but it's simpler to just transpose the data so we'll copy it then we're going to paste it down

here and we're going to use paste special okay and this will trans and then we'll use transpose and that'll put

reverse these things into a vertical column next we'll copy the mean data oops place that down there by

transposing it using this P special but we have to click click values or it will copy the

formulas and we'll do the same with the standard deviations pay special values again so

we copied the the values and transpose so now we have our data into a format that we can make into a a chart very

easily so we first highlight the uh mean values uh for this data and we hit insert

column like that so we have our our basic graph now what we want to do is assign the um names of these these

categories so to do this we need to click on one of these uh charts oop sorry right click on and you'll see here

when you right click the mouse that you're going to see select dat data and so you'll choose that so now you'll hit

edit in the horizontal category AIS labels we'll hit edit now we're going to select the label range by clicking on

this little box here and highlight those that titles now when we click this little box again this will uh insert

these in so now we have the labels on on our our graph the next thing we want to do is click on on these um little charts

these little bars and we'll hit layout and this will allow us to insert in error bars so this will be the the

standard deviations that that we have here so let's click on Arrow bars and you'll see more Arrow bars options

that's where we want to go with this so we hit more Arrow bar options and we tell it that we want to use a custom

value and now we're going to specify the data range for that okay so we specify value and what we're going to do is

highlight those standard deviations we calculated okay then once we do that we just hit okay and now our standard

deviations are are now in this uh in this data now one of the things you might want to do to make your standard

deviations look a little nicer is uh when you uh click on these standard deviations is actually just have them as

plus so they don't go downward just upward so now they look a little bit nicer next thing you want to do is is

clean this up a bit get rid of this series caption um you want to insert your uh your axis

titles so the first one is primary horizontal axis title idle AIS below okay so this is all under that

that layout tab so now we just type in what this is and we see type in observed

behaviors okay next we'll insert the axis title for the vertical one we do a a primary vertical axis this time and we

rotate it and what we want to do is is uh call this um

numbers of fish per

minute okay and now we have a a the uh axis done so that essentially is all we have to do in order to to create a graph

and Excel that will allow us to compare our data so you could do this with INF fish or you can take the grand means of

your all your samples yours and your partner's data and and calculate the means and standard deviation for your

combined data so in other words calculate the tallies for each uh sampling that you did uh and then and

then take the means for each one of those and then calculate standard deviation and means for the combined

data and uh then create your graphs and you want to do this uh as a way to summarize your data rather than having

lots and lots of data but instead having it summarized in nice graphs that are easy to understand and clear to read

thank you hope that helps