KIN 506 Excel Template Lesson for data analysis



Goal: calculate the mean rate of force development for each of your MANY subject trials on a force plate, then average those trials.CREATE DATA ANALYSIS FOR FIRST FILECreate worksheet and calculate time duration of each sample (as single value in cell to use subsequently). Duration of 1 sample = 1/sample freqCreate worksheet for subject 1Copy in subj 1 data to column B.In column C insert time for each sample (start at 0, using relative address above + absolute address of time increment on previous worksheet)Prepare for future template: Some data samples may be for longer time. Add 25% to total time range. Total 5 sec in this example. Shade column B from first data point to cell next to last time point YELLOW (to mark range of possible data)Graph data: USE SCATTER PLOT WITH LINE (NOT LINE GRAPH). Use full YELLOW data range even though all does not have data in this case (next subject could). Format graph: add labels & units on both axis, remove gridlines and legend, adjust x-axis scale max to your max time (allow vertical axis to auto scale min & max to allow for different range on subsequent subjects). Reduce marker size.Add labels as shown below (values will come later)cell B2: Find Max Forcecell C2: Find time of Max force using =VLOOKUP(B2, B6:C6006,2,FALSE) [False allows lookup column to not be sorted]cell G2 & G3: Find baseline no force over first second (average & stdev.s)cell G4: Find value of mean baseline force + 4sIn column A: find first force to exceed 2s of baseline force “off baseline” (start of force rise). Use “off baseline” flag and IF statement =IF(B6>$G$4,"off baseline","")Cell B3: Find force at onset of force rise using =VLOOKUP("off baseline", A6:C6006,2,FALSE)Cell C3: Find time of onset of force rise using =VLOOKUP("off baseline", A6:C6006,3,FALSE)Add line plot of slope of Mean Rate of Force Development to plot: Select data, add…If points are not connected by line, add trendline between min & max points: Chart tools> Layout>trendline>more trendline options>select MRFD: Linear+solid blackCell D1 & D3: add labelsCell D2: Calculate MRFD: =(peak force-onset force)/(time of peak force-time of onset force)CONVERT DATA ANALYSIS FOR FIRST FILE INTO BLANK TEMPLATESAVE file!!Copy worksheet to end of tabs, rename to “TEMPLATE”Remove all data in yellow cellsCreate 3 copies of TEMPLATE worksheet, name them SUBJ2, SUBJ3, SUBJ4USE TEMPLATE TO ANALYSE SUBSEQUENT FILESCopy SUBJ2, SUBJ3, SUBJ4 data into yellow data area of worksheets.DO DATA SUMMARY IN EXCELCreate new worksheet DATA SUMMARYTitle columns SUBJECTS, VGRF MRFD (N/s)Set cells yellow over range mean is calculated forAbove, calculate Mean of range belowIn subject cells, set address to appropriate cells in individual subject worksheetsSIMILAR, BUT SIMPLER, PROCEDURE CAN BE USED TO BUILD TEMPLATE AND SUMMARY OF MULTIPLE STATISTICAL ANALYSIS (if excel is used for statistics). ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download