basic data analysis using ms excel - uconn 4-h · 2018. 12. 13. · basic data analysis using ms...
TRANSCRIPT
![Page 1: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/1.jpg)
Basic Data Analysis Using MS Excel
Teresa McCoy Assistant Director, University of Maryland Extension
Evaluation & Assessment
![Page 2: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/2.jpg)
First Things First: ToolpakClick on “File” then on “Op1ons” Click on “Add Ins” then on “Manage … Go”
![Page 3: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/3.jpg)
Click on the box, “Analysis Toolpak,” and Then click on “OK” You will see the “Data Analysis” option appear on the right-hand side of your ribbon.
![Page 4: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/4.jpg)
Overview
I. Data Management II. Data Manipulation III. Data Analysis IV. Data Communication
![Page 5: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/5.jpg)
Preface
Relax and just listen Beginner pace Exposure to the Excel platform Exposure to T-Tests Tips & Tricks Support Materials
![Page 6: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/6.jpg)
1) Sort 2) Filter Tip: Auto- or Flash-fill
Data Manipulation
![Page 7: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/7.jpg)
Sort Select the data in my spreadsheet. Go to “Data” tab. Select “Sort.” I’m going to sort by Column B (county) in alphabetical order.
![Page 8: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/8.jpg)
Sorted by Column A alphabe1cally Sorted by Column B alphabe1cally and then by Colum C smallest to largest
![Page 9: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/9.jpg)
Sort & Filter
![Page 10: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/10.jpg)
Auto or Flash Fill: Can fill in the same value or formula or a sequence
Grab the handle at the bottom right corner. Hold and drag
There is also a “Fill” op/on on the Home Menu Ribbon where you can set up mul/ple fill op/ons.
![Page 11: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/11.jpg)
III. Data Analysis
![Page 12: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/12.jpg)
CaveatMultiple ways to go about your analysis. 1) Toolpak
2) Formulas 3) Manual entry
![Page 13: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/13.jpg)
Descriptive Stats Using Toolpak
N=149
![Page 14: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/14.jpg)
Example: Typing in a Formula
![Page 15: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/15.jpg)
Using Formulas from the Formula Tab
P-‐Value shows as .36
![Page 16: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/16.jpg)
III. Data Analysis
![Page 17: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/17.jpg)
![Page 18: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/18.jpg)
Review Main Points
• T-test looks for significant difference between two sets of data. • Eliminates our bias. • Science wants a 95% confidence level. • T-test returns the p-value (probability). • Want a p-value that is <.05 • Because that says the difference in the data
is NOT attributable to random chance.
![Page 19: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/19.jpg)
Review Main Points
If the results are not attributable to chance, Then our program intervention worked.
![Page 20: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/20.jpg)
Question
Is there a significant difference between girls and boys in the number of hours spent in 4-H activities each week?
Open-ended; therefore a two-tailed t-test
![Page 21: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/21.jpg)
Question
Do boys spend more time each week in 4-H activities than girls?
Closed-ended; one direction; therefore a one-tailed t-test
![Page 22: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/22.jpg)
Paired or Unpaired Data
Paired
![Page 23: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/23.jpg)
Paired or Unpaired Data
Unpaired
![Page 24: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/24.jpg)
Let’s go back to a question
Is there a significant difference between girls and boys in the number of hours spent in 4-H activities each week?
Open-ended; therefore a two-tailed t-test
![Page 25: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/25.jpg)
Two-tailed T-Test on Unpaired Data Using Formula Bar
![Page 26: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/26.jpg)
T-Test Result of p=.36
Can we be confident that there is a significant difference between girls and boys in the number of hours spent in 4-H activities each week?
No
![Page 27: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/27.jpg)
Two-tailed T-Test on Unpaired Data Using Toolpak
![Page 28: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/28.jpg)
Remember,
• Look back at the video. • Don’t be intimidated. • Ask for help.
![Page 29: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/29.jpg)
I. Data Management
Data Entry Quality Tool = Data Validation
![Page 30: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/30.jpg)
Data Validation1) Select the cells that you
want to include in the data validation.
2) Choose an empty space in your spreadsheet and type in the answers you will accept.
3) Click on your data validation option.
This is the area on the sheet for data entry.
These are the only entries I will allow in the cells.
![Page 31: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/31.jpg)
Data Validation
4) The data validation dialog box will open.
5) In “Allow,” choose the drop-down box and choose “List.”
6) You will then have to tell Excel what list to use.
7) Go back to the area of the sheet where you typed in the allowable entries and drag across those boxes.
![Page 32: Basic Data Analysis Using MS Excel - UConn 4-H · 2018. 12. 13. · Basic Data Analysis Using MS Excel Teresa McCoy Assistant Director, University of Maryland Extension ... &on&“Manage&…&Go”&](https://reader036.vdocuments.us/reader036/viewer/2022063010/5fc28a2f472aaa28f878b93b/html5/thumbnails/32.jpg)
Data Validation
• Users can only choose what you have listed.
• This eliminates user error in data entry.