In Excel, you can put the comments in the column headers to create and transfer additional information to Genstat. Excel comments can be used to specify the following information:
- factor labels or levels and their order
- the column type by prefixing the column name with a symbol (!,#,$, etc.)
- the column description (used to provide additional information about the data in a column)
- To create a factor label or level, create a comment within Excel (right-click and select Insert Comment).
- The factor labels or levels must start with a ! on a new line in the comment.
- Enter factor levels as a comma separated list preceded by an exclamation mark ! with the values enclosed in brackets. For example !(100,90,50,10).
- Enter factor labels as a comma separated list preceded by the characters !T or !t with the characters enclosed in brackets. For example !t(A,B,C).
The following image shows two comments entered into Excel (to do this right-click and select Insert Comment, making sure you don’t put line breaks in the middle of the list).
The column type (! for factor, # for variate or $ for text) and display formats (:D etc…) can be inserted at the start of the comment.
If any of the levels in the list is not a valid number, the list will be interpreted as labels replacing the ! with !T when importing the data.
If any of the labels in the list contain one or more of the characters ‘!’, ‘,’ or ‘)’ (exclamation mark, comma or right bracket), then the label must be contained in single quotes. If a label contains a single quote this must be duplicated; for example, the string (Pierre David’s) should be entered as ‘(Pierre David”s)’. If labels do not include any of the reserved characters then you do not need to use single quotes. For example, the labels A, B, C and D-E could be supplied in the comment as !T(A,B,C,'(D-E)’).
A column description lets you provide additional information about the contents of a column. For example, the comments shown below contain the descriptions ‘Chemical Applied‘ and ‘% of normal rate‘.
To enter a column description, type a string of text. The string cannot start with any of the reserved characters listed above, e.g. ‘!’, ‘$’, ‘#’ or ‘:’.
You can combine the factor label/level specification and column description into a single comment. These must be specified in the following order separated by a new line (in Excel use Shift+Enter to create a new line):
- The factor labels or levels
- The column type or display symbols(!,#,$, etc.)
- The column description
For example, the following comment could be used to supply factor labels, but with the levels in date format with a description:
!T(‘Spring’,’Summer’,’Autumn’,’Winter’)
:D Seasonal Summary
If the values for a factor are stored using the ordinal values (1,2,3…) within Excel, then you could use comments to specify the levels or labels to be used within Genstat. For example, a column called Gender in Excel may have the data stored as the values 1 and 2. By using a comment, you can enter labels as !T(Male,Female) to represent the values within in Genstat.
If the data for a factor within Excel has the actual levels or labels specified then the cell comment can be used to specify the order of these to be used in Genstat. For example, if a column contained a factor with labels AR1, AR37, Nil and Wild, then by default these would be read into Genstat in alphabetical order. Using the cell comment these could be given an alternative order such !T(Nil,Wild,AR1,AR37). Similarly if a column contained a factor with levels 0.5,1,2,10 then a reordering such as !(10,2,1,0.5) could be used to specify the levels should be stored in decreasing order.