psych week 2 discussion question

Mishawn6

PSYC_6800_WK2_5_6_7_9_10_WorkingWithDatasetsJobAid_Excel.pdf

Home >Psychology homework help >psych week 2 discussion question

PSYC 6800: Applied Psychology Research Methods

Working with Datasets

Job Aid

Introduction

Throughout PSYC 6800: Applied Psychology Research Methods, you will have

assignments requiring you to use Excel to conduct various statistical analyses. (Note:

You may use a different statistical software program of your choice, however, if you

choose a different statistical software program, it will be important for you to be very

familiar with the software).

Additionally, periodically you will be asked to use Excel to create visual displays of your

statistical analyses (e.g., scatterplots, tables, etc.), and then post them to the

Discussion Board.

The PSYC 6800: Applied Psychology Research Methods Working with Datasets Job

Aid provides step-by-step instructions about how to use Excel to conduct statistical

analyses, as well as how to post visual displays of those analyses.

For assistance in using the basics of Excel, such as inserting rows and columns, or

what the ribbon tabs mean, review the Excel materials in the Walden Academic Skills

Center, https://academicguides.waldenu.edu/academic-skills-center/skills/ms-

office/excel Tutoring appointments are also available.

Table of Contents

Load the Analysis ToolPak in Excel (Week 1)

How to Create a Histogram in Excel (Week 5)

How to Determine Measures of Central Tendency and Variability in Excel (Week 6)

How to Construct a Frequency Distribution in Excel (Week 7)

How to Run a Correlation and Generate a Scatterplot of Correlated Data in Excel

(Week 9)

How to Determine Statistical Significance of Correlated Data (Week 9)

How to Run an Independent Samples t Test (Week 10)

How to Post a Visual Display to the Discussion Board (Weeks 3, 5, 6, 7, 9, 10)

https://academicguides.waldenu.edu/academic-skills-center/skills/ms-office/excel

Load the Analysis ToolPak in Excel

(Week 1)

In order to conduct advanced statistical analyses in Excel, you must load and activate

the Analysis ToolPak add-in. The link provides instructions for activating it on Windows

and Mac operating systems: https://support.office.com/en-us/article/Load-the-Analysis-

ToolPak-in-Excel-6a63e598-cd6d-42e3-9317-6b40ba1a66b4

Follow the instructions within Excel on your computer. Note. The Analysis TookPak

add-in may not be available in Excel for the web, mobile app versions of Excel or

Windows RT.

Back to Table of Contents

How to Create a Histogram in Excel

(Week 5)

Course Text

Salkind, N. J. (2016). Excel statistics: A quick guide (3rd ed.). Sage.

● Excel Quickguide 55, “The Histogram Tool”

Excel Practice Datasets

● Quick Guide Data Sets

o SAGE Publications (2020). Q55.Histogram.xlsx. https://study.sagepub.com/node/22833/student-resources/quick-guide- data-sets

● Check Your Understanding Data Sets

o SAGE Publications (2020). QS55a.xlsx. https://study.sagepub.com/node/22833/student-resources/check-your- understanding-data-sets

https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-in-Excel-6a63e598-cd6d-42e3-9317-6b40ba1a66b4

https://study.sagepub.com/node/22833/student-resources/check-your-understanding-data-sets

Note: In order to access this file, you will need to select the link, “Click here to download all files” and extract QS55a.xlsx from the downloaded .zip file.

o SAGE Publications (2020). QS55b.xlsx. https://study.sagepub.com/node/22833/student-resources/check-your- understanding-data-sets

Note: In order to access this file, you will need to select the link, “Click here to download all files” and extract QS55b.xlsx from the downloaded .zip file.

Additional Resources

https://support.office.com/en-us/article/create-a-histogram-85680173-064b-4024-b39d-

80f17ff2f4e8

Note: In Excel online version, you can view the histogram but cannot create it. Other

platforms (e.g., iOS, Android) have limited support for creating Histograms.

1. Open the dataset, “Study Habits” from the Learning Resources.

2. Choose a continuous variable from the dataset

3. Identify the range of data values in the variable to create your Bin for the

Histogram program.

4. Select an empty column to record the bin range and label it “Range”

https://support.office.com/en-us/article/create-a-histogram-85680173-064b-4024-b39d-80f17ff2f4e8

5. In the Range column you created, include the range of numbers that will form the

X-axis values in your Histogram. You can make the interval between values as

small as you need to capture the details of the distribution (e.g., tenths of a

point). In this case, whole numbers will suffice.

6. In the Data tab, select the Data Analysis option. The Analysis Tools wizard will

open. Scroll down and Select the Histogram option. Click OK.

7. In the Input box, click on the RefEdit button for the Input Range.

8. Click and drag the entire column of data for the variable you wish you analyze.

Include the variable name as part of your selection. The column information will be

displayed in the Input Range (circled in red below)

9. Next, click the RefEdit button for the Bin Range and select the values in the

Range column that you created. Be sure to include the column name, “Range” as

part of your selection.

10. In the Output options of the wizard, select Output Range and click the RefEdit

button. Click anywhere to the right of your dataset to mark the beginning of

where the output will be placed.

11. Complete the Histogram wizard by checking the boxes “Labels” and “Chart

Output”. Click OK.

12. Your frequency table and histogram should be displayed in your worksheet. Note

that if there were values outside the Bin range you identified, they are labeled as

“More” in the histogram. You should consider extending your Bin range

accordingly and re-running the Histogram.

Note. If you have a very large worksheet/dataset, the Histogram may be

displayed elsewhere in your worksheet (i.e., you might have to search around on

the worksheet to find it; it might be placed on top of the middle of your dataset).

You can click and drag the histogram elsewhere.

Back to Table of Contents

How to Determine Measures of Central Tendency and Variability in Excel

(Week 6)

For the Week 6 Discussion, use the Income Data_50 U.S. States and Washington

DC_2013-2018.xlsx file to compare two states’ measures of central tendency (i.e.,

mean, median, and mode) and measures of variability/dispersion (i.e., range and

standard deviation) for the complete span of 6 years.

Course Text

Salkind, N. J. (2016). Excel statistics: A quick guide (3rd ed.). Sage.

● Excel Quickguide 41, “Descriptive Statistics”

Excel Practice Datasets

● Quick Guide Data Sets

SAGE Publications (2020). Q41.Descriptive Statistics.xlsx. https://study.sagepub.com/node/22833/student-resources/quick-guide-data-sets

● Check Your Understanding Data Sets

SAGE Publications (2020). QS41a.xlsx. https://study.sagepub.com/node/22833/student- resources/check-your-understanding-data-sets

SAGE Publications (2020). QS41b.xlsx. https://study.sagepub.com/node/22833/student- resources/check-your-understanding-data-sets

Note. The descriptive statistics function requires data in contiguous columns. If you

want to calculate information for data from non-contiguous columns (e.g., Hawaii and

Connecticut), you will need to move (i.e., Copy and Paste) those columns so they are

adjacent to one another (see below).

Back to Table of Contents

1. Open the dataset, “Income Data_50 U.S. States and Washington DC_2013-

2018” in Excel.

2. Select the two states you wish to compare and copy and paste their data into an

area of the worksheet so their data columns are adjacent (i.e., contiguous). It is

also helpful to copy the row information to make it easier to track what each data

point represents (i.e., income data for each year).

3. In the Data tab, select the Data Analysis option. The Analysis Tools wizard will

open. Scroll down and Select the Descriptive Statistics option. Click OK. The

Descriptive Statistics wizard will open.

4. In the Input box, select the RefEdit button for the Input Range. Click and drag the

data to select two states you wish to compare. In this example, they are Hawaii

and Connecticut. Be sure to include the column labels as part of your selection.

a. In the Descriptive Statistics wizard, ensure that the data are Grouped By

Columns, and that Labels in first row is checked.

5. In the Output options of the wizard, select Output Range and click the RefEdit

button. Click a blank spot on your dataset to mark the beginning of where the

output will be placed. Check the Summary statistics box. Click OK.

6. Your descriptive statistics and measures of variability will be displayed in a table.

For your assignment, you can clean up the table by deleting the third column

(statistics labels). This makes it easier to compare the statistics between the two

states.

How to Construct a Frequency Distribution Using Pivot Tables

(Week 7)

Resources

Walden Academic Skills Center: https://academicguides.waldenu.edu/academic-skills-

center/skills/ms-office/excel

Note: Download the Pivot Tables PDF file

Getexcellent (2010). Create a percentage frequency table in Microsoft Excel.

WonderHowTo. https://ms-office.wonderhowto.com/how-to/create-percentage-

frequency-table-microsoft-excel-360161/

1. Open the dataset, “US Demographic Information_PA”, with the new data column

you entered with each state’s political affiliation.

2. Locate the PivotTable button on the INSERT tab ribbon, but don’t click it yet.

3. Select any cell in your dataset, then choose PivotTable.

a. The PivotTable will select the range noted in green dashed lines (most

often, the entire dataset). If necessary, adjust the range by clicking and

dragging the mouse over the range of cells you want included. It’s okay if

additional columns are included, so long as the “Affiliation” column is

included in its entirety (you’ll select which columns you want reported in

later steps).

https://academicguides.waldenu.edu/academic-skills-center/skills/ms-office/excel

https://ms-office.wonderhowto.com/how-to/create-percentage-frequency-table-microsoft-excel-360161/

4. Choose where you want the PivotTable to be placed. For this assignment, select

Existing Worksheet and click on an empty cell on the worksheet where you want

the Heading to start. Click OK.

5. The PivotTable Fields wizard will appear. Here is where you choose which

columns of data to report. Select the check box “Affiliation” and it will

automatically populate under Rows.

6. In the same PivotTable Fields wizard, click and drag “Affiliation” to the box

marked Values. Do this two times: once to report frequencies and once to report

percentages. When you do so, you will see the Columns box now includes “Σ

Values”. Click the X to close the wizard. You will see your worksheet with the

Affiliation frequencies in place.

7. Change the “Row Labels” heading: Click any cell in the PivotTable report to

activate the PivotTable Analyze Ribbon. Click on the “Design” tab next to it.

Locate the Report Layout pulldown menu and select “Show in Tabular Form”.

The Row Labels heading should change to Affiliation (or whatever label you used

for that data column).

8. Change “Count of Affiliation” heading to Frequency: Double-click (or right click)

on the heading and the Value Field Settings wizard will open. Click on the “Count

of Affiliation” label in the Custom Name area and change the name to

“Frequency”. Click OK to close the wizard.

9. Change “Count of Affiliation2” to report percentages: Double-click (or right click)

on the heading and the Value Field Settings wizard will open. Change the

heading from “Count of Affiliation2” to “% Frequency” in the Custom Name area.

Select the Show Values As tab. Click on the pull-down menu and switch from “No

Calculation” to “% of Column Total”. Click OK to close the wizard.

10. You can click and drag over the table to copy and paste it in a Word file:

Affiliation Frequency %

Frequency

Green 22 43.14%

Mauve 22 43.14%

Teal 7 13.73%

Grand Total 51 100.00%

11. You can also generate a bar chart from your Pivot Table worksheet by first

clicking anywhere on the frequency table, then, within the Insert tab of Excel,

select the 2-D column bar chart from the Chart area.

12. For the purposes of this discussion assignment, you may leave the % Frequency

data in place.

13. Include a Chart Title:

14. Include the linear Trendline: a. Click on the chart to open the Chart Design ribbon b. Locate the Add Chart Element pull-down menu and select Chart Title:

Above Chart.

c. Click on the Chart Title area and type in your chart title.

d. If you are familiar with editing Excel charts, you may uncheck “Show Value Field

Buttons” and “Show Axis Field Buttons” and remove the % Frequency data from the

chart. However, these extra steps are not necessary for the discussion assignment.

Back to Table of Contents

Green Mauve Teal

Frequency and Percentage of Political Affiliations in U. S.

Total

How to Run a Correlation and Generate a Scatterplot of Correlated Data in Excel

(Week 9)

For the Week 9 Discussion, you use Excel to add a variable column and corresponding

data. Then, identify the correlation coefficient (i.e., Pearson Correlation) and determine

statistical significance between two variables. Lastly, you create, post, and explain a

scatterplot of your correlated data.

Course Text

Salkind, N. J. (2016). Excel statistics: A quick guide (3rd ed.). Sage.

● Excel Quickguide 53, “The Correlation Tool”

Excel Practice Datasets

● Quick Guide Data Sets

SAGE Publications (2020). Q53.CORRELATION.xlsx. https://study.sagepub.com/node/22833/student-resources/quick-guide-data-sets

● Check Your Understanding Data Sets

SAGE Publications (2020). QS53a.xlsx. https://study.sagepub.com/node/22833/student- resources/check-your-understanding-data-sets

SAGE Publications (2020). QS53b.xlsx. https://study.sagepub.com/node/22833/student-

resources/check-your-understanding-data-sets

1. Open the .xlsx file

For the Week 9 Discussion, open the US Demographic Information_PA.xlsx file

you created and saved in Week 7.

2. Add the variable

For the Week 9 Discussion, add a new variable column labeled population size.

3. After adding your variable, enter the corresponding data (i.e., population size of each state) from your internet research. The example below shows fictitious data.

https://study.sagepub.com/node/22833/student-resources/check-your-understanding-data-sets

4. Conduct the statistical analyses. For the Week 9 Discussion, you will identify the correlation coefficient (i.e., Pearson Correlation) and determine statistical significance between population size and income in the United States for one chosen year. • The correlation function requires data in contiguous columns. If you want to avoid

including “political affiliation” as one of your correlations, you will need to move

(i.e., Copy/Cut and Paste) the columns on the worksheet so they are adjacent

(i.e., contiguous).

• Follow the instructions listed in the Salkind textbook for calculating correlations.

5. Generate the Scatterplot. a) Scatterplots can only compare two variables at a time, but the data columns

need not be contiguous. Select the column for the YEAR you will be correlating with population size by first clicking and dragging the data for the selected YEAR variable (being careful NOT to include the variable name), then while holding the CTRL button, click and drag the “Population Size” column data (again do not include the variable name in your selection).

b) On the Insert ribbon, find the Charts menu and select the Scatter chart from the XY chart pull-down menu:

The scatterplot will be displayed on your worksheet.

c) Include the linear Trendline: a. Click on the chart to open the Chart Design ribbon b. Locate the Add Chart Element pull-down menu and select Trendline:

Linear.

c. The Trendline will automatically be placed in your Scatterplot.

d) Double-click on the Chart Title so it reflects the variables used in the chart. Click on the outer edge of the chart so the entire chart is selected; copy and paste into your Word file or (graphics editor to save as a jpg) to post in the classroom:

Back to Table of Contents

-2,000,000

2,000,000

4,000,000

6,000,000

8,000,000

10,000,000

0 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000 90,000

Chart Title

-2,000,000

2,000,000

4,000,000

6,000,000

8,000,000

10,000,000

0 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000 90,000

Scatterplot of 2016 US Income and Population Size

How to Determine Statistical Significance of Correlated Data

(Week 9)

Excel does not directly provide p-values for correlations that are calculated through the

Correlation Tool or CORREL function.

For the purposes of this course, you will be determining the significance of a correlation

using a probability table. You will be examining the probability of significance based on

a two-tailed test.

1. Determine degrees of freedom.

a. Degrees of freedom are calculated based on the number of cases (i.e.,

number of participants) you compared – 2.

b. In the case of the US Demographic Information sample, you would

have:

51 – 2 = 49 (or 50 – 2 = 48 if you dropped one case due to missing data).

2. Look up the critical value for a correlation given the degrees of freedom (df) and

level of significance p).

a. Locate the column level of significance you wish to use; α .05 and α .01

are the typical values reported.

b. Locate the row for the df that corresponds to your sample. If your sample

size falls between two values on the chart, you can either interpolate the

value or be conservative and rely on the df in the table that is closest to

but less than your actual degrees of freedom. For example, if your sample

degrees of freedom is 32, you can use the df of 30 from the table to

determine the critical value.

For instances of this course assignment, degrees of freedom above 100

should refer to the df of 100 in the chart. Correlations smaller than .195 (p

< .05) and .254 (p < .01), while they may be significant, may indicate that

relatively little variance in the dependent variable was accounted for by the

independent variable (i.e., r2; 3.80% and 6.45%, respectively).

c. Identify the correlation coefficient that marks the intersection between the

α column and the df row.

d. If your calculated correlation is larger than the critical value listed in the

table, you can say that the probability of it being a Type I error (a “false

positive”) is less than 5% (i.e., p < .05), or less than 1% (i.e., p < .01; a

more stringent criterion than p < .05). If either one is the case, you have a

statistically significant correlation.

Back to Table of Contents

How to Run an Independent Samples t Test (Week10)

Course Text

Salkind, N. J. (2016). Excel statistics: A quick guide (3rd ed.). Sage.

● Excel Quickguide 49, “t-Test: Two-Sample Assuming Equal Variances”

Excel Practice Datasets

● Quick Guide Data Sets

SAGE Publications (2020). Q49.TTEST - EQUAL.xlsx. https://study.sagepub.com/node/22833/student-resources/quick-guide-data-sets

● Check Your Understanding Data Sets

SAGE Publications (2020). QS49a.xlsx. https://study.sagepub.com/node/22833/student- resources/check-your-understanding-data-sets

SAGE Publications (2020). QS49b.xlsx. https://study.sagepub.com/node/22833/student-

resources/check-your-understanding-data-sets

To conduct independent samples t-tests from the existing dataset, the data must first be

sorted according to political affiliation.

1. Open the US Demographic Information_PA_PS.xlsx file in Excel

2. Use CTRL + a in Windows (⌘ + a on Mac) to select the entire workbook. The

workbook will turn gray in color. NOTE: In order to use this method for identifying the

data, you must remove all other extraneous information on the worksheet, such as

tables, figures, other data output. Another option is to click and drag to highlight the

specific variable headings and data that will be sorted.

3. In the Data tab, choose Sort

https://study.sagepub.com/node/22833/student-resources/quick-guide-data-sets

https://study.sagepub.com/node/22833/student-resources/check-your-understanding-data-sets

4. Within the Sort wizard, check the box labeled “My data has headers” and use the

pull-down menu to Sort by “Affiliation”. Click OK.

Now the data are sorted by political affiliation and you can compare the income for a

particular year by two political affiliations (You will choose Red and Blue).

5. In the Data tab, choose Data Analysis, t-Test: Two-Sample Assuming Equal

Variances, and click OK. Decide which year you want to compare affiliations.

6. Select your comparison groups. In the t-Test wizard, click the RefEdit button for

Variable 1 Range and select the data for the RED states (i.e., click and drag the income level for the year you chose, but only for the RED states). The example below shows a fictitious Affiliation labeled Green.

7. In the t-Test wizard, click the RefEdit button for Variable 2 Range and select the data for the BLUE states (i.e., click and drag the income level for the year you chose, but only for the BLUE states). The example below shows a fictitious Affiliation labeled Mauve. Uncheck the box marked “Labels”.

Back to Table of Contents

8. Select where you want your output displayed. Within “Output options”, select the button for “Output Range:” and click the RefEdit button. Select a cell in your worksheet where you want your output to begin. Click OK to run the t-test.

9. Click the Variable 1 and Variable 2 cells and rename appropriately. Include a heading to identify which year you selected.

NOTE: Refer to the t Stat for the t-test. Use the two-tailed test of significance to determine the p value of the t-test.

Back to Table of Contents

How to Post a Visual Display to the Discussion Board

(Weeks 5, 6, 7, 9, and 10)

During the course, you will be asked to post visual displays of data you analyze in Excel (e.g., scatterplots, histograms, etc.). These visual displays appear in the Excel worksheet, and must then be captured and posted to the Discussion Board In order to post a visual display to the Discussion Board, you must first save the visual display as a JPEG image. Then, post the JEPG image into the Discussion Board. In Weeks 5, 6, 7, 9 and 10, you will use the following steps #1 – 7 to save visual displays as JPEG images. Below is a Week 5 example of how to post a histogram to the Discussion Board. Week 5 In Week 5, you create a visual display called a histogram which is included in the Excel worksheet. Use the following steps to save the histogram as a JPEG image and post it to the Discussion Board 1. Click on a blank area within the histogram chart so the entire chart is selected.

2. Copy the image using CTRL + c in Windows (⌘ + c on Mac). 3. Open your favorite image editor (e.g., Snip & Sketch, Paint, Paint 3D, GIMP,

Photoshop, GIMPshop, Paintshop Pro, Irfanview, and others)

Back to Table of Contents

4. Place your cursor inside the image editor blank field and press CTRL + v in Windows (⌘ + v on Mac) to paste the screenshot into the image editor.

5. Crop the pasted image to only include the histogram itself.

6. Go to File, then Save As, and choose JPEG picture.

7. Choose the location where you want to save the JPEG file (e.g., Desktop), give the histogram file a name, and click Save.

Back to Table of Contents

Once your visual display is saved as a JPEG, you can post it to the Discussion Board.

In Weeks 5, 6, 7, 9, and 10 you will use the following steps #8 – 13 to post a visual

display to a Discussion Board. Below is a Week 5 example of how to post a histogram

to the Discussion Board.

8. Go to Week Discussion and select REPLY button For the Week 5 Discussion, go to Week 5 Discussion and select REPLY button

Back to Table of Contents

9. Expand the Message toolbar by selecting the down-arrows icon

10. Select the “Insert/Edit Image’ icon

Back to Table of Contents

11. Retrieve your visual display file by selecting the ‘Browse My Computer’ button and opening the file from its location on your computer

For the Week 5 Discussion, retrieve your histogram chart file by selecting the

‘Browse My Computer’ button and selecting the file from its location on your

computer

12. When your visual display appears, enter an “Image Description” and “Title” for it. Under your visual display, write your Post requirement. Then, select the INSERT button. For the Week 5 Discussion, when your histogram chart appears, enter an “Image Description” and “Title” for it. Under your histogram, write your By Day 4 Post requirement (i.e., to interpret your histogram in terms of normality and explain your reasoning). Then, select the INSERT button.

13. Then, from the Discussion Board, click the SUBMIT button.

Back to Table of Contents