1 / 12

Obtaining Summary Statistics and Plots by Treatment Groups in EXCEL

Obtaining Summary Statistics and Plots by Treatment Groups in EXCEL. Data Format. Data are in a Block of ( n +1) rows and ( f+m ) columns where: Variable labels are in row 1 Data on n units are in rows 2: n +1 f Columns of factor levels (one column per factor)

locke
Download Presentation

Obtaining Summary Statistics and Plots by Treatment Groups in EXCEL

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. Obtaining Summary Statistics and Plots by Treatment Groups in EXCEL

  2. Data Format • Data are in a Block of (n+1) rows and (f+m) columns where: • Variable labels are in row 1 • Data on n units are in rows 2:n+1 • f Columns of factor levels (one column per factor) • m Columns of response measurements (one column for each response variable) • For this presentation, we will assume • n=24 • f = 2 (With Factor A at 2 Levels, B at 3 Levels) • m = 2 Response Variables (X , Y)

  3. Data Layout Row 1 Data in Columns A-D • Highlight the data, then click: • Insert • Name • Define • type in a name (e.g. expdat) • OK Row 25

  4. Obtaining Summaries for 1 Factor (I) • Go to cells outside the range of the data (different columns) • Type in the Factor label in as many cells in a row as there are levels • In the cells below them, enter the factor levels • Example: Factor B has 3 levels Column G Column H Column I Row 1 Row 2

  5. Obtaining Summaries for 1 Factor (II) • For Summaries of X, In cell G3, Type: • =DCOUNT(expdat,”X”,G1:G2) • Copy/Paste G3 to Cells H3,I3 • Repeat for the following cells/commands: • G4: =DAVERAGE(expdat,”X”,G1:G2) • G5: =DVAR (expdat,”X”,G1:G2) • G6: =DSTDEV (expdat,”X”,G1:G2) • G7: =DMIN (expdat,”X”,G1:G2) • G8: =DMAX (expdat,”X”,G1:G2) Column F, Row 1 The first element in each command is the database (could have entered $A$1:$D$25 instead), the second element is the variable to be analyzed, the third is the criteria (e.g. Factor B at Level 1)

  6. Obtaining Summaries for 1 Factor (III)

  7. Obtaining Summaries for Combinations of Levels of 2 Factors (I) • List the 2 Factor names in 2 side-by-side cells • Below them enter their factor levels (There will be ab pairs of cells Column G H I J K L M N O P Q R Row 11 Row 12

  8. Obtaining Summaries for Combinations of Levels of 2 Factors (II) • Similar to process for levels of one factor, except criteria are in pairs of columns. • Put Results in (say) the righthand of the 2 columns • In cell H13, type: =DCOUNT(expdat,”X”,G11:H12) • Copy and Paste cells G13:H13 to cells I13:R13 • Repeat Process for mean, variance, etc

  9. Obtaining Summaries for Combinations of Levels of 2 Factors (II) • To obtain a “factor effect/interaction” plot: • List the factor levels of the factor with most levels in a column • In successive columns give the means for the combinations of that factor’s level and the various levels of the other factor

  10. Factor Effect/Interaction Plot • Highlight the entire field on previous slide, then click on: • Chart Wizard • XY(Scatter) • Series in Columns • Picture with points and jagged (not smooth) lines • Complete Dialog Box (Select separate chart window) • Experiment with format by right clicking on parts of graph and formatting. • Copy and Paste into word processor

More Related