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

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

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.

Calculation

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

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.

Using Queries

Create query in Query tool, name cell

Using Excel files in the Tygron Engine

Used in a number of places, but function the same everywhere

Uploading and Downloading

Either upload a new file, select an existing file, or update an existing file

Debugging

Download file using Debug Excelsheet, inspect values, make changes to original and upload adjusted original

Advanced Excel techniques

Filtering data, HTML, combining text, VARs, X queries, ID queries

Excel tips

Performance, colors, using sheets, possible issues