MS-EXCEL: ANALYSIS OF EXPERIMENTAL DATA
Rajender Parsad and Shashi Dahiya I.A.S.R.I., Library Avenue, New Delhi-110 012
The inbuilt functions and procedures of MS-EXCEL can be utilized for the analysis of experimental data. Some of the inbuilt functions and procedures are given below:
1. Analysis of Variance
In the Tools menu, the option of Data Analysis can be used for carrying out following types of Analysis of Variance (ANOVA):
I. ANOVA: Single Factor
II. ANOVA: Two Factor With Replication III. ANOVA: Two Factor Without Replication
I. ANOVA: Single Factor
This option can be used for analysis of one-way classified data or data obtained from a completely randomized design. In this option, the data is given either in rows or columns such that observations in a row or column belong to one treatment only.
Accordingly, define the input data range. Then specify whether, treatments are in rows or columns. Give the identification of upper most left corner cell in output range and click OK.
In output we get replication number of treatments, treatment totals, treatment means and treatment variances. In the ANOVA table besides usual sum of squares, Mean Squares, F-calculated and P-value, it also gives the F-value at the pre-defined level of significance.
II. ANOVA: Two Factor With Replication
This option can be used for analysis of two-way classified data with m-observation per cell or for analysis of data obtained from a factorial CRD with two factors with same or different levels with same replications.
Suppose there are two factors A and B with levels ‘a’ and ‘b’ respectively and there are ‘m’ replications for each of the treatment combinations. Prepare the (a+1) x (bm+1) data sheet. Define it in the input range. Define numbers of rows per cell as ‘m’, define α and output range.
III. ANOVA: Two-Factors Without Replication
This option can be utilized for the analysis of two-way classified data with single observation per cell or the data obtained from a randomized complete block design. Suppose that there are ‘v’ treatments and ‘r’ replications, then prepare a v x r data sheet. Define it in input range, define alpha and output range.
2. Transformation of Data
Most commonly used transformations in the analysis of experimental data are Arcsine, Logarithmic and Square root. These transformations of data can be carried out using the following options.
Arcsine (ASIN)
It gives the arcsine of a number. The arcsine is the angle whose sine is number and this number must be from -1 to 1. The transformation is to be used on square root of this number. The returned angle is given in radians in the range -π/2 to π/2. To express the arcsine in degrees, multiply the result by 180/π.
For this go to the CELL where the transformation is required and write = ASIN (Give Cell identification for which transformation to be done)*180*7/22 and press ENTER. Then copy it for all observations.
Examples
ASIN (0.5) equals 0.5236 (π/6 radians) ASIN (0.5)*180/PI equals 30 (degrees)
Logarithmic (LN)
It gives the natural logarithm of a positive number. Natural logarithms are based on the constant e (2.71828182845904).
For this go to the CELL where the transformation is required and write =LN (Give Cell Number for which transformation to be done) and press ENTER. Then copy it for all observations.
Examples
LN (86) equals 4.454347 LN (2.7182818) equals 1 LN (EXP(3)) equals 3 EXP (LN(4)) equals 4
Some other related functions with LN options are:
EXP Returns e raised to the power of a given number IMLN Returns the natural logarithm of a complex number IMLOG10 Returns the base-10 logarithm of a complex number IMLOG2 Returns the base-2 logarithm of a complex number LOG Returns the logarithm of a number to a specified base LOG10 Returns the base-10 logarithm of a number
Square Root (SQRT)
It gives square root of a positive number. For this go to the CELL where the transformation is required and write =SQRT (Give Cell No. for which transformation to be done+0.5) and press ENTER. Then copy it for all observations. However, if number is negative, SQRT returns the #NUM! errorvalue.
Examples
SQRT (16) equals 4
SQRT (-16) equals #NUM! SQRT (ABS(-16)) equals 4
3. OTHER OPTIONS
Some other useful options that can be used in the Experimental Data Analysis are SUM, SUMSQ, MMULT, MINVERSE, TRANSPOSE and IF statement.
SUM
It gives the sum of all the numbers in the list of arguments. For this go to the CELL where the sum of observations is required and write =SUM (define data range for which the sum is required) and press ENTER. Instead of defining the data range, the exact numerical values to be added can also be given in the argument viz. SUM (number1, number2, ...), number1, number2,... are 1 to 30 arguments for which you want the sum.
Examples
If cells A2:E2 contain 5, 15, 30, 40, and 50: SUM (A2:C2) equals 50
SUM (B2:E2, 15) equals 150 SUM (5,15) equals 20.
Some other related functions with this option are
AVERAGE Returns the average of its arguments
COUNT Count how many numbers or values are in a list of or COUNTA arguments
PRODUCT Multiplies its arguments
SUMPRODUCT Returns the sum of the products of corresponding array components
Sum of Squares (SUMSQ)
Gives the sum of the squares of the list of arguments. For this go to the CELL where the sum of squares of observations is required and write =SUMSQ (define data range for which the sum of squares is required) and press ENTER. Instead of defining the data range, the exact numerical values to be added can also be given in the argument viz. SUMSQ (number1, number2, ...), Number1, number2,... are 1 to 30 arguments for which you want the sum.
Example
If cells A2:E2 contain 5, 15, 30, 40, and 50 SUMSQ (A2:C2) equals 1150
SUMSQ (3, 4) equals 25
MATRIX MULTIPLICATION (MMULT)
It gives the matrix product of two arrays, say array 1 and array 2. The result is an array with the same number of rows as array1, say a and the same number of columns as array2, say b. For
getting this mark the a x b cells on the spread sheet. Write = MMULT (array1, array 2) and press Control + Shift + Enter.
The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
Array1 and array2 can be given as cell ranges, array constants, or references.
If any cells are empty or contain text, or if the number of columns in array1 is different from the number of rows in array2, MMULT returns the #VALUE! error value.
Some other Related Functions of MMULT are
MDETERM Returns the matrix determinant of an array MINVERSE Returns the matrix inverse of an array TRANSPOSE Returns the transpose of an array
MATRIX INVERSE (MINVERSE)
It gives the inverse matrix for the non-singular matrix stored in an square array, say of order p i.e. an array with equal number of rows and columns. For getting this mark the p x p cells on the spread sheet where the inverse of the array is required and write = MINVERSE (array) and press Control + Shift + Enter.
Array can be given as a cell range, such as A1: C3; as an array constant, such as {1,2,3;4,5,6;7,8,9}; or as a name for either of these.
If any cells in array are empty or contain text, MINVERSE returns the #VALUE! errorvalue.
Inverse matrices, like determinants, are generally used for solving systems of mathematical equations involving several variables. The product of a matrix and its inverse is the identity matrix the square array in which the diagonal values equal 1 and all other values equal 0.
As an example of how a two-row, two-column matrix is calculated, suppose that the range A1:B2 contains the letters a, b, c, and d that represent any four numbers. The following table shows the inverse of the matrix A1:B2:
Column A Column B Row 1 d/(a*d-b*c) b/(b*c-a*d) Row 2 c/(b*c-a*d) a/(a*d-b*c)
MINVERSE is calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the cancellation is not complete.
Examples
MINVERSE ({4,-1;2,0}) equals {0,0.5;-1,2}
TRANSPOSE
For getting the transpose of an array mark the array and then select copy from the EDIT menu. Go to the left corner of the array where the transpose is required. Select the EDIT menu and then paste special and under paste special select the TRANSPOSE option.
IF Statement
It gives one value if logical test evaluates to TRUE and another value if it evaluates to FALSE. For getting this go to the CELL where the results are required and write = IF(logical_test, value_if_true, value_if_false), where
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. Value_if_true is the value that is returned if logical_test is TRUE.
Value_if_false is the value that is returned if logical_test is FALSE.
Examples
In the following example, if the value referred to by the name File is equal to "Chart", logical_test is TRUE and the macro function NEW (2) is carried out, otherwise, logical_test is FALSE and NEW(1) is carried out:
IF (File="Chart",NEW(2),NEW(1))
Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.
You could write a macro to check whether you are over budget for a particular month, generating text for a message with the following formulas:
IF(B2>C2,"Over Budget","OK") equals "Over Budget" IF(B3>C3,"Over Budget","OK") equals "OK"
Suppose you want to assign letter grades to numbers referenced by the name Average. See the following table.
If Average is Then return
Greater than 89 A From 80 to 89 B From 70 to 79 C From 60 to 69 D Less than 60 F
Following statements can be used:
IF(Average>89,"A",IF(Average>79,"B", IF(Average>69,"C",IF(Average>59,"D","F"))))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.
Some other related functions with IF statement are
AND Returns TRUE if all its arguments are TRUE
FALSE Returns the logical value FALSE
NOT Reverses the logic of its argument
OR Returns TRUE if any argument is TRUE
