1. Home
  2. ADSPREADSHEET procedure

ADSPREADSHEET procedure

Puts the data and plan of an experimental design into a spreadsheet (R.W. Payne).

Options

DATA = factors or variates Data variables (e.g. design factors and covariates) to put into the data spreadsheet; default takes the factors defined by previous BLOCKSTRUCTURE and TREATMENTSTRUCTURE directives
NEWDATA = variates New variates (e.g. measurements to be taken during the experiment) to create and put into the data spreadsheet; default * i.e. none
Y = variate or factor Specifies the y-coordinates of the plots for the plan spreadsheet
X = variate or factor Specifies the x-coordinates of the plots for the plan spreadsheet
CONSTANTFACTORS = string tokens Whether to put factors whose levels are constant in the y or x direction in a separate row or column of the Plan spreadsheet (y, x); default * i.e. neither
SEPARATOR = text Separator for factor values in the plan spreadsheet; default '; '
OMITGAPS = string token Whether to omit gaps when the plots in the plan are equally spaced (yes, no); default no
FOREGROUND = scalar, variate or text Foreground colours to use for the plots in the experiment; default 'Black'
BACKGROUND = scalar, variate or text Background colours to use for the plots in the experiment; default 'BlanchedAlmond'
CFACTORS = factors Factors to determine the colour to use for each plot; default uses the first block factor or no colouring otherwise
GAPFOREGROUND = text or scalar Foreground colour for gaps and surrounding plots; default 'Black'
GAPBACKGROUND = text or scalar Background colour for gaps and surrounding plots; default 'LightGreen'
YFOREGROUND = text or scalar Foreground colour for factors constant in y-direction; default 'Black'
YBACKGROUND = text or scalar Background colour for factors constant in y-direction; default 'PaleTurquoise'
XFOREGROUND = text or scalar Foreground colour for factors constant in x-direction; default 'Black'
XBACKGROUND = text or scalar Background colour for factors constant in x-direction; default 'LightCyan’
SPREADSHEET = string tokens Which spreadsheets to form (data, plan); default data
OUTFILENAME = texts Name of Genstat workbook file (.gwb) or Excel (.xls or .xlsx) file to create

Parameters

FACTOR = factors Factors to include in the plan spreadsheet; if unset, includes the factors defined by a previous TREATMENTSTRUCTURE directive
LABELS = texts Labels to be used for each factor if its own levels or labels are inappropriate

Description

ADSPREADSHEET puts information about an experimental design into a spreadsheet. By default the spreadsheet is opened within Genstat itself, but you can save it to an external file by supplying its name using the OUTFILENAME option. The file can be a Genstat workbook (.gwb) or an Excel spreadsheet (.xls or .xlsx). If the name is specified without a suffix, '.gwb' is added (so that a Genstat workbook is saved).

The SPREADSHEET option specifies which sheets to form, with settings:

    data contains data variables i.e. design factors, covariates and measurements, and
    plan constructs a plan of the design.

By default, SPREADSHEET=data. If both sheets are formed, they are put together, as pages of a Genstat workbook.

The contents of the data spreadsheet are specified by the DATA and NEWDATA options. The DATA option lists existing data variables (i.e. design factors and covariates) to put into the data spreadsheet. If this is unset, the default is to take the factors defined by previous BLOCKSTRUCTURE and TREATMENTSTRUCTURE directives; ADSPREADSHEET gives a failure

diagnostic if the DATA option is unset and there has been no previous BLOCKSTRUCTURE or TREATMENTSTRUCTURE. The NEWDATA option allows you to include new spreadsheet columns to provide blank cells for new variates like measurements that are to be taken during the experiment. For security all the existing variables are protected so that they are read-only.

The locations of the plots in the plan spreadsheet are specified by variates or factors supplied by the X and Y parameters; these define the row and column of the plots in the sheet, respectively (with row coordinates increasing from top to bottom, and column coordinates increasing from left to right in the usual way). The plots need not be equally spaced. However, ADSPREADSHEET looks to see whether the coordinates in either direction are taken from a regular grid, possibly with some gaps: for example coordinates (1, 2, 4, 6) are on a grid with spacing 1 and gaps at 3 and 5. If so, ADSPREADSHEET will include rows or columns for all the coordinates, including the gaps (i.e, 1, 2, 3, 4, 5 and 6 for the example), unless you set option OMITGAPS=yes. The x-coordinates are shown in a units column of the spreadsheet, and the y-coordinates are given in a row at the bottom of the plan. If either Y or X is not specified, ADSPREADSHEET will generate values automatically according to the factors in the design – factors from a previous BLOCKSTRUCTURE directive, if available, otherwise from a previous TREATMENTSTRUCTURE directive.

The factors to include in the plan can be specified using the FACTOR parameter. If this is omitted, ADSPREADSHEET takes the factors from a previous TREATMENTSTRUCTURE directive (and fails if there has been none). The values of each factor are represented by its labels, if available, or otherwise its levels. The LABELS parameter allows alternative labels to be specified for each factor, if the existing levels or labels are too unsuitable. The values of the factors in each plot are listed in the equivalent cell of the spreadsheet. By default, they are separated from each other by a semi-colon and a space, but you can supply alternative separating characters using the SEPARATOR option. You can set option CONSTANTFACTORS to x to list the values of factors whose values are constant in the x direction separately, in a column on the left-hand side of the sheet. Similarly, the setting y causes factors whose values are constant in the y direction to be listed in a row at the top of the sheet.

The colouring of the cells in a Genstat can be controlled using the FOREGROUND, BACKGROUND, CFACTORS, GAPFOREGROUND, GAPBACKGROUND, YFOREGROUND, YBACKGROUND, XFOREGROUND and XBACKGROUND options. The colours can be specified as numbers defining RGB values, or texts containing names of the standard Genstat colours; see the PEN diective for details. The FOREGROUND and BACKGROUND options control the colours of the text and background, respectively, of the spreadsheet cells that correspond to plots in the experiment. You can give the plots different colours by supplying several values (in texts or variates). ADSPREADSHEET then uses a different colour for each combination of levels of the factor or factors specified by the CFACTORS option. If several colours are defined, but CFACTORS is not set, the first factor in the block factor (in BLOCKSTRUCTURE) is used. If there are no block factors, the first defined colour is used for all the plots. The GAPFOREGROUND and GAPBACKGROUND options define the colour to use for the cells representing gaps in the experiment or surrounding it. The YFOREGROUND and YBACKGROUND options specify the colour for the text and background in the cells containing the names and levels of the factors constant in the y-direction. The XFOREGROUND and XBACKGROUND options similarly specify the colour for the text and background for the factors constant in the x-direction.

Options: DATA, NEWDATA, Y, X, CONSTANTFACTORS, SEPARATOR, OMITGAPS, FOREGROUND, BACKGROUND, CFACTORS, GAPFOREGROUND, GAPBACKGROUND, YFOREGROUND, YBACKGROUND, XFOREGROUND, XBACKGROUND, SPREADSHEET, OUTFILENAME.

Parameters: FACTOR, LABELS.

Action with RESTRICT

If X or Y or any of the factors in the plan is restricted, only the unrestricted plots will be included in the plan spreadsheet.

See also

Directive: SPLOAD.

Procedures: ASPREADSHEET, AUSPREADSHEET, DDESIGN, PDESIGN, FSPREADSHEET, VSPREADSHEET.

Commands for: Design of experiments.

Example

CAPTION       'ADSPREADSHEET example',
              !t('1) Randomized block design in 4 blocks each of 8 plots',\
              'all on a regular grid.'); STYLE=meta,plain
FACTOR        [LEVELS=4] Blocks; VALUES=!(8(1...4))
&             [LEVELS=8] Plots; VALUES=!((1...8)4)
&             [LEVELS=8] Treat; VALUES=!(1...8,1...4,8...5,5...8,4...1,8...1)
VARIATE       X,Y; VALUES=!((1...4)8),!(4(1...8))
ADSPREADSHEET [Y=Y; X=X; SPREADSHEET=plan] Blocks,Plots,Treat;\
              LABELS=*,*,!t(a,b,c,d,e,f,g,h)
CAPTION       'Plots on an irregular grid, and with a gap in one block.'
VARIATE       X,Y; VALUES=!((1,2)4, (3,4,6,7)2, (1...4)2, 6,6,(4,5,6)2),\
                          !(2(1...4), 4(2,3), 4(6,7), 9,10,3(11,12))
ADSPREADSHEET [Y=Y; X=X; SPREADSHEET=plan] Blocks,Plots,Treat;\
              LABELS=*,*,!t(a,b,c,d,e,f,g,h)

CAPTION       !t('2) Split plot design (Yates,F: The Design and Analysis',\
              'of Factorial Experiments, Commonwealth Bureau of Soils,',
              'Tech. Comm. 35 p.74)')\
FACTOR        [NVALUES=72; LEVELS=6] Block
&             [LEVELS=3] Wplot
&             [LEVELS=4] Subplot
GENERATE      Block,Wplot,Subplot
FACTOR        [NVALUES=72; LABELS=!T('0 cwt','0.2 cwt','0.4 cwt','0.6 cwt')]\
              Nitrogen
&             [LABELS=!T(Victory,'Golden rain',Marvellous)] Variety
READ          [SERIAL=yes] Nitrogen,Variety
 4 3 2 1 1 2 4 3 1 2 3 4 3 1 2 4 4 1 2 3 2 1 3 4
 2 3 4 1 4 2 3 1 1 4 2 3 3 4 1 2 1 3 4 2 2 3 4 1
 4 1 3 2 3 4 1 2 3 4 2 1 3 1 4 2 4 3 1 2 1 2 3 4 :
 3 3 3 3 1 1 1 1 2 2 2 2 3 3 3 3 1 1 1 1 2 2 2 2
 2 2 2 2 3 3 3 3 1 1 1 1 3 3 3 3 2 2 2 2 1 1 1 1
 2 2 2 2 1 1 1 1 3 3 3 3 1 1 1 1 2 2 2 2 3 3 3 3 :
BLOCKSTRUCTURE Block/Wplot/Subplot
TREATMENTSTRUCTURE Nitrogen*Variety
ADSPREADSHEET [SPREADSHEET=data,plan]
CALCULATE     x = Subplot
&             y = Block * NLEVELS(Wplot) + Wplot
ADSPREADSHEET [Y=y; X=x; CONSTANTFACTORS=y; SPREADSHEET=data,plan]
Updated on June 20, 2019

Was this article helpful?