Spreadsheet Options – Conversions

Select menu: Tools | Spreadsheet Options (Conversions Tab)

This dialog lets you set options affecting the conversion of columns between types (Variate,Factor, Text) and the adding of data to columns.

From the menu select Tools | Spreadsheet Options then click the Conversions tab.

Sort factor levels on convert

When converting text or variate columns into factors, specifies whether to automatically sort the levels or labels that are generated. This setting can be temporarily overridden when using Column Convert if you not wish to use the default action on a particular column.

Suggest filling in missing factor values

Controls whether a prompt will appear suggesting that the Fill down operation is carried out on factors with a reasonable proportion of their values missing.

Allow new factor levels in edit

Controls whether new factor levels can be added by entering them into a cell via the keyboard. If this option is enabled, entering any levels or labels not found in the existing factor will cause a prompt to appear querying whether a new level is added to the factor, or the entered value is changed to the closest matching level/label.

Suggest converting columns with <= N unique items

Controls the default status of prompting to convert columns to factors on reading foreign spreadsheet files (XLS/WQ1/WK1/WB?/QPW) and clipboard data. If columns have repeated values, and less than the specified number number of unique items, a prompt will appear suggesting that they could be converted.

Tolerance on assigning values to nearest level

When a numerical value is entered into cell in a factor column, if the number does not exactly match an existing factor level, it will be assigned to the closest existing level, provided the difference between the entered number and that level is less than the provided tolerance (which must be positive). A value of 0 (the new default) will always raise an error on any mismatch.

Tolerance on creating levels from variate

A positive value in this field will be used as a tolerance in creating levels from a factor. If two values differ by less than this tolerance, then they will be assigned to the same factor group. The default value of 0 places all unique values into their own group. If the variate being converted to a factor contains calculated values, then these values may differ in their final few significant figures. For example, the calculated values 1/3 and 200/600 may be assigned to different groups depending on how the final figure rounds. A small value such as 1e-12 could then be used to allocate these two distinct values to the same group.

Use standard factor orders

When selected, if all labels in a factor match either the compass points, weekday or month names, or the user specified order, then these labels will be sorted into the order of the matching list.

User defined label order

This opens the User defined Label/Level Order within a Factor dialog which allows a list of labels to be entered. If the labels in a factor match the user defined labels, then they will be sorted into the order in this list. Multiple orders may be entered by providing these in sections. For example a colour order and a species order can be entered by providing the list of labels: Black, Blue, Green, Red, Yellow, White, followed by Clover, Lotus, Ryegrass, Cocksfoot.

Remove unused factor levels on update

If selected, when updating Genstat, any factors having levels (or labels) that are not actually present in the data will be redefined without those levels.

Ignore case

When selected, case of individual characters in text columns will be ignored during certain operations, such as sort, search, and conversion to factors.

Ignore blanks

When selected, blank characters in text columns will be ignored during certain operations, such as sort and conversion to factors.

Check for date values

When selected, Genstat checks all new text columns to see if they contain data in date format. If columns appear to contain data in date format you are prompted to convert these to dates. The Convert Text to Date dialog will prompt for each column that looks like a date. This option controls the default setting for a number of dialogs that allow this check (New Spreadsheet from Clipboard, Append Multiple Excel files, and Merge Multiple files).

Text to number conversions

This controls how labels are interpreted as numbers when a text is forced to be read as a number:

Strict – only labels that contain numeric data only are converted
(e.g. ’10’ -> 10, ‘1O’ -> *.)
Single – a single character substitution is read as a number
(o,O -> 0, i,I,l,L -> 1, s,S-> 2, z,Z -> 5, comma -> decimal point)
(e.g. ‘1O’ -> 10, ‘Io’ -> *).
Common – multiple substitutions as in single are made
(e.g. ‘Io’ -> 10, ’23X’ -> *).
Standard – as in common but extra text is ignored at the end
of the number (e.g. ’23X’ -> 23, ‘A2X3’ -> *).
Lax – any digits are read from the text (e.g. ‘A2X3’ -> 23).

Maximum text length

The maximum text length in Genstat is a user defined limit between 90 and 10000. You can select the specified limit from the dropdown list or type it directly into the field. Texts that are read from any data source which are longer than the specified limit will be truncated.

Auto extend on pasting cells

If selected, when pasting data from the Clipboard, new rows or columns will be added to the sheet as required. Otherwise, Clipboard data will be truncated at the current row and column extents.

Auto delete on cut

When data are cut from a spreadsheet they are replaced by missing values, by default. If this item is enabled, entire rows or columns that are cut will also be deleted from the spreadsheet.

When converting a variate created by a calculation to a factor

When a variate created from a calculation is converted to a factor, re-calculating the column may in future generate some invalid factor levels. For example, if the column C1 contains the values 1,2,3 and 4 and the column C2 is calculated as C2 = 2*C1, this will produce the values 2,4,6 and 8. After converting C2 to a factor, if a value is changed in C1 to 5 this will create a recalculated value of 10 which is not a valid factor level (2,4,6 or 8) of C2. This item controls what happens when converting the variate to a factor:

Always remove calculation	The column’s calculation will always be removed.
Always keep calculation	The column’s calculation will always be kept.
Prompt to remove or keep	A dialog will ask whether to remove or keep the column’s calculation.

Unicode in Excel XLSX files

When reading an Excel XLSX file, this option controls what happens to Unicode characters that are not supported by the extended ASCII character set. Some Unicode characters such as Greek or mathematical symbols can be displayed using the Genstat typesetting commands used by the PRINT directive. However, many foreign language Unicode characters cannot be displayed by Genstat and these will be removed with a warning. The options below control how the supported Unicode characters are handled:

Convert Unicode to Genstat typesetting	The Unicode characters are converted to equivalent typesetting strings, e.g. α is converted to ~{alpha} and √ to ~{sqrt}.
Convert Unicode to ASCII characters	The Unicode characters are converted to the nearest ASCII characters, e.g. α is converted to a and √ to sqrt.
Remove all Unicode characters	All Unicode characters are removed.

The conversion of Greek letters to ASCII characters is given in the table below:

Defaults

Resets the spreadsheet options to their built-in default values.