Welcome to the Southern Health Allied Health Research Unit: Research Training Session program for 2011

1. Welcome to the Southern Health Allied Health Research Unit:Research Training Session program for 2011

2. Today • Networking • General introduction to Southern Health Allied Health Research Unit and the 2011 Research Training Session program • Today’s session • Using Excel for Research • Planning for future sessions

3. The Southern Health Allied Health Research Unit • A collaborative network • Monash University & Southern Health allied health researchers

4. The Southern Health Allied Health Research Unit • Aims to • facilitate the development of research by and in collaboration with allied health clinicians • conduct innovative research that advances clinical practice • enable allied health clinicians to judiciously use evidence to guide their clinical practice.

5. How we operate Clinical practice Research Clinicians Clinician as researchers Researchers

6. How we operate

7. Research Training Sessions • Networking • Academic presentation • Clinician researcher / research student presentation • More networking

8. Research Training Sessions Clinical practice Research Clinicians Clinician as researchers Researchers

9. Workshop:Using Excel for research Assoc Prof Terry Haines Director, Allied Health Research Unit, Southern Health Director of Research, Southern Physiotherapy Clinical School, Monash University National Health and Medical Research Council Career Development Awardee (2010-2013) terrence.haines@monash.edu

10. Using Excel for research • Introduction to Excel • Arranging data in Excel • Cleaning data in Excel • Validating data entry in Excel • Basic formulae • Cutting and pasting with functions and formulae • If commands • Basic statistics commands • Some quick short-cut keys • Graphs and integrating with power-point

11. Introduction to Excel • Spreadsheet program • Very flexible for data input, data manipulation • Very flexible for developing graphs / charts • Some basic analysis tools available • I use it at some stage with just about every project I conduct

12. Scroll bars Commands ribbon Data Other worksheets Column labels

13. Arranging data in Excel • Wide format • One row of data per participant • Long format • One row of data per assessment or point in time • Multiple rows per participant

14. Wide format

15. Long format

16. Sorting data

17. Long format

18. Does it matter? • Sorting and arranging data in Excel does matter if you want to create graphs or use Excel’s statistical analysis functions • It may be easier to enter data in long format in some studies, or in wide format in others • Long usually takes more cells • In our example, 128 cells vs 224 for the same information • Other programs can convert one format to another with a click of a button • Will need specific column heading terminology

19. Reshaping data goes from this

20. To this

21. Cleaning data in Excel • Cleaning • Identify and address incomplete, incorrect or inaccurate data • Small datasets • Can do it visually • Large datasets • Takes too long to do visually • Excel can help

22. Check the range of your data using a chart To highlight a column, click on the column letter at the top, or click and drag, or use the arrows with a shift (+/- ctrl) key

23. Simple 2D column graph

24. Something isn’t right Value >100 These numbers are just the order of data – value is meaningless

25. Check out what the value is

26. Can do two columns at a time

27. Also helps pick up the problem Note: Excel does not label the axis very well unless you tell it to. The X-axis here is Barthel, the Y-axis is QoL

28. Cleaning data • Other options • Sort data to check range is correct • Be careful not to permanently rearrange your data • Always have a master file • Clean data in a separate file • Save as different versions as you go along • Use “if” commands to identify incorrect values • Use Ctrl – H to find a specific value • Don’t enter incorrect data in the first place

29. Validating data entry • Restricts values in a particular column to a certain amount or set of values • Enter your column headings first

30. Select the column you want first, then find “Data Validation”

31. Select the type of data you want to enter

32. Specify the range

33. So when I now enter a dodgy value, an error message pops up

34. Data validation • Will not detect an incorrect value if the incorrect value lies within the range that has been specified • If a person really wants to bugger up the data entry, there are not many things a program like Excel can do to stop them

35. Important to track meaning of numerals that you use

36. Use the comments to leave a trail A red triangle in the top right corner of a cell tells you there is a comment there. Wave your mouse over this see what comment says.

37. Basic formulae • Simple mathematical commands are easily implemented using Excel • Remember BODMAS conventions • Remember to have the same number of ( as you do ) in one formula

38. I want to calculate change in Barthel score

39. I press enter and...

40. I click and drag from the bottom right corner of that cell

41. And the formula has filled down the column

42. Copying and pasting with formulae

43. Click on the top cell from where you want the data to be pasted....But what is this new data?

44. If we wanted QoLChange, then we needed to paste one column further over.

45. Copy and paste conventions with formulae • It is the formula that is pasted, not the value • If you paste one column to the right, then all the values in the formula will change to being one to the right • If you paste one row down, then all the values in the formula will change to being one further down • If you don’t want this, you need to “paste special”

46. If commands • I use these commonly when manipulating datasets • Syntax • =if(logical test,[value if true],[value if false]) • Eg. I want a column that identifies older people • =if(b2>75,1,0)

47. I want a column that identified older people

48. Fill down to apply to the whole dataset

49. You can use up to 5 if commands in the one cell, eg. To identify old men

50. Fill down again