TURBOCALC 3.5 STATISTICAL ANALYSIS USING AREXX The ARexx script files are: ANOVA_OneWay.rexx ANOVA_TwoWay.rexx ANOVA_Twoway_NO_Rep.rexx Chi-Sq_2_Ind_Sam.rexx Correlation.rexx Covariance.rexx Descriptive_Stats.rexx Exponential_Smooth.rexx F_Ratio.rexx Histogram.rexx Moving_Average.rexx Rank&Percentile.rexx Regression.rexx T-test_Corr_Sample.rexx T-test_IndSamples.rexx T-test_IndSamples_UneqVar.rexx USING THE TOOLS All the tools work more or less in the same fashion. Where there are differences they will be pointed out . Start by creating the data you wish to analyse. The format of the data, in columns and rows, is explained below for each analysis tool. The Arexx macros can be started either from a Shell or from within TurboCalc. If started from a Shell, each file will check to see that TurboCalc is running and, if it is not, will start it and present the user with a "File/Open" requester. Load in the file you want to work on. To start from within TurboCalc, open the spreadsheet you have the data on , then open the "STATS_Macros" file. The macro sheet contains macros to start each of the Arexx files. Their names appear in the "Play..." macro requester. Make sure your data spreadsheet is active before choosing the Macro to play. If the file selection is okay, the macro will be run. You will then be asked to supply the program with some data. The most common is a request to enter both the cell range for data input and an output cell reference. This (and other program requests) will appear in normal requester windows. Enter the requested information and then press the key. The program will then proceed. Please note that when you give the program a cell reference for output, the program uses that reference as the top left cell in the output. All cells in the output area will be overwritten, so be careful to select cells that are empty. Once the ARexx file is running, the user is locked out to avoid any problems with stray mouse clicks or keyboard presses when the program stops. The screen display will not be updated until the calculations are finished - so be patient! NOTE: The Tables in the following sections look best if viewed with a fixed width font! ANALYSIS OF VARIANCE The analysis of variance is a method for dividing the variation observed in experimental data into different parts, each of which is assignable to a known source, cause or factor. The null hypothesis that is tested asserts that the samples are drawn from populations with the same mean. If any detected variation cannot be attributed to sampling error, then the null hypothesis is rejected. The assumptions underlying this type of analysis are: - the distributions of the variables in the populations from which the samples are drawn are normal. If the distributions are not normal, the results will appear more significant than they really are. Hence a more rigorous level of confidence may be required. - the variances in the populations from which the samples are drawn are equal (homogeneity of variance). Large departures from this assumption will seriously affect the results. This can be compensated for by transforming the variable to give a greater uniformity of variance, or if this is not possible, use a non-parametric procedure (see below). ONE WAY CLASSIFICATION The ANOVA_OneWay.rexx file performs a simple analysis of variance on two or more columns of data. Example: INPUT DATA RANGE: Method I Method II Method III Method IV 5 9 8 1 7 11 6 3 6 8 9 4 3 7 5 5 9 7 7 1 7 4 4 4 4 2 OUTPUT RANGE: ANOVA - One Way Group Count Total Mean Variance Method I 8 43 5.375 5.4107 Method II 5 42 8.4 2.8 Method II 7 43 6.1429 3.8095 Method IV 6 18 3 2.8 ANOVA Source of Variation SS df Variance Est F-Ratio Between Groups 82.2217 3 27.4072 7.0167 Within Groups 85.9321 22 3.906 Total 168.1538 25 `TWO-WAY CLASSIFICATION` The ANOVA_TwoWay.rexx file performs an analysis of variance on two or more columns of data. Each column includes more than one sample. * NOTE: The first column must contain labels in the first row of each sample. This ARexx program will request you to specify the number of rows to expect in each sample. * enter a number for the number of rows in each sample and press . Example: INPUT DATA RANGE: Bright Mixed Dull Free 26 41 36 41 26 39 28 19 59 92 59 27 14 82 87 16 86 99 29 45 126 31 37 104 Restricted 51 39 42 96 104 92 97 130 156 22 122 144 35 114 133 36 92 124 28 87 68 76 64 142 In this example, there are 8 rows in each sample. OUTPUT DATA RANGE: ANOVA - Two Way: With Replication Summary Statistics Bright Mixed Dull Total Free Count 8 8 8 24 Sum 277 395 577 1249 Mean 34.625 49.375 72.125 52.0417 Variance 609.6964 601.4107 1353.2679 1028.7373 Restricted Count 8 8 8 24 Sum 441 752 901 2094 Mean 55.125 94 112.625 87.25 Variance 925.8393 936.8571 1668.2679 1673.413 Col. Total Count 16 16 16 Sum 718 1147 1478 Mean 44.875 71.6875 92.375 Variance 828.65 1248.8959 1847.45 ANOVA Source of Sum of Degrees of Variance F Ratio Variation Squares Freedom Estimate Sample 14875.5208 1 14875.5208 14.6428 Column 18150.0416 2 9075.0208 8.9331 Interaction 1332.0417 2 666.0208 0.6556 Within Cells 42667.3744 42 1015.8899 Total 77024.9791 47 `TWO WAY CLASSIFICATION WITH NO REPLICATION` The ANOVA_TwoWay_No_Rep.rexx file performs an analysis of variance on two or more columns of data. Each column includes one sample. Example: INPUT DATA RANGE: Subject A B C D 1 31 42 14 80 2 42 26 25 106 3 84 21 19 83 4 26 60 36 69 5 14 35 44 48 6 16 80 28 76 7 29 49 80 39 8 32 38 76 84 9 45 65 15 91 10 30 71 82 39 OUTPUT RANGE: ANOVA - Two Way: No Replication Subject Count Total Mean Variance 1 4 167 41.75 782.9167 2 4 199 49.75 1466.9167 3 4 207 51.75 1344.9167 4 4 191 47.75 404.25 5 4 141 35.25 230.25 6 4 200 50 1072 7 4 197 49.25 486.9167 8 4 230 57.5 691.6667 9 4 216 54 1030.6667 10 4 222 55.5 621.6667 A 10 349 34.9 390.9889 B 10 487 48.7 388.9 C 10 419 41.9 751.8778 D 10 715 71.5 513.6111 ANOVA Source of Sum of Degrees of Variance F Ratio Variation Squares Freedom Estimate Rows 1565 9 173.8889 0.2787 Columns 7553.1 3 2517.7 4.0359 Interaction 16843.4 27 623.8296 Total 25961.5 39 TESTS OF SIGNIFICANCE: MEANS Experimental data very often require a comparison and evaluation of statistics obtained from separate samples or from the same samples for measurements obtained under two or more experimental conditions. The null hypothesis is that there is no difference exists between the two population statistics being measured, in this case the means. TWO MEANS FOR INDEPENDENT SAMPLES This test assumes that the distributions of the variables in the populations from which the samples are drawn are normal. It assumes also that these populations have equal variances. The T-test_IndSamples.rexx performs a t-test on two columns of data. Each column consists of one sample. Example: INPUT DATA RANGE: Group A Group B 16 20 9 5 4 1 23 16 19 2 10 4 5 2 OUTPUT RANGE: T-Test: Two Means for Independent Samples Group A Group B Count 8 6 Sum 88 48 Mean 11 8 Variance 60.1667 T Ratio 0.7161 Deg of Freedom 12 TWO MEANS FOR CORRELATED SAMPLES This test assumes that the distributions of the variables in the populations from which the samples are drawn are normal. It assumes also that these populations have equal variances. The T-test_Corr_Sample.rexx performs a t-test on two columns of paired data. Each column consists of one sample. There must be equal observations in each sample. Example: INPUT DATA RANGE: Stress Non-Stress 7 5 9 15 4 7 15 11 6 4 3 7 9 8 5 10 6 6 12 16 OUTPUT RANGE: T-Test: Two Means for Correlated Samples Mean of Diff. -1.3 Variance 12.2333 T Ratio -1.1754 Count 10 Deg of Freedom 9 TWO MEANS FOR INDEPENDENT SAMPLES WITH UNEQUAL VARIANCES This test assumes that the distributions of the variables in the populations from which the samples are drawn are normal, but that these populations have unequal variances. The T-test_IndSamples_UneqVar.rexx performs a t-test on two columns of data. Each column consists of one sample. Example: INPUT DATA RANGE: Sample A Sample B 74 66 69 62 70 63 71 64 73 65 68 61 71 63 61 55 OUTPUT RANGE: T-Test: Two Means for Independent Samples: Unequal Variances Sample A Sample B Count 8 8 Sum 557 499 Mean 69.625 62.375 Standard Error 1.8504 T Ratio 3.918 First Deg of Freedom 7 Second Deg of Freedom 7 F TEST FOR VARIANCES When there is a need to establish the significance of the difference between the variances of measurements for two independent samples, use the F test procedure. This relates directly to the use of other tests (such as the t test) where assumptions about the degree of homogeneity of the variances is critical. The F_Ratio.rexx performs an F test on two columns of data. Each column consists of one sample. Example: INPUT DATA RANGE: Group1 Group2 64 26 52 35 48 34 52 32 43 34 44 28 46 29 58 28 45 33 OUTPUT RANGE: F Test: Two-Sample for Variances Group1 Group2 Count 9 9 Mean 50.2222 31 Variance 49.6944 10.75 Degrees of Freedom 8 8 F ratio 4.6227 NON-PARAMETRIC TESTS The ANOVA and the t-test both assume normality of the parent distributions from which the samples are drawn. In experimental work, it is often the case that little is known about the population distributions or it is known that the distributions depart significantly from the normal form. In such cases, non-parametric tests may be appropriately used. SIGN TEST FOR TWO INDEPENDENT SAMPLES This test is also known as the median test as it compares the medians of two independent samples. The idea is that in two samples drawn from the same population the expectation is that as many observations in each sample will fall above as below the joint median. The null hypothesis is that no difference exists between the medians of the populations from which the samples are drawn. The Chi-Sq_2_Ind_Sam.rexx file performs a sign test on two columns of data. Each column consists of one sample. Example: INPUT DATA RANGE: Sample I Sample II 10 6 10 7 10 8 12 8 15 12 17 16 17 19 19 19 20 22 22 25 26 OUTPUT RANGE: Chi-Square Non-Parametric Sign Test of Two Independent Samples Positive Negative Sample I 7 5 Sample II 3 6 Median 16 Count 21 Chi-Square 0.4813 Deg of Freedom 1 MEASURES OF CENTRAL TENDENCY AND VARIATION The variation of one measurement from another is a common concern. Statistics is the study of variation. There are a number of statistics that have been developed to assess the degree and type of variability between two or more sets of data, including the mean, range, standard deviation and variance among others. `DESCRIPTIVE STATISTICS` The Descriptive_Stats.rexx file generates a set of univariate statistics for two or more sets of data in the input range. Example: INPUT DATA RANGE: Group A Group B 22 18 15 16 9 31 7 8 4 2 45 36 19 12 26 16 35 47 49 22 OUTPUT RANGE: Statistics Group A Group B Count 10 10 Sum 231 208 Mean 23.1 20.8 Mode None 16 Median 20.5 17 Range 45 45 Maximum 49 47 Minimum 4 2 Std. Error 4.9519 4.3097 Std. Deviation 15.6592 13.6284 Variance 245.2111 185.7333 Skewness 0.4588 0.5966 Kurtosis -1.0607 -0.5153 Confidence Level (95%)-low 13.3943 12.353 Confidence Level (95%)-high 32.8057 29.247 Confidence Level (99%)-low 10.3242 9.681 Confidence Level (99%)-high 35.8758 31.919 RELATIONSHIPS BETWEEN SETS OF VARIABLES The essential feature of the data used with these tools is that one observation can be paired with another observation for each member of the group. The data under consideration consists of pairs of measurements. The analysis is concerned over the degree of of simultaneous variation of the variables - that is, to what extent the change in one variable will lead to a change in the paired variable. COVARIANCE The Covariance.rexx file returns the average value of the product of deviations of observations from their respective means. Use this measure to assess the degree to which two pairs of data move with each other. Covariance is sensitive to the magnitude of the unit of measurement of each set , so that two pairs of similarly related ranges of data will yield different covariance values if the magnitudes of the data points vary (see the example data set and output values below). INPUT DATA RANGE: Trial A Trial B Trial C 2 5 40 3 4 30 6 12 110 5 8 70 OUTPUT RANGE: Covariance: UnGrouped Data Trial A Trial B Trial C Trial A 2.5 4.5 45 Trial B 4.5 9.6875 96.875 Trial C 45 96.875 968.75 CORRELATION The Correlation.rexx file returns the average value of the product of deviations of observations from their respective means (the covariance) divided by the product of their standard deviations. Use this measure to assess the degree to which two pairs of data move with each other. Correlation is independent of the units of measurement. Example: INPUT DATA RANGE: X Y 5 1 10 6 5 2 11 8 12 5 4 1 3 4 2 6 7 5 1 2 OUTPUT RANGE: Correlation: UnGrouped Data X Y X 1 0.575 Y 0.575 1 REGRESSION The Regression.rexx file performs a linear regression analysis on a set of observations using the least squares method. Use this tool to analyse how a single variable (the dependent variable) may be affected by another variable (the independent variable). Only two columns of data are allowed. The program asks the user to specify which column is to be considered the dependent variable. Example: INPUT DATA RANGE: IQ Reading Scores 118 66 99 50 118 73 121 69 123 72 98 54 131 74 121 70 108 65 111 62 118 65 112 63 113 67 111 59 106 60 102 59 113 70 101 57 In this example, the dependent variable is in the second column, so we would respond accordingly to the program question. OUTPUT RANGE: Least Squares Regression Predicted Values Axis Intercept (a): 67.8932 -11.2576 55.1486 Slope of Line (b): 67.8932 0.6708 69.9055 St. Deviation: 71.247 0.0813 54.4778 Value of t: 76.6132 8.2548 69.9055 Coeff. of Determination (r sq): 61.1855 0.8098 63.1978 Deg. of Freedom: 67.8932 16 63.8685 64.5393 63.1978 59.8439 57.1609 64.5393 56.4901 RANK & PERCENTILE The Rank&Percentile.rexx file analyses the data in a range to determine the relative standing of each value relative to the others. Example: INPUT DATA RANGE: Sample1 Sample2 60 75 65 89 90 93 45 66 66 78 66 84 89 97 92 100 75 75 OUTPUT RANGE: Sample1 Rank Percentile Percentile Sample2 Rank Percentile Percentile Rank Point Rank Point 45 9 6 0 66 9 6 0 60 8 17 12.5 75 7 22 12.5 65 7 28 25 75 7 22 12.5 66 5 44 37.5 78 6 39 37.5 66 5 45 37.5 84 5 50 50 75 4 61 62.5 89 4 61 62.5 89 3 72 75 93 3 72 75 90 2 83 87.5 97 2 83 87.5 92 1 94 100 100 1 94 100 HISTOGRAM The Histogram.rexx file analyses a single column of data to generate frequencies, cumulative frequencies and cumulative percentages. This allows you to see the distribution of the data set before embarking on more elaborate statistical procedures. NOTE: Only one column of data is allowed. The program will request you to nominate the start cell of a range of "bin" values, ie. class interval values. If no range is nominated, the program will create its own bin values. Example: INPUT DATA RANGE and BIN VALUE RANGE: Exam Scores 40 42 50 45 60 46 70 46 80 42 90 55 100 60 94 86 72 64 59 52 44 86 99 100 77 84 OUTPUT RANGE: Histogram Exam Scores Bin Frequency Cum. Freq. Cum. % 40 6 6 31.5789 50 3 9 47.3684 60 2 11 57.8947 70 2 13 68.4211 80 3 16 84.2105 90 2 18 94.7368 100 1 19 100 TIME SERIES EXPONENTIAL SMOOTHING The Exponential_Smooth.rexx file analyses the data in a single range to produce a new range of values that were predicted on the basis of the input range values. The program will produce each new value using a formula that incorporates a "damping factor" that determines to what extent the forecasts respond to errors in previous forecasts: Forecastt+1=Forecastt + a(At-Ft) where: a is the smoothing constant (1-damping factor); At is the input data at time t; Ft is the forecast value at time t. The default damping factor is 0.3. NOTE: the input range must NOT include labels in the first row of the column. The program will ask you to enter a damping factor. * enter a factor between 0.1 and 0.9, or press to use the default (0.3). INPUT DATA RANGE: 1 3 6 8 3 8 9 7 3 8 9 12 14 15 Note: There are no labels in the column. OUTPUT RANGE: 0 0 1 0 2.4 0 4.92 0 7.076 2.9685 4.2228 3.1179 6.8668 2.9971 8.3601 2.8445 7.408 3.0579 4.3224 3.1254 6.8967 3.0467 8.369 3.0996 10.9107 3.0988 13.0732 3.0302 Forecast Values St. Error Smoothing Constant= 0.7 MOVING AVERAGE The Moving_Average.rexx file analyses the data in a single range to produce a new range of values that were predicted on the basis of the average value of the data over a number of specific periods. The program will produce each new value using the following formula: Forecastt+1 = 1 * Sum(At-i+1) - N where: N is the number of prior periods to include in the forecast; At-i+1 is the input data at time t minus period i plus 1; Ft+1 is the forecast value at time t+1. The default period value is 3. NOTE: the input range must NOT include labels in the first row of the column. The program will ask you to enter the number of periods to use. * enter a factor of 2 or more, or press to use the default (3). INPUT DATA RANGE: 420 650 800 1420 1360 1600 2110 2400 Note: No labels are included in the selection. OUTPUT RANGE: 0 0 0 0 623.3333 0 956.6667 0 1193.3333 303.9993 1460 298.0132 1690 276.193 2036.6667 333.727 Forecast St. Error Interval= 3