| some basic math in Excel |
How to make a scatter plot in
Excel |
| some basic reseach design hints |
How to make a bar graph in Excel |
1. How to determine the mean average and standard deviation in Excel
2. Data should be entered in vertical columns, with each group a
different column.
3. In an open cell below the column of data you wish to analyze,
fist type =
4. Then type average(
5. Then highlight all the data you wish to determine the average
(probably the entire column
6. After you have highlighted the data, type ) and hit enter
7. So the final equation to determine an average of 20 numbers in
column A should look something like this: average(A2:A21) with all the
numbers you wish to average in rows 2 through 21.
8. To determine the standard deviation, the final equation should
look something like this: stdev(A2:A21) with all the numbers that you wish
to determine the standard deviation in rows 2 through 21.
1. Data should be entered in vertical columns, with each group a
different column.
2. To make a bar (histogram) graph, highlight data you wish to plot
3. This graph will usually plot the average ± the standard deviation
for two or more different groups. The question we are asking is how does
VO2max compare between untrained students vs. endurance-trained students.
4. Click Chart Wizard on the top tool bar
5. Click on the type of graph you wish to make (in this case we
want to make a Bar graph. Excel calls these Column graphs. So pick Column
graph
6. You can decide what kind of chart sub-type you would like. Usually
we will use the first (Clustered graph) sub-type.
7. Click on Next
8. Decide if your data on in Columns or rows. If data were
entered in Columns (that is each group in a separate column) then click on
columns. Each bar of the graph should be a different color.
9. Click Next.Ý
11. Enter category x-axis and value y-axis. Make sure to enter
correct units for the chart axis.
12. You can turn the legend on or off. If you turn off legend you
will need to label each Bar (see #21).
13. Click next
14. Place the chart as a new sheet and enter a file name for the chart
15. Click Finish
16. To move the graph title from the top to the bottom of the graph,
highlight the chart title and drag to the center and bottom of the graph.
You may need to resize the graph if the figure legend does not fit.
17. To resize the graph, click in the center of the graph, and then
the graph should be outlined. Move the mouse to the bottom right corner of
the graph. The curser should change design. Then hold the mouse down and
drag to resize the graph (smaller or larger).
18. Make sure your figure legend is numbered and that it describes
the graph. You can change the text of the figure legend by highlighting
the figure legend then place the curser inside the text. The curser should
change design so you can delete or enter text.
19. You can change design of each bar (histogram) by double clicking
on the specific bar. To change the color or design, click on Patterns and
change design of that specific bar. If you wish to change the width of
the bar or the amount of overlap between bars, click on Options and change
gap or overlap.
20. To enter the standard deviation for each group, double click on
the bar, click on Y error bars, click on Display Both (we want error bars
above and below the mean average), click on fixed value for the error amount
and enter the value that was calculated in the Excel spread sheet.
21. If you wish to proved a label at the bottom of each Bar on the
x-axis. Simply click in the center of the graph so the graph is highlighted,
then type out the label and hit enter. The new label will appear someplace
in the center of the graph and you can easily drag (see # 16) the label to
the bottom of the appropriate Bar.
22. Make sure you save the graph onto your disk and if you are done
print a final copy. See Figure 1 for a final copy.
1. Data should be entered in two vertical columns, with each column
a different variable (VO2max = column one and training time per week = column
two). Data need to be entered so that each subject’s measurements are in
the same row.
2. To make a Scatter Plot, highlight data you wish to plot
3. This graph will plot all the data points for all of the subjects.
4. Click Chart Wizard on the top tool bar.
5. Click on the type of graph you wish to make in this case we want
to make an XY Scatter Plot. So click on XY Scatter.
6. You can decide what kind of chart sub-type you would like. Usually
we will use the first sub-type (compares pairs of values) without connecting
the data points with lines.
7. Click on Next
8. Decide if your data are in columns or rows. If data were
entered in Columns (that is each group in a separate column) then click on
columns. The graph should have many of the same colored/design symbols in
the graph.
9. Click Next
10. Enter a Figure legend under Chart title. This will be moved to
the bottom of the graph
11. Enter category x-axis and value y-axis. Make sure to enter correct
units for the chart axis.
12. You can turn the legend on or off.
13. Click next
14. Place the chart as a new sheet and enter a file name for the chart
15. Click Finish
16. To resize graph and drag figure legend, See directions above for
a Bar graph.
17. To analyze the data, you will enter a regression line (line of
best fit), regression equation and an r2 value.
18. To enter a regression line, click in the center of the graph so
the graph is outlined.
19. Click on the command Chart on the tool bar and scroll down to Enter
Trendline …
20. The type of trend line will be a linear regression, so highlight
the first box titled linear trend/regression type.
21. Click in Option
22. Check display equation on chart
23. Check display r 2 on chart
24. Click OK
25. Highlight the figure legend and enter delete (a figure legend
is not needed for a simple regression)
26. You can move the equation by highlighting the equation and dragging
to an appropriate location.
27. Make sure to save graph on your disk and if all is done, print
out a final copy
1. The average is obviously a measure of central tendency.
2.The standard deviation is a measure of variability about the mean.
A larger standard deviation means there is more variability or more spread
in the scores or measurements.
3. Regarding the Scatter Plot, according to our question training
time per week is the independent variable (this the variable that is controlled
or manipulated by the researcher).
4. The dependent variable is the measurement of VO2max (a dependent
variable is the behavior or physiological measurement that is measured to
determine if it is affected by the independent variable).
5. In a Scatter Plot, the independent variable (the one manipulated)
is usually placed on the x-axis and the dependent variable (the one you measure)
is on the y-axis.
6. So in our case, training time would be on the x axis and VO2max
on the y axis.
7. The regression line is the line of best fit through all the data
points.
8. Since this is a straight line it will have the form y = mx+b where
m = slope, and b = y intercept.
9. Our line has a positive slope and goes from bottom left to top right,
so there is a positive correlation between training time and VO2max (just
what you would predict). That is as one variable increases (training time)
the other tends to increase (VO2max).
10. The R2 value is a measure of the strength of the correlation. The
R-value is also called the Pearson product moment correlation and is used
to measure how well two variables are related to each other. As the R2 value
approaches 1.0 there is a much stronger correlation. An R2 value of 0 would
mean that there is no relationship between the two variables. The R2 value
for the data I presented of 0.7521 is pretty strong.