Excel

Please note: This page is currently being updated.

This article is a stub.

What is an Excel file

An Excel file is a file, consisting of one or multiple tables of values, generally used in business environments for administration and calculation purposes. A single file can consist of multiple sheets. Each sheet is a grid of cells, each possibly containing either a value or a formula.

Excel files can be created and edited using a variety of programs. The most obvious program is by using Microsoft Office's Excel. Alternative programs include OpenOffice or LibreOffice, although minor differences in both use and calculation can occur, and support for these differences may be limited.

For the Tygron Engine, the file format .xlsx is specifically required.

How do Excel files relate to the Tygron Engine

One of the core functions of the Tygron Engine is to calculate prospective effects of actions and scenarios. Depending on the case being explored, what exactly to calculate and the precise method of calculation can vary. To provide the greatest freedom in the ability to configure a project with relevant, accurate, and third-party approved calculation models, the Tygron Engine allows some calculation to be made in Excel files. Most notably indicators can easily be created or adjusted by inspecting and editing the underlying Excel file. By defining specific cells to act as input and output for the calculation, the Tygron Engine can perform calculations using user-defined Excel files.

General structure of Excel files

Excel files, in general, have three parts to them: input, calculation, and output. The input and output are "connected" to the Tygron Engine. The contents of each input cell is changed by the Tygron Engine to reflect some form of data from the project. Each output cell is eventually read by the Tygron Engine, and its content used in some fashion. Input cells, and some of the output cells, are defined using TQL.

Cell types

Cells can be divided into 4 categories: Output, Input, Calculation, and Unused cells.

Output

In this image the cell B2 is named "EXPLANATION". This serves as an output cell. For its content it refers to a different cell.

The most important part the Excelfile is the output. At least one cell in the file is required to be an output cell, the content of which the Tygron Engine can use during a session. In each excel file, one of the cells must be named EXPLANATION. Excel Indicators and Zone Excels have a number of other output terms available for use. More output cells can be defined using TQL's UPDATE statements.

Input

In this image the cell B4 is named "SELECT_UNITS_WHERE_STATE_IS_READY". This serves as an input cell. It has a value, which will be overwritten by the engine.

Next to the output cells, the input cells are most important. You will generally want your Excel file to produce different outputs based on the current state of the project or session. Input cells are always defined using TQL. When the Excel file is uploaded and used by the Tygron Engine, the content of the input cells will be overwritten by the Engine with the proper, requested value.

Calculation

In this image the cell D3 contains a formula. It's not directly overwritten by the engine, but the results change based on input from the engine. It is referred to by an output cell, so this is a calculation cell.

Lastly, there are the calculation cells. It's likely you don't want to output the data from the engine directly. Instead, you want to use it in some form of calculation, and display only the result, likely with some formatting. For example, you may not want to return the amount of green and the amount of houses, but the amount of green per house, rounded to a decimal or two, with some unit appended to it. Any cell, even empty cells, are deemed to be calculation cells if they are referenced by an output cell, because their contents are calculated (evaluated) to determine what the output will be.

Unused cells

In this image the cell C3 is empty and unnamed, so it is not an input or output cell. It is also not referred to, so its an unused cell.

Lastly there are unused cells. These are cells which are not input cells, and are not referenced directly or indirectly by output cells. Empty cells, when referenced directly or indirectly by output cells, are evaluated, and thus seen as used. Unused cells can contain values or even formulas, or even reference input cells. Unused cells with formulas can be useful when creating or inspecting excel sheets, to provide insight in how the calculation works.

Naming cells

The functioning of Excel files in the Tygron Engine hinges on the named cell functionality in Excel files. Input cells and Output cells require names to be set before they function as such. If the required output cells don't exist (for example, no cells are named EXPLANATION), the Excel file will not function as expected in the Tygron Engine. To set a cell name in an Excel file, select the cell and enter its name in the name field (just under the ribbon).

How to name a cell:

1. Open the Excel file in Microsoft Office Excel
2. Select the cell you wish to use as output cell
3. In the top left of the window, find the "Name" field (just under the ribbon)
4. Enter the name for the cell (for example: EXPLANATION)
5. Press "Enter"

Correcting and removing cell names

There may be a number of reasons to correct the naming of a cell:

• The cell is no longer required
• The cell no longer exists
• The cell has been given multiple names
• The same name has been applied to multiple cells, or a range of cells
• The name of the cell is no longer correct, and needs to be changed

To correct issues like these, you can use the Name Manager in Microsoft Office Excel. You can find the Name Manager under the "Formulas" tab in the ribbon.

The Name manager displays a list of all assigned names in the Excel file, as well as what cells they refer to, and what the current value of those cells are.

To edit a cell name, or change what cells are referred to by that name, select it, and select "Edit". The "Edit Name" panel will appear, where you can enter a new name, as well as redefine to what cell the name refers.

To delete one or multiple names, select them in the Name Manager, and select "Delete". You will be asked to confirm that action.

Using Excel files in the Tygron Engine

Excel files are used in a number of places in the Tygron Engine, including Indicators, Zones, and Panels. To allow for the reuse of Excel files, the Tygron Engine presents an Excel File Manager, in which Excel files can be uploaded, replaced, and selected for use.

Uploading and Downloading

When, in the Editor, an item is selected which uses an Excel file, it will present you with the option to "Select Excelsheet". This will open the Excel File Manager.

How to select an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to use
3. Select "Apply"

How to download an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to use
3. Select the "Download" option
4. Select the location to save the file and select "Save"
5. Select "Cancel" in the Excel File Manager to close it

How to import a new Excel File:

1. Open the Excel File Manager
2. Select "Import new Excelsheet"
3. Browse to the Excel file you wish to import and select "Open"
4. Select the file you wish to use (which may or may not be the imported Excel) in the Excel File Manager
5. Select "Apply"

How to replace an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to replace
3. Select the "Update" option
4. Browse to the Excel file you wish to import and select "Open"
5. Select the file you wish to use (which may or may not be the replaced Excel) in the Excel File Manager
6. Select "Apply"

Debugging

Sometimes, when you look at the output of an Excel-based calculation in the engine, you may not directly recognize how a certain result was achieved. As more complex Excel files are uploaded, this may occur more often. At these times, it may be desirable to see how the Excel file has been filled by the Tygron Engine, and how these values are treated by the Excel file's calculation. This can be done by downloading the Excel file using the "Debug Excelsheet" option. This will allow you to download the Excel file with the current values filled in.

After download, you can open the Excel File in Microsoft Office Excel. You will see that all input cells are filled with values from the Engine. Inspecting the output cells and tracing back through your calculation will allow you to determine how certain values came to be, based on the provided input.

It is important to note that the downloaded file may have lost some formatting in the sheet, as well as contain some project-specific adjustments when "X" or "ID" queries were used. For this reason, we advise not to re-upload Excel files which were downloaded with current values, even when they require a change altered. Instead, download the original file using the Excel File Manager, adjust it, and re-upload that version.

How to debug an Excel File:

1. Select the item with the relevant Excel File in the editor
2. Select "Debug Excelsheet"
3. Select the location to save the file and select "Save"
4. Download the original Excel file as well
5. Open the Excel file with the current values and step through it to find any issues
6. Make any desired changes in the original Excel file
7. Upload the modified Excel file

Advanced Excel techniques

Filtering data, HTML, combining text, VARs, X queries, ID queries, _CURRENT and _MAQUETTE postfixes, colors in attributes, colors in HTML/CSS

Excel tips

Performance, colors in excel formatting, using sheets, possible issues