Analyzing Data from Excel in Statistica

Statistica offers high power analytic tools not found in Excel. With these advanced analytic tools also comes more structural requirements for the data. This example shows how a typical Excel data sheet can be rearranged to create a Statistica Spreadsheet ready for analysis.  

Statistica analysis tools are designed to accept data that follow a specific structure. Specifically, each row or case should represent one individual experimental unit. If the data measure the blood pressure of patients, each patient will have one row. The variables will provide the information about that individual patient.

This example uses an experimental data file from Excel's Help files. The data show growth measurements of plants from two levels of group and two levels of trial. The data have a total of 16 observations. The following image shows how the data are arranged in Excel. To perform ANOVA analysis on this data in Statistica, it must first be rearranged to meet Statistica data structure standards.

After importing the data into Statistica, some edits are necessary. First, each row must indicate the trial from which it came. Use the fill/copy down option to achieve this. The spreadsheet below shows the data after filling in trial information for all rows.  

Next, the data must be rearranged.

Ribbon bar. On the Data tab in the Transformations group, click Stack to display the Unstacking/Stacking dialog box.

Classic menus. From the Data menu, select Unstacking/Stacking to display the Unstacking/Stacking dialog box.

Select the Stacking tab. Select variables Group 1 and Group 2. Rename the Destination variable name to Measurement and the Code variable name to Group.

Click OK and a new spreadsheet is created that holds the data in the format expected by Statistica. Each experimental unit has its own row. Each factor has its own column. Double-click on the Var1 header, and change the name to Trial.

The data are now ready for analysis in Statistica. We will conduct an ANOVA analysis.

Ribbon bar. On the Statistics tab in the Base group, click ANOVA to display the General ANOVA/MANOVA dialog box.

Classic menus. From the Statistics menu, select ANOVA to display the General ANOVA/MANOVA dialog box.

Ensure that Factorial ANOVA is selected in the Type of analysis box, and click OK. In the ANOVA/MANOVA Factorial ANOVA dialog box, click the Variables button. In the variable selection dialog box, select Measurement as the Dependent variable and Trial and Group as Categorical factors. Click OK in the variable selection dialog box.

Click OK in the ANOVA/MANOVA Factorial ANOVA dialog box to advance to the ANOVA Results dialog box. On the Summary tab, click the All effects button to create the ANOVA table.

The ANOVA output shows significance tests for Trial, Group, and Trial by Group interaction effects.