|
Bivariate Analysis Guidelines for Excel
Using Excel to Create Scatterplots:
-
First, know what values are to be indicated by the vertical
and horizontal axes.
-
Select range containing data to be plotted (i.e.,
the Y-range).
-
Click on the chart tool
-
Work through the Chart Wizard dialog boxes:
-
Select XY (Scatter) and then click on Next
in the second dialog box.
-
Click on Series tab in next dialog box.
-
Click on the button at the end of the X-Values
box.
-
Use the mouse pointer to select the X-range
from the worksheet and then click on the button at the end of the
text box.
-
Click on Next.
-
In the next dialog box:
-
Click on the Legend tab and uncheck
the Show legend box.
-
Click on the Gridlines tab and:
-
Check at least one Value (X) axis
box.
-
Make sure at least one Value (Y)
axis box is checked.
-
Note: use your judgement about whether
you need minor gridlines.
-
Click on the Titles tab and fill in
the title boxes for the main title and the axis titles.
-
Click on the Finish button.
-
Move, resize, and edit (i.e., clean-up) the
graph as appropriate: titles, labels, legend, etc.
-
Graph should be about the size of the
screen area available.
-
Try font sizes of 12, 10, and 8 points,
respectively for main title, axis titles, and axis labels.
-
Remember: to modify the properties of any
object (or object within another object) right click on the
object and then choose the property you wish to modify.
Excel Formulae for Regression Calculations:
b0
: =INTERCEPT(Y-range,X-range)
e.g., =INTERCEPT(G4:G23,E4:E23)
b1
: =SLOPE(Y-range,X-range)
e.g., =SLOPE(G4:G23,E4:E23)
R2 :
=RSQ(Y-range,X-range)
e.g., =RSQ(G4:G23,E4:E23)
Syx :
=STEYX(Y-range,X-range)
e.g., =STEYX(G4:G23,E4:E23)
|
