The Sheets tab

The Sheets tab in the Data Manager task present a Microsoft Excel-like spreadsheet where you can operate with scalar quantities without the need to repeat them in the whole column as in the Data tab.

Sheets tab gives you also access to various univariate and bivariate statistics through the Statistic Manager, allowing you to automatically populate sheets with scalar quantities inferred directly from your data. Those quantities can then be used directly here or in the Data tab to perform operation on columns. Even Sheets tab present a Formula toolbar to connect through formulas different cells of sheets.

The Sheets tab is organized in three different regions, from top to the bottom:

Executing a sheet formula

The Formula sheet toolbar allows you to execute functions or procedure on the various cells of your sheets. The formula is divided in two different terms:

  • Left term, controlling the cell where the result is going to be inserted

  • Right term, presenting the real formula.

Right term formula must follow the syntax rules of the underlying GOLD language. Main rules and operators about this language useful to write complex formula are presented here.

Warning

Formulas can not be executed on all the sheets. Sheets are divided into two groups: Locked and Unlocked. As explained in this section, Locked sheet are controlled only by the Statistic Manager and therefore if you are on one of this, the Left term of Formula sheet toolbar will become disabled. Formulas can be executed only on Unlocked sheets.

To control the Left term of your operation you can:

  • Click on a spreadsheet cell to automatically fill the left value. Right value will be filled with the latest executed formula, if any.

  • Type directly on the left term text field* using the Microsoft Excel-like form (A20).

Important

Differently from the Data tab, you can have multiple sheets. However, formula are always applied on the current selected sheet.

When the Right term of the formula toolbar is focused (this is highlighted by a colored border for the Formula text area and by the blinking cursor) some filling shortcuts are available:

  1. By clicking on a spreadsheet cell its corresponding formula value #"<cell-name>" will be inserted in the text area next to the blinking position cursor. Cursor is then moved accordingly. Using Ctrl and Shift during clicking operation will allows you to create more complicated list attribute expression as for example (#"<cell-name-1>", #"<cell-name-2>", ...) or (#"<cell-name-1>":$#"<cell-name-2>").

  2. Autocompletion is available during your formula typing: * Possible functions are suggested any time you start writing a new not quote enclosed value. * Possible attributes or results presented in the Data tab are suggested any time you start typing $.

To blur the Right term of the formula toolbar you need to click outside the whole Sheets tab for example on the Attribute list panel.

Note

During Right term focus, you can select with click shortcut even cell not present in the current sheet. If you change sheet with the Right term focused, original sheet will color with a light green, highlighting the fact it is remained the actual sheet. Then, if you click on any cell in a different sheet, the formula will be filled with a slightly more complicated formula value #"<sheet-name>!<cell-name>" which takes into account the name of the sheet either. To really blur the formula you need to come back on the original sheet or to execute the formula.

Variables can also be used in formulas. They are recognized by the @ prefix. For example, @Today could be a variable that contains the current date. For more information on variables see the variables page.

See also

When typing formulas, the complete formula with the parameters is displayed under the formula bar with a MONACO editor.

Formula toolbar supports also a whole list of operators inherited directly from the GOLD language which is underlying the whole platform: more information about this language, its syntax and its operators is present in this section. The use of operators in Formula toolbar and in Data Manager formulas is described more in detail here.

Important

When using functions in formulas, pay close attention to the following syntax:

  • Function names are case-sensitive.

  • When specifying the cell, the syntax is #"cellreference"

  • When specifying a string, the syntax is 'string'

  • When specifying continuous values and integers, just type them.

  • Strings can contain any characters, but must always enclose with the symbol ". For example, "sales".

  • Variable names can contain letters (capital and small), numbers and underscore only.

  • If you need to select more than one cell at a time, you can use either press Ctrl and select all the required cells: (#"cellA1", #"cellC5", #"cellD11")

  • If you need to select a range of cells, you can either press Shift and select the first and last cell in the range, or select them all with the cursor. The formula bar will include a colon to indicate the range: (#"cellA1":#"cellC5").

Examples of multiple selection syntax:. * #"A1" = sum(#"A1",#"B5", #"C11") * #"A1" = sum(#"A1":#"C5")

At the right end of the Formula Toolbar you can find the Approve button which launch the execution of the formula. Same result can be obtained by pressing Enter during Right term typing.

Prerequisites

  • You have added an Unlocked sheet.

  • You have selected it.

Procedure

  1. In the formula sheet bar, in the Left term, enter the cell you will be defining in one of the following ways: * writing directly the cell in Left term text field; * click on a cell in the spreadsheet.

  2. In the Right term, create the formula which will define the attribute by: * entering cells * entering constants

  3. Press enter or click on the Approve button.

The Sheets panel

The central panel of the whole Sheets tab is constitutes by the Sheets panel. In this panel you can add and remove sheets available for statistic evaluation or for formulas execution.

To add a Sheet you simply have to click on the Plus icon located on the left of the Sheets panel header bar. The new sheet you are going to add will appear at the bottom of the list. Default name for a sheet is s<position index>.

Sheets are classified into two groups:

  • Locked sheet: where stats evaluation can be carried on using the Statistic Manager.

  • Unlocked sheet: where cells can be manually edited and formulas can be applied.

Tip

The Sheet header background reminds the used the sheet classification: if colored it is a Locked sheet, otherwise it is Unlocked. Same information is conveyed by the Lock icon in the upper left corner of the sheet header tab.

You can Lock or Unlock an empty sheet by clicking on the Lock icon located in the upper left corner of the considered sheet header tab.

On the Sheet header tab, you can find two important icon buttons:

  • Delete: located in the upper right corner allows the deletion of the considered sheet.

  • Rename: located in the lower right corner allows the renaming of the considered sheet name.

Any sheet presents a spreadsheet visualization which is mainly divided into three parts:

  • Index column: where row indexes are displayed.

  • Header row: where column names are presented.

  • Main grid: containing the various cells.

By clicking on any of these three sectors you can highlight a subset of the whole sheet. Multiple selection can be combined with the usual Ctrl or Shift click procedure. The selection is often used in most of the operations we can trigger from the various context menu we can open from the Sheets panel.

By double-clicking on one of the column you will automatically resize its column according to the visualized content. Width resizing for columns and height resizing for rows can be granular changed by moving the right end of the header cell or the bottom end of the index cell with the mouse pointer.

Index column context menu

By right-clicking on any index row you can open the following context menu:

  • Add rows: it will add a provided number of rows before or after the selected rows. If you are selecting more than one continuous rows you can decide to insert a unique set of rows at the end of the whole block or before/after each row. In the second case more than one number of new rows needs to be provided. All these configuration options are decided in the dedicated dialog which is shown after this entry selection.

  • Delete rows: it will delete all the selected rows.

  • Go to row: it enables the user to automatically center a particular provided row in the main spreadsheet viewport.

Header column context menu

By right-clicking on any header column you can open the following context menu:

  • Add columns: it will add a provided number of columns before or after the selected columns. If you are selecting more than one continuous columns you can decide to insert a unique set of columns at the end of the whole block or before/after each column. In the second case more than one number of new columns needs to be provided. All these configuration options are decided in the dedicated dialog which is shown after this entry selection.

  • Delete columns: it will delete all the selected columns.

  • Go to row: it enables the user to automatically center a particular provided row in the main spreadsheet viewport.

Main grid context menu

  • Copy: it copies a unique cell value (the first of your selection) for subsequent paste operation in another cell.

  • Paste: it pastes the copied value in all the selected cells.

  • Copy to clipboard: it copies the text of the selected cells into your computer clipboard to be available for pasting operation even in programs different by Rulex Platform.

  • Go to row: it enables the user to automatically center a particular provided row in the main spreadsheet viewport.

  • Set value: it allows the user to insert a unique value which is going to be written in all the selected cells (only on Unlocked sheet).

Note

In Paste formula operation, cell references expressed by # are accordingly shifted by the difference in position between the original copied cell and the final pasted cell. To fix a particular cell term you can use the equivalent GOLD operator ##. Indeed, the unique difference between # and ## is its behavior under copy and paste operation.

Editing a cell

Warning

Editing operation is only available on Unlocked sheets.

To start the manual editing of a cell you can:

  • Double-clicking on it.

  • Selecting it and start typing.

Note

Even if both these operations will start the editing operation, there is a substantial difference between the two behaviors:

  • when you double-click on a cell the value already present will be maintained and the cursor will appear blinking at the end of the old text.

  • when you start typing after a cell selection any value already present in the cell will be erased at the beginning of the new typing operation.

Once you have finished your manual editing you can use Arrows or the Enter button to move to a continuous cell (Enter is equal to the Down arrow) and continuing your editing operation. Real value modifications will be triggered only when no input editing will be performed on one of the cell or blur has been triggered by clicking on a different cell or location outside the scope.

The Statistic Manager

In the Statistic Manager you can evaluate univariate or bivariate statistics on your data and automatically populate your sheets with the results.

Warning

Results can be inserted only on Locked sheets. If no Locked sheet are present the Statistic Manager will be disabled.

Inputs for your statistic evaluation are provided by dragging and dropping attributes and results from the Attribute list into four different areas of the Statistic Manager:

  • Var_1: controlling the main input columns for the univariate evaluation and for the statistic tests, as well as the first input columns for the bivariate evaluation and for correlation study.

  • Var_2/Target: controlling the target attributes for the statistic tests, as well as the second input columns for the bivariate evaluation and for correlation study.

  • Statistics: controlling the type of statistics to evaluate as well as the precise list of single statistics to include in the result representation.

  • Sheet: controlling in which sheet results are displayed. The choice is reduced only to the Locked sheet list.

When attributes are dropped into the Statistic Manager they are organized in rows: each row can contain an arbitrary number of attributes in each area; it will construct however a unique table onto the final selected sheet.

Note

Any row is associated with a unique sheet. They can represent multiple statistics evaluated for multiple input attribute, they still will draw a unique table. If you want to divide your evaluated stats into multiple Locked sheet, you are forced to divide your input attributes into two separate rows of the Statistic Manager.

On the right end of each filled row of the Statistic Manager panel, a Cross icon enables you to erase the whole row in a unique operation.

A complete description about stats supported and on the internal interaction of the various four areas is present in this dedicated page.

See also

For a whole list of supported statistics and their relative type see this page.