1 / 64

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

Welcome to the Southern Health Allied Health Research Unit: Research Training Session program for 2011. 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

moral
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related