Richard Baldy College of Agriculture

2. Richard Baldy College of Agriculture One-Click Anovas – Analysis of Variance with Microsoft Excel

3. Overview of Presentation • Demonstration of “One-Click ANOVA” with an experiment set out in a Latin-square design. • Goal of project: develop data analysis software agriculture undergraduates would use in college in their post college careers • Why the goal? • Software creation

4. Demonstration of “One-click Anova” Latin square randomization Latin-square design with 1 experimental factor

5. Background for Developing Data Analysis Software Agriculture Undergraduates Would Use in College and After Graduation • Snapshot of California’s agriculture • Industry input into curriculum development • Our students

6. Features of the Industry Sophisticated producers of 350 different crops and commodities in the nation’s most populous state

7. Features of the industry continued • Public demands fewer chemical inputs • Yet, public expects industry to do the research • Industry economically powerful. Has the money • Public cannot support research of 350 commodities • Research needs local ecosystem focus to advance goal of ecologically sound agricultural production • Much on-farm research yields results once/yr • Easy to forget involved data analysis procedures

8. Characteristics of our students Most will not attend graduate school

9. They will enter industry. Some will teach high school agriculture

10. Industry Input • Continue to give hands-on experience • Integrate curriculum around ecosystem concepts • Teach problem solving: • Working in emotionally “charged” settings • Leadership • Experimentation including data analysis –

11. Experimentation, Data Analysis Course “Dick, you are a plant physiologist, develop and teach this course.” Expectations: • Undergraduates experiment and analyze data • They report research via scientific papers, posters, web pages and seminars • And continue as farmer-researchers, consultant-researchers, high school teacher-researchers

12. Faculty Wish List • Completely randomized design • Randomized complete block design • Latin-square design • Split-plot design • Ancova • Simple and multiple regression • Analysis of count data

13. Why Excel? • Students familiar with Excel. Therefore, teach data analysis, not a program –saves time • Students’ computers come with Excel – saves $ • Graduates use Excel for other purposes, not just data analysis • Specific experimental design templates friendlier than general purpose programs e.g., SYSTAT • OK for regression

14. Why Not SYSTAT? • Expensive. Cheaper student version lacks required capability such as split-plot • My experience. Used daily use for weeks. Left it for 3 months. Had to relearn • Number codes for treatments are confusing • For split-plot have to recalculate Anova • Remember ag research data analyzed once/yr

15. Excel’s Limits As an Anova Platform • “Out of the box” Anova procedures handle few designs; Do not handle missing data • No mean separation tests • No orthogonal contrasts • No automatic charting of treatment means

16. Text book formulas Overcoming Excel’s Limitations Such formulas are not for a world where experimental cows die – become missing data. No residuals for testing normality, equal variance

17. Instead Use Method That Gives Residuals For missing data, use iteration to find values that gives residual total = zero

18. The Right Model • Example: randomized block design, single experimental factor. • Need to solve for three sums of squares: Error Block Treatment

19. Model for Error Term • Treatment Mean + Block Mean – Grand Mean • Subtract the above estimate from datum to obtain residual • Square residuals • Sum of squared residuals = Error SS

20. Error SS confounded with block SS From this sum of squares subtract the error term’s sum of squares. Block SS Error SS Treatment SS Treatment SS Model for Block Term • Datum - treatment mean = residual • Square and sum residuals = confounded error and block SS

21. Model for Treatment Term Error confounded with treatment SS From this sum of squares subtract the error term’s sum of squares. Error SS Treatment SS Block SS Block SS • Datum – block means = residual • Square and sum residuals = confounded error and treatment SS

22. Circular argument – need careful control of calculation order to avoid crashes Finding Correct Substitute Data • Pivot table gives means to be used in estimates. • Set substitute datum to some value, e.g., 0 • Substitute datum – estimate = residual • New substitute datum = former substitute datum – residual • Refresh pivot table, obtain new means for estimates

23. Let’s see how a pivot table summarizes these data

24. These are just data in this pivot table and are not used in computing estimates. If this were a Factor A x Factor B summary, these numbers would also be means and used in estimates

25. Looked up in pivot table Residual = Response-Estimate. Thus, 0.0 – 44 = -44 Response = Response-Residual. Thus, New response = 0- (-44) = 44 calculate

26. refresh

27. calculate

29. How to handle different sized data sets • Have pivot table summarize 65,000+ rows. • Simple to program • Takes 5-10 minutes for analysis. Rodney’s autofill code + ASSUME page tip on Offset function  cut run times 70 – 90%

30. Neville’s time trimming suggestion • Refreshing pivot tables for each iteration = 1-2 minutes/analysis • Use DAVERAGE function

32. DAVERAGE(DataRange, ResponseColumn, Criteria for selecting responses to average) INDIRECT(HC1))

34. Further Topics • Experiment planning • Formulate additional, orthogonal hypotheses • Estimate number of replicates • Additional work

35. Experiment planning: Formulate additional hypotheses. Test with single degree of freedom orthogonal contrasts

36. An Example of Estimating Number of Replicates. Example Will Be for Randomized Complete Block Design. # reps RBD 1 experimental factor

37. Additional Work • Simplify mean separation tests for factorial designs • Example using split-plot design Split-plot design with blocks

38. Additional Work (Continued) • Replace trial and error method to develop models • Open development to others • Share programs