Data handling and analysis in Excel Stats Club

Data handling and analysis in Excel Stats Club 3: Dec 2013 David Gardner

References for today • Excel help menu? • Excel for dummies? • MS online help

Data entry analysis • Numerical variables – enter them with the same precision as they are measured, and use a consistent unit of measurement – If you are measuring kilograms • E. g. record 5. 3 kg, not just 5 kg • Stick with kilograms, and convert pounds to kilograms • But for analysis use only numerical (e. g. 5. 3) OR categorical variables (e. g. A, B, C etc…). Programs cannot analyse numbers and letters.

Important steps • Keep a copy of all raw data/ raw data sheet – Home>format>move or copy sheet I also reiterate all below in previous session • • Check the original when an error is found Save a new version with each change made Keep a record of all versions/changes Make sure you can retrace your steps if necessary!

Pivot table for describing data • Open ‘data handling using excel’ • Create a pivot table – What is average distance run by a female on day 3? • Row = Gender • Column = day • Values = distance (‘average of’ – value field settings)

Data add-ins: t-test • Open ‘data handling using excel’ • Go to file>options>add-ins – Is Analysis Tool. Pak active? • Not VBA (visual Basic programming) • click OK • Sort data by gender>day – Establish mean of female/male day 1 – Are they sig different? • T-test in excel?

Data add-ins: t-test • Go to data ribbon in excel – ‘data analysis’ • Choose test ‘ t-test: two sample unequal variance>OK • Highlight two ranges for testing (variable 1, variable 2…) – Hypothesised mean difference, ‘ 0’ or blank – Output range: click cell in current sheet – OK

Data add-ins: descriptive • Go to data ribbon in excel – ‘data analysis’ • Choose descriptive statistics – Grouped by columns – Labels in first row – Output range >click cell in current sheet – Check summary statistics • Play with rows/columns to get descriptive stats on given dataset

Data add-ins: other • Remember, Excel Help is v good for reminders

Data add-ins: other • Can do lots of other basic analyses using this function • I encourage you to try and play with some data • BE AWARE, USE WITH CARE – I have hardly ever encountered datasets that are so simple that this analysis is appropriate.

Excel basics: commands • Concatenate function – Open ‘Excel commands’ – See sheet ‘concatenate’ • Vlookup function – Open ‘Excel commands’ – See sheet ‘Vlookup’ • If function – Open ‘Excel commands’ – See sheet ‘IF’