Step 4: Remove the legend by clicking on it and pressing Delete Step 3: Select the first plot in the drop-down menu Step 2: Click on the tab labeled ‘Insert’, and then select ‘Scatter’ in the ‘Charts’ menu. Step 1: Select both columns of variables you wish to plot (StudyHrsĪnd GPA). We will now create a scatterplot for StudyHrs and GPA. Each point on the scatterplot represents an individual’s data on the two variables.
Knowledge check: What does the r value of 0.88 tell you about the strength and direction of the correlation between StudyHrs and GPA?Ī scatterplot is an excellent way to visually display the relationship (correlation) between two variables. Once you have your r value, don’t forget to round to 2 decimal places.
Step 4: For Array1, select all the values under StudyHrsįor Array2, select all the values under GPA. Step 2: Click on the function wizard button. Step 1: Select the cell where you want your r value to appear (you StudyHrs = average number of hours spent per week studying for 209 GPA = grade-point average earned in 209 at the end of the quarter To do so, we can calculate Pearson’s r for our two variables. Let’s say we want to determine if there is a relationship between number of hours spent per week studying for Psych 209 and GPA earned in the class at the end of the quarter. In Excel, there are many functions that can calculate a correlation statistic, however, we will only use =PEARSON in this class.
The closer the correlation coefficient is to 0.00, the weaker it is.Ĭorrelations are described using the Pearson Product-Moment correlation statistic, or r value. Every correlation has a magnitude or strength: The closer the correlation coefficient is to +1.00 or -1.00, the stronger it is. correlation: high scores on one variable are associated with low scores on the other variable. Your Excel spreadsheet should now look like this:Ī quick review: Every correlation has a direction (positive or negative): + correlation: high scores on one variable are associated with high scores on another variable. =AVERAGE(first cell:last cell): calculates the mean =MEDIAN(first cell:last cell): calculates the median =MODE(first cell:last cell): calculates the mode =VARP(first cell:last cell): calculates the variance =STDEVP(first cell:last cell): calculates the standard deviation You may directly write the functions for these statistics into cells or the formula bar, OR You may use the function wizard ( in the toolbar) You can either type these cell references in by hand or by clicking and dragging with your mouse to select the cells.īelow are several functions you will need to learn for this class. A reference to cell(s) (e.g., “=AVERAGE(B1:B18) or “=AVERAGE (B1, B2, B3, B4, B5, B6, B7, B8)” This tells Excel to calculate the average of the data that appear in all the cells from B1 to B8. The input for a function can be either: A set of numbers (e.g., “=AVERAGE(2, 3, 4, 5)”) This tells Excel to calculate the average of these numbers. They take data you select and enter, perform calculations on them, and return value(s).Īll functions have a common format – the equals sign followed by the function name followed by the input in parentheses. When you enter an equals sign into a cell, you are basically telling Excel to “calculate this.” Functions are Excel-defined formulas. Formulas always begin with an equals sign (=). Enter the following information into your spreadsheet:įormulas are equations that perform calculations in your spreadsheet. Open Excel (Start ? All Programs ? MS Office ? Excel). Now type the data into the bar and press Enter.ġ. Click on a cell, and then click in the formula bar (the space next to the ). Click on a cell, and type in the data (numbers or text) and press Enter.Ģ. There are two ways to enter information into a cell:ġ. Each cell is named for the column letter and row number that intersect to make it.
Columns are represented by letters across the top of the sheet.Ī cell is the intersection between a column and a row. Rows are represented by numbers along the side of the sheet. Store and organize data, Analyze data, and Represent data graphically (e.g., in bar graphs, histograms, and scatterplots)Įxcel spreadsheets organize information (text and numbers) by rows and columns: Introduction to Excel 2007 Part 1: Basics and Descriptive Statistics
0 kommentar(er)
