Spreadsheet Calculate Menu

These menu items perform calculations on columns in the spreadsheet and retrieve the results as a new column in the spreadsheet.

Fill

Fill columns with data generated in cyclic patterns (for example when creating block and treatment factors). data generated in cyclic patterns

List Fill

Fill columns with data specified in a list (for example when creating block and treatment factors).

Fill by Groups

Fill columns with a separate numerical sequence, group by group.

Date Fill

Fill columns with date or time sequence.

Column

Calculate a new column using formula in the Calculations dialog. (Same as Column Calculate in the 3rd Edition).

Factor

This opens the Form Groups dialog, but any factors formed here will be inserted as a column into the spreadsheet. If a column of the same name already exists, this will be replaced with the new factor created. (Same as Factor Calculate – duplicated menu item for ease of finding all items which create new structures under one menu).

Recalculate

All columns in a spreadsheet which have been generated by a Spread Calculation menu item (Spread | Calculate | Column/Factor/Random Sample) have the calculation which created them stored in the column definition. This menu item can be used to edit and rerun the calculation which created the column, and retrieve the new results back into the spreadsheet. Click here for more details.

Standardize

This standardizes the current variate column, or selected variate columns so that the column values have a mean of 0 and variance of 1.

Random Sample

This opens the Generate Random Sample window, but any variates formed in this window will be inserted as a column into the spreadsheet. If a column of the same name already exists, this will be replaced with the new variate created.

Random Subset

This opens the Generate a Random Subset from a Spreadsheet dialog, which will create a new spreadsheet from a random subset of rows from the current spreadsheet. A weighted sample can be drawn, with or without replacement.

Summary Stats

This opens Summarize Spreadsheet dialog that lets you create a new vector spreadsheet containing the selected summaries (mean, counts, totals etc.) of the current spreadsheet columns by factor groups.

Cell Selection Stats

Summary statistics for the selected cells will be calculated. These include the arrangement of cells (selection types, and number of rows by columns), the number of numerical observations, missing values and text cells, and for the numerical cells, the total, mean, minimum, maximum, variance, standard deviation, standard error of the mean and the % coefficient of variation.

Row Summaries

This opens the Calculate Row Summary dialog which allows a summary over the specified columns to be calculated for each row in the spreadsheet.

Table Margins

For a table spreadsheet, this opens the Create Table Margins dialog which allows margins (either totals, means, minima, maxima, medians or variances) to be calculated and added to the table.

Text Split

This opens the Split a Text Column dialog which allows a text or factor column to be split into one or more columns.

Combine Text

This opens the Combine/Concatenate Text Columns dialog which allows text from multiple columns to be concatenated into a single columns.

Recode

This opens the Recode a Column dialog which displays a list of the unique items in the column. For each item, you can specify a new value to be taken in the resulting column. This can be used to modify a group of values in a column (e.g. to set all missing values to 0, or change text values to numerical values – (‘Bad’, ‘Good’, ‘Average’) -> (0,1,2). If the column name specified for the results is the same as the input column, then the input column will be replaced with the recoded column.

Code to Groups

This opens the Code to Groups dialog which can be used to form factors by splitting numerical columns into groups based on specified limits or break points.

Rebase Dates

Lets you change the base date for a date column. There are two base dates which you can switch between:

1/3/1600	The nominal starting day for the Gregorian calendar.
1/1/1900	The starting day used by spreadsheets such as Excel, Lotus and Quattro.