1. Home
  2. Expanding a Spreadsheet Using a Weight Column

Expanding a Spreadsheet Using a Weight Column

Most Genstat analyses have an option to add weights for an analysis. For those Genstat analyses that don’t have a weights option you can still do a weighted analysis by expanding the spreadsheet to duplicate each row the number of times given by the weights. You can then use the expanded spreadsheet to perform the equivalent of the weighted analysis.

In the following spreadsheet image a number of ryegrass tillers was sampled in each plot and the number of weevil larvae in these tillers was counted. The column Larvae tiller contains the mean number of larvae per tiller.

If we want to use the number of tillers counted as weights in an analysis that doesn’t allow for weights, we can expand the spreadsheet using the Tillers column as weights. Using this data would be equivalent to specifying the Tillers column as weights.

  1. Select Spread | Manipulate | Expand.


  2. Select the column that contains the weights to be used in the expansion.
  3. Leave options at their defaults or set them as required then click OK.

If values within the weight column are not integers the values will be rounded to the nearest integer. If a weight value is less than zero or a missing value then no copies of that row will appear in the new spreadsheet. If a weight value is zero, then rows will only be copied if the Keep rows with zero weight option is selected.

The CALCULATE function NEXPAND can be used to expand a data column in command mode. However, this function can only be used with numerical columns, and factors are converted to variates.

The menu will produce a warning if the weights seem excessively large. The new spreadsheet will not contain the original weights column.


Use weights from column Specifies which spreadsheet column contains the weights to be used in the expansion. Each row is repeated the number of times specified by the values in this column. If the column is a factor, then the levels values of the factor will be used, and if these are not defined, then the ordinal values will be used.
Keep rows with zero weight When selected, a row with a weight of zero will be copied once into the new spreadsheet, otherwise no copies will be made. Rows with negative or missing weights will not be copied to the new spreadsheet
Create unique column names Column names in the new spreadsheet will be made unique by appending an underscore and an incremented number to them e.g. _1, _2, etc.
Set as active sheet This sets the new spreadsheet that is created as the active spreadsheet. Setting an active spreadsheet provides a method of avoiding multiple data updates to the server when more than one spreadsheet is open within Genstat. When a spreadsheet is set as the active spreadsheet only data from that spreadsheet is automatically updated to the Genstat server. Another advantage of specifying an active spreadsheet is that the Spread menu options will always be enabled even if your spreadsheet does not have the cursor focus.
Add to book Any open spreadsheets will be displayed in this dropdown list. Select a spreadsheet to insert your data into or leave it at the default setting New book to create a new workbook.

Updated on March 1, 2019

Was this article helpful?