Daniel asks…
How do I find the standard deviation of this data using Excel 2007?
Right so I've put all my data into Excel 2007 from a survey i conducted this is how it looks at the moment. I won't bother putting the question in, just the data.
Age................. Frequency
17-18.................... 21
19-20..................... 7
21-22..................... 3
23-24..................... 1
So how do I find the standard deviation? I'm finding it hard because it's grouped data. Do i need to put the class centres in or something?
admin answers:
Fill in Frequency using cells B1-B4. In B5 use formula =STDEVA(B1:B4) to get 9.0185.
STDEVA
Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Text and logical values such as TRUE and FALSE are included in the calculation.
Syntax
STDEVA(value1,value2,...)
Value1, value2, ... Are 1 to 30 values corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.
Remarks
STDEVA assumes that its arguments are a sample of the population. If your data represents the entire population, you must compute the standard deviation using STDEVPA.
Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the STDEV worksheet function instead.
The standard deviation is calculated using the "unbiased" or "n-1" method.
STDEVA uses the following formula:
sqrt((Sum(x -xbar)^2)/(n-1))
Sharon asks…
how do i find how steep the standard deviation graph will be using excel?
in my finance class we had to do specific calculations that gave us the mean (23,103,395) and standard deviation (17,549,613)
after getting that number I have to decide if it's (+/-) average riskiness - the only way i know to do this is looking at a graph and seeing how steep it is - but i have no clue how to get there
please help!!!
admin answers:
Calculating and plotting mean and standard deviation in Excel
Print or open this Excel file
Let’s take a simple experimental example. Imagine that your lab group decided to carry out an experiment investigating the effect of Solvay substrate on the growth of bean plants in comparison to potting soil. You decide to use two treatments: 1) regular potting soil (75%) with 25% Solvay substrate and 2) potting soil (100%).
You plant the seedlings and measure the height in centimeters of 3 seedlings per treatment for six weeks.
To calculate the mean for each treatment:
List the number of weeks in a vertical column. Head the next 3 columns with treatment 1 and replicate number. Head the next column with “Treatment 1 Mean” and the next column with “Treatment 1 standard deviation.” Enter the data for each of the three replicates.
Place the cursor in the first cell under the column labeled “Treatment 1 Mean.” Go to the Insert toolbar at the top and scroll down to Function. Type “average”, then click on AVERAGE, OK. A dialog box will come up. Click on the square on the right hand side, then the first cell in the sheet to average, and drag across to the last cell that you want to average. Click OK. The mean should appear in the cell. The mean of the three replicates for Week 1 should now be present in the first cell under “Treatment 1 Mean.”
To automatically calculate for the rest of the weeks, click on that cell and then place your cursor at the lower right hand corner of the cell. It should turn to a cross. Click and drag down to the sixth cell (the sixth week). The mean for treatment 1 for each week should automatically calculate.
To calculate the standard deviation for each treatment:
What the heck is standard deviation, anyway? Standard deviation is a measure of spread or variance in your data. 68% of all observations will fall within 1 standard deviation. 95% of observations for a given data set will fall within two standard deviations. If standard deviations between treatments do not overlap, that means that there is probably a significant difference in the means between the treatments.
Place the cursor in the first cell under the column labeled “Treatment 1 standard deviation.” Go to the Insert toolbar at the top and scroll down to Function. Type “standard deviation”, then click on ST DEV, OK. A dialog box will come up. Click on the first cell in week 1 (treatment 1 rep 1) and drag across to the last cell (treatment 1 rep 3). Click OK. The standard deviation should appear in the cell. The standard deviation of the three replicates for Week 1 should now be present in the first cell under “Treatment 1 Standard Deviation.”
To automatically calculate for the rest of the weeks, click on that cell and then place your cursor at the lower right hand corner of the cell. It should turn to a cross. Click and drag down to the sixth cell (the sixth week). The standard deviation for treatment 1 for each week should automatically calculate.
Now repeat the entire process of calculating mean and standard deviation for treatment 2.
You now have a dataset to graph!
Copy and paste the 4 columns for mean and standard deviation for treatments 1 and 2 to a separate area below. When you paste, choose “Paste Special” and click “values.” Also paste the weeks in to the left of the values.
Go to the Insert toolbar at the top and scroll down to Chart. Click Line, Next, Series, Add, and in the Values, highlight the Treatment 1 Mean values.
Click ADD to add another series. Do the same as above, only highlight the Treatment 2 Mean values.
Enter labels for the X and Y axes and legends as necessary.
Click Finish. The graph will appear where you designate.
Now, to enter the standard deviation valuesono your graph. Click on any point in treatment 1 to highlight that series. Right click and hit “Format Data Series.” Click on “Y error bars,” “both,” and “custom.” Then, hit the square to the right of the box for the + values and highlight the cells in the sheet corresponding to the standard deviation for Treatment 1. For the – values, highlight the same cells. Click OK.
Repeat for treatment 2.
You should now have a graph with means and corresponding standard deviations for treatments 1 and 2.
Robert asks…
Excel calculating MEAN and Standard deviation. Help Thanks!?
I have this assignment for excel and I'm having trouble figuring out how to do it, and I'm hoping someone can help clarify it for me. I'll tell u what I think and let me know if its right.
For this assignment, you will generate a series of random numbers. You will then manually calculate the mean of those numbers; use the prebuilt MEAN() function to test that your calculations are correct. Once you have calculated this mean, use your mean to find the standard deviation of the numbers; once again, use the prebuilt STDEV() function to determine that your calculations are correct.
Ok, what I did was type =RAND() into a cell and hit enter, then i dragged the cell down 25 cells so it has =RAND() as the function in all those cells (25), which is my series of random numbers i guess. The to calculate the mean (couldnt find a =MEAN() function) I used =AVERAGE and highlighted the series range and hit enter. then I typed =STDEV and selected the range of random numbers and hit enter Is this right?
The only weird thing that caught my eye was everytime I hit enter the random numbers changed,but it seemed that the average and stdev changed with them. Is this normal?
Thanks much!
admin answers:
The formulas you used for mean and std dev are correct.
It is normal to see that the random numbers keep changing whenever you open the file because the formulas you use (including RAND) will run every time you open the file.
WRT to average and standard deviation numbers dynamically updating with change in rand() values, this is normal too. That's what makes Excel a cool program. When you change a cell, it updates all its related cells.
Hope this helps.
Mahurshi Akilla
Linda asks…
how to write excel formula for standard deviation?
i want to find the people who are two standard deviations from the average and two standard deviations below the average how would i write this in excel
admin answers:
I will assume you are using the range A1:A100 to list the statistic about the people you are measuring. In cell B1 you could paste this formula:
=IF(ABS(A1)>=AVERAGE(A$1:A$100)+ 2*STDEV(A$1:A$100),"Include","")
then copy it down to B100.
The numbers in column A that are two or more standard deviations from their mean will have the word "Include" next to them in column B.
Hope that helps...
Carol asks…
How do I easily calculate the standard deviation in Excel when I have a value of "30 out of 100?"?
I frequently find myself in the situation of trying to calculate the standard deviation for "565 of 1725 people said yes".
My current solution is to create a column with 565 "1" values and 1160 "0" values and calculate the average & standard deviation from that.
That strikes me as a horrible kludge. Isn't there any way to just say "565 of 1725" and get the standard deviation?
Ultimately I want the standard deviation to calculate the confidence interval so I can compare results from two different surveys.
thanks mike c, I see.
I'd seen an explanation of confidence intervals once that used election outcomes as the example...
So would it be presumptuous of me to use any other test to determine the validity of the outcome - e.g. how well we can extract the difference between the two (or more) different results?
admin answers:
You have a bianary set of outputs (only 2 outcomes), there is no standard deviation for that.
Standard Deveiation calculates the average DIFFERENCEs of your answers.
. A measure of the dispersion of a set of data from its mean. The more spread apart the data is, the higher the deviation.
When there are ONLY 2 possible outcomes, there is an infinite deviation.
Powered by Yahoo! Answers
No comments:
Post a Comment