Transcript Slide 1
Intro to Excel Spreadsheets Nancy Clark FSU College of Medicine 1 What are they used for? Financial forecasting Statistical analysis Data collection Data manipulation Summarizing data Communicating data 2 Budgets Research Gradebook Charts Big tables Job aids Spreadsheet Construction 3 Columns (letters) Rows (numbers) Worksheets Cells (E9) Mousing Around a Cell Mon Highlight/Select Move Contents Fill Contents 4 Tue Cell Formats Words Numbers 5 # decimal places $ % Days/Dates Formulas Calculations… 45.2719 45.3 3/5/2002 5-Mar-02 .2719 27% 5-Mar STUDENT $45.27 Formulas =A2+B2+C2 =SUM(A2:AS2) $29.00 $1,886.00 AutoSum Formula Results 6 Formulas =Today() Average(range) Count(range) Max(range) Column (C2:C80) Row (B2:AK2) Block IF(logical_test,value_if_true,value_if_false) (A2:G50) =IF(F3>89,"A",IF(F3>79,"B",IF(F3>69,"C","F"))) =SUM (A2:AS2) Function Range Formulas can be Filled Down Insert menu, Function…, Help on this function 7 More on Cell Formats Alignment Word wrap Orientation Shrink to fit Font 8 More on Cell Formats Width Height 9 Merging and Splitting 10 Add to Formating Toolbar Highlight cells to merge Click Merge Button Unmerge Copying and Pasting Highlight cells Hit Copy (CTRL-C) Dotted lines Click on target cell 11 Top of area Hit Paste (CTRL-V) Unless still dotted not in Clipboard CTRL to select multiple targets Worksheets Open U:\Nancy Clark\Spreadsheets\Handbase Pt Encounters.xls 12 Sorting Data Entire worksheet of tabular data with headings Data menu, Sort… Small section of worksheet Highlight section Hit sort button 13 Formulas that Reference Other Worksheets =COUNT(Allison!B2:Allison!B100) 14 Allison!B2 -- cell B2 on sheet Allison Copying Worksheets Edit menu Move or copy sheet… 2 1 15 3 Adding and Deleting Sheets To add a sheet Insert menu Worksheet To delete a sheet Edit menu 16 Delete sheet Page Setup (Format) 17 Landscape or Portrait Print to 1 page wide Margins Headers and Footers Print Titles Making Sheets Pretty Do it yourself method Fill Color Font Colors Borders toolbar Autoformat (Format) Conditional Formatting 18 Charts 19 Visual representations of data Select Data Insert menu, Chart… Charts – Step 1 20 Charts – Step 2 21 Charts – Step 3 22 Charts – Step 4 23 Charts – Final Patients by Age Group 0% 5% 5% 16% 0 - 3 wks 1 mo - 10 yrs 74% 11 - 24 yrs 25 - 64 yrs 65 yrs+ 24 Different Scales Score on Survey % Not Installed PDxMD 4.76 8 InfoRetriever 4.80 7 ePocrates 4.87 8 Stedmans 4.65 9 CP Onhand 4.45 22 25 PDA Software 25 20 15 Score on Survey % Not Installed 10 5 0 PDxMD 26 InfoRetriever ePocrates Stedmans CP Onhand Printing Always Print Preview Page Setup Select Print Area 27 Highlight area File, Set Print Area File, Clear Print Area Insert Excel Spreadsheets Copy and Paste Use Insert Microsoft Excel Worksheet button Double click on table or chart Calls up Excel 28 Suggestion for Spreadsheet Summarize your patient data No data? Do the composite. Do a chart of some part of the data Put the data sheet in as well 3 sheets: Data Summary Chart 29 Statistical Analysis Using Excel 30 Descriptive Statistics Distribution 31 frequency distribution Histogram Central tendency Mean Median mode Dispersion Range Standard deviation Variance N Not P (inferential stats) Central Tendency Mean Median =MODE(A2:A7) N 32 =MEDIAN(A2:A7) Mode =AVERAGE(b2:b1500) =COUNT(A2:A1500) =COUNTBLANK(A2:B5) Dispersion Range Standard deviation =STDEV(A2:A110) Variance 33 =MAX(A2:A60)- MIN(A2:A60) =VAR(A2:A110) Distribution Frequency distribution Not easy – use SPSS FREQUENCY(data_array,bins_array) Use help Histogram 34 Bar chart of frequency table Data Tables Field names at top Each row is a record (sample) Sorting whole table By one column By more than one column 35 Sorting individual sections Hands on experience 36 Analyze data in examples.xls Sensitivity, Specificity sensitivity = a / (a+c) specificity = d / (b+d) Patient Patients s with without disease disease Test is positive Test is negativ e 37 a b c d