1. Home
  2. AUSPREADSHEET procedure


Saves results from an analysis of an unbalanced design (by AUNBALANCED) in a spreadsheet (R.W. Payne).


MEANS = pointer Pointer to tables to contain the treatment means; default means
SEMEANS = pointer Pointer to tables to contain the standard errors of treatment means; default sem
SEDMEANS = pointer Pointer to matrices to contain standard errors of differences of treatment means; default sed
ESEMEANS = pointer Pointer to matrices to contain effective standard errors of treatment means; default ese
EFFECTS = pointer Pointer to contain the estimated effects, their standard errors, t-statistics and probabilities; default effects
REPLICATIONS = pointer Pointer to tables of treatment replications; default replication
RESIDUALS = variate Variate to save the residuals in the fittedvalues page; default residuals
FITTEDVALUES = variate Variate to save the fitted values in the fittedvalues page; default fittedvalues
COMBINATIONS = string token Factor combinations for which to form predicted means (present, estimable); default esti
ADJUSTMENT = string token Type of adjustment to be made when predicting means (marginal, equal, observed); default marg
AOVTABLE = pointer Pointer to a text and variates containing the information in the analysis-of-variance table; default aovtable
RMETHOD = string token Type of residuals to form (simple, standardized); default simp
LSDMEANS = pointer Pointer to matrices to contain least significant differences for means
LSDLEVEL = scalar Significance level (as a percentage) for the least significant differences; default 5
SPREADSHEET = string tokens What to include in the spreadsheet (aovtable, effects, means, semeans, sedmeans, esemeans, lsdmeans, replications, fittedvalues); default aovt, mean, sedm, repl, fitt
OUTFILENAME = texts Name of Genstat workbook file (.gwb) or Excel (.xls or .xlsx) file to create
SAVE = identifier Save structure (from AUNBALANCED) containing details of the analysis for which further output is required; if omitted, output is from the most recent use of AUNBALANCED

No parameters


AUSPREADSHEET puts results from an analysis, by AUNBALANCED, of an unbalanced design into a spreadsheet. By default the results are from the most recent analysis by AUNBALANCED, but you use the SAVE option to specify the save structure from some other analysis.

The SPREADSHEET option specifies which pages of the spreadsheet to form, with settings:

    aovtable analysis of variance table,
    effects estimates of effects, with their standard errors, t-statistics and probabilities,
    means tables of treatment means,
    semeans tables of standard errors of treatment means,
    sedmeans matrices of standard errors of differences of treatment means,
    esemeans tables of effective standard errors of treatment means,
lsdmeans matrices of least significant differences of treatment means,
    replications replication tables of treatment terms,
    fittedvalues y-variate, fitted values and residuals.

By default, SPREADSHEET = aovt, mean, sedm, repl, fitt.

To help avoid clashes between the columns of the spreadsheets if you want to save results from more than one analysis, the parameters MEANS, SEMEANS, SEDMEANS, ESEMEANS, LSDMEANSEFFECTS, REPLICATIONS, RESIDUALS, FITTEDVALUES and AOVTABLE allow you to specify identifiers for the columns (or sets of columns) that will store the corresponding results in the current spreadsheet.

Tables of means are obtained from the AUKEEP procedure which uses the PREDICT directive. The first step (A) of the calculation forms the full table of predictions, classified by every factor in the model. The second step (B) averages the full table over the factors that do not occur in the table of means. The COMBINATIONS option specifies which cells of the full table are to be formed in Step A. The default setting, estimable, fills in all the cells other than those that involve parameters that cannot be estimated, for example because of aliasing. Alternatively, setting COMBINATIONS=present excludes the cells for factor combinations that do not occur in the data. The ADJUSTMENT option then defines how the averaging is done in Step B. The default setting, marginal, forms a table of marginal weights for each factor, containing the proportion of observations with each of its levels; the full table of weights is then formed from the product of the marginal tables. The setting equal weights all the combinations equally. Finally, the setting observed uses the WEIGHTS option of PREDICT to weight each factor combination according to its own individual replication in the data.

The LSDLEVEL option specifies the significance level (as a percentage) for the least significant differences; default 5.

You can save the data in either a Genstat workbook (.gwb) or an Excel spreadsheet (.xls or .xlsx), by setting the OUTFILENAME option to the name of the file to create. If the name is specified without a suffix, '.gwb' is added (so that a Genstat workbook is saved). If OUTFILENAME is not specified, the data are put into a spreadsheet opened inside Genstat.


Parameters: none.

Action with RESTRICT

If the Y variate is restricted, that restriction will carry over into the fitted-values spreadsheet.

See also

Directive: SPLOAD.


Commands for: Regression analysis.


        'Data from Genstat 5 Release 1 Reference Manual, page 340.';\
FACTOR  [NVALUES=36; LEVELS=3; VALUES=12(1...3)] Block
FACTOR  [NVALUES=36; LABELS=!t(baresoil,emerald,emergo)] Leachate
&       [LABELS=!t('1','1/4','1/16','1/64')] Dilution
VARIATE [NVALUES=36] Nhatch,Nnohatch
READ    Leachate,Dilution,Nhatch,Nnohatch
  1           2         109         318
  3           4          54         350
  3           1           *         415
  2           2         783         212
  3           3         652        1375
  2           4         490         816
  1           3          95        1219
  2           1        1012          66
  1           4         166         943
  3           2        1059         313
  1           1         257        1006
  2           3        1058         234
  2           4         507        1119
  1           2         194         840
  1           3         175        1707
  1           1         326         609
  3           4         142         980
  2           3         286         230
  3           2         546         313
  2           2           *         301
  2           1        2471         112
  3           3          76         489
  1           4         208         503
  3           1           *         325
  1           1         322         913
  1           2         255        2246
  3           2        1774        1446
  2           2         999         193
  2           4         388        1836
  3           4         221        1800
  1           3         220        1902
  2           1        2821         187
  3           1        1486         463
  3           3         717        1473
  1           4         143         941
  2           3         968         550 :
CALCULATE          Logit%h = LOG(Nhatch/Nnohatch)
AUNBALANCED        [PRINT=aovtable,effects,means,residuals; FPROBABILITY=yes;\
                   PSE=differences,alldifferences,means,ese] Logit%h
AUSPREADSHEET      [SPREADSHEET=aovtable,effects,fittedvalues,\
Updated on January 12, 2022

Was this article helpful?