Calculation Grid brings Excel-like functionality into Displayr. It is designed to work like a standard built in table, which allows you to auto-size with its contents, apply table styles, be referenced in R with square brackets like tables can be, etc. Individual cells can be edited which allow you to edit your code (either by double clicking, hitting F2 or using the code editor in the object inspector). You can also add columns and rows with the inserter, drag to reorder them, and copy and paste cells into the same or different grid or into an Excel spreadsheet.
This is an example of a Calculation Grid:
- Calculation Grid User Interface
- Navigating Calculation Grid
- Editing Cells
- Programming in R
- Manipulating Rows & Columns
- Modifying the Calculation Grid Properties
Calculation Grid User Interface
To create a grid on a page:
-
In the toolbar, select
- Calculation > Calculation Grid or
-
Table > Calculation Grid or
-
Anything > Calculation > Calculation Grid
The mouse pointer will change to a cross,and from that point, you can create your grid.
- Click on the page, and an empty 2 x 2 grid is created (including Row and Column headings)
- For a larger grid, click on the page and drag the pointer down and to the right until you get the desired number of rows and columns. You will get visual feedback showing the number of rows or columns as you drag the mouse, as well as a counter near your cursor.
The results are as follows:
Each cell, including the row, heading, column heading, and the "statistic" cell (top-left) are all R calculations.
Each cell has its own name, created by default.
Demonstration: Creating a New Calculation Grid
Navigating Calculation Grid
The are a number of useful Keyboard Shortcuts available to help you move around in Calculation Grid. They are as follows:
| In In-cell editor (not the Object Inspector editor) | |
| Shortcut | Action |
| Enter | Commits the code and moves down |
| Shift + Enter | Commits the new code and moves up |
| Alt + Enter | Moves the cursor to a new line |
| ESC | Cancels editing of cell's code |
| Cell selection mode (navigating through cells but not editing them) | |
| Shortcut | Action |
| Tab | Moves to the cell on the right |
| Shift + Tab | Moves to the cell on the left |
| Backspace OR Delete | Deletes the formula/text inputted into the cell |
Editing Cells in Calculation Grid
You can edit cell values either by double-clicking, typing F2 or using the code editor in the object inspector.
For example, if I click the Row Label 1 from this grid, and then right-click on the cell, the R CODE window on the right shows the calculation, which in this case is "Row Label 1". Because Row Label 1 is a text value, it is entered between double-quotes.
Numeric values don't require quotes:
Editing cells in Object Inspector:
- Select the table
- Click the cell you want to edit
- Edit it in the R CODE window
Double-clicking a cell to edit:
- Double-click the cell
- Directly enter your code. This includes row or column headers, as well as the very top left cell.
If you have a lot of code to write, continue typing your code and the cell with automatically expand to the right. If you need additional lines, hold down the ALT key and keep typing the Enter key until you get the desired number of lines.
Using the F2 key to edit a cell
Instead of double-clicking, you can also use the F2 key to switch you to the code editor.
The steps are as follows:
- Click the cell you want to edit.
- Type F2 on your keyboard
- Type your new code in the cell
- Type ESC to exit the cell
For example,
Be sure you enter syntactically correct R Code. For example, to enter text, you must enter it in "double quotes".
Perform calculations on the cells on tables
Excel-like calculations, where the inputs are cells in a table, are done by point-and-click. In this example, the detractor percentage is subtracted from the promoter percentage.
To recreate the same sort of operation yourself:
- Click on Calculation in the toolbar (alternatively, select from the Calculation menu, select Custom Code)
- Click on your page where you wish to display your output and/or R code.
- Click the Promoters percentage, type a subtraction sign, and click the Detractors percentage
- Click Calculate in the Object Inspector on the right.
Use formulas with code completion
In addition to doing simple math, you can also use formulas (e.g., Sum, SumEachRow). A few tips:
- As you type the function, Displayr will present a list of its best guesses and you can click on them (see the screen recording below).
- Displayr cares about capitalization. That is, sum, Sum, and SUM are all different things (and, most of the time you will be wanting Sum).
- The names in the menus match the function names (e.g., Sum EachRow is the same function found in the toolbar under Calculation > Sum > Sum Each Row).
- In addition to performing calculations on tables, you can also use functions when creating new variables.
Programming in R
Working in Calculation Grid often involves writing R code. If you are not an expert in R but still want to write R code, you still can.
For example, suppose you have this Calculation Grid and want to subtract the percentage of people who say they love Coke Zero from the percentage of people who say they love Coca-Cola.
These are the steps:
- Click on Calculation in the toolbar (alternatively, select from the Calculation menu, select Custom Code)
- Click on your page where you wish to display your output and/or R code.
- Click the Coca-Cola Love percentage, type a subtraction sign, and click the Coke Zero Love percentage
- Click Calculate in the Object Inspector on the right.
Manipulating Rows & Columns
Adding Rows / Columns
You can add columns and rows with the inserter and drag to reorder them.
Additional Rows and/or Columns can be added by using Grid > Properties on the right. In this example, we are requesting a Calculation Grid with 7 Rows and 5 Columns.
To add columns and/or rows:
- Select the Calculation Grid table to view Grid > Properties
- Use the Rows and/or Columns pull-down menus as required to specify the desired number of rows and columns.
Inserting Rows / Columns
You can add columns and rows with the inserter and drag to reorder them.
- Select the Calculation Grid
- Hover over the location when you would like to insert a row or column until you see the insert tool symbol:
.
- Click the symbol
- A blank space will appear between the two labels.
- Double-click the blank space and type a label (be sure the label is between double-quotes)
Deleting Rows / Columns
To delete a row or column from Calculation Grid:
- Right-click the header of the row or column you want to delete
- From the menu, select Delete Row (Column)
For example:
Reordering rows or columns
You can move rows and columns from a menu by
- Right-clicking the header of the row or column you want to move
- From the menu, for rows select Move Up or Move Down, and for columns select Move Left or Move Right for columns. For example,
You can also drag to reorder rows and columns.
- Click the header of the row or column you want to move
- Drag the
box up or down to where you want to move the row. For columns, drag the box to the left or right.
- Release the mouse button at the desired location.
For example, to move Column 2 to the left of Column 1:
Copy/Paste Values in a Calculation Grid
The following copy/paste operations are supported by Calculation Grid:
- Copy and paste within the same grid
- Copy from one grid to another
- Copy from Excel and paste into the grid
- Copy out of the grid as text values to paste into Excel
To copy / paste calculation grid cells:
- Select the cell(s) you want to copy.
- Right-click the highlighted cell(s) and select Copy
- Click the target cell and select one of the Paste options.
- If you are copying an entire row of column, select all of the cells in the row or column, click the first cell in the row or column you pasting to, and then select one of the Paste options.
Paste Code pastes the Value in the target cell and the R Code in the R Code window
Note: This option will only be enabled if the item you have copied contains R Code, and using it will put the copied item's code inside the selected cell(s).
Paste Link pastes the source Cell Location (Link) in the R Code window and the Value in the target cell. This pastes a live connection to a number from another output in Displayr. This Value will update as the original output changes. For example:
Paste Values pastes the Value in both the target cell and in the R Code window. This just pastes a Value that will not update as other data changes. For example:
Demonstration: Copy/Paste Cells in the Same Grid
Demonstration: Copy/Paste From Excel
Modifying the Calculation Grid Properties
The properties window on the right allows you to modify your Calculation Grid.
The following properties can be modified:
- Font
- Table Style
- Decimals
- Number format (either % or $)
Changing the font
- Select the table to enable Font editing
- In the Calculation Grid Properties window on the right:
- Select Appearance > Fonts
- Select the font you want.
For example:
The results are as follows:
Changing the Table Style
- Select the table
- From the Appearance > Table Style menu, select the style you want
The results are as follows:
Changing the Number of Decimals
- Select the table
- From to the right of Appearance > Decimals, select
-
for more decimal places
-
for fewer decimal places
-
In this example, the number of decimal places is changed from one place to two places:
Changing the Number Format
You can change the number format to either or
or to just a number by using either of these symbols to the right of Appearance > Numeric Format on the right:
For example, let's say I start with this table and would like to change the format to :
- Select the table
- Click the
sign to the right of Appearance > Number format. Notice that the symbol is now highlighted which indicates the current number format for the table
The results are as follows:
Next