Excel Indicator
|
Please note: This page is currently being updated! |
Excel Indicator
The Excel Indicator is an indicator that uses a Microsoft Excel File to do the calculations. Spatial information can be send to the Excel and score and explanation information is retrieved from the Excel to show in the Indicator Panel.
Quick Start
First create a new blank Excel, or use an existing one. In the image below you can see a created Excel with Function Categories. As an example, we are interested in calculating the percentage per construction type that are within a safe zone. In the Tygron Engine, a Terrain with the type SafeZone has already been created.
In order to get the amount of Floor Space of constructions within the SafeZone, a Cell can be given a particular Name:
The Name is shown in the upper left text field in the Excel. This Name is actually a query command that can be interpreted by the Tygron Engine, also known as TQL. In this case it says:
SELECT_FLOORSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_MAP_IS_MAQUETTE_AND_TERRAINTYPE_IS_SAFE_ZONE
This query is not hard to read or understand. To retrieve the total amount of Affordable (Social) Housing, the query is as followed:
SELECT_FLOORSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_MAP_IS_MAQUETTE
Also see the image below:
The naming of cells should be done carefully, because making a mistake here will result in an error during the calculation of the Excel Indicator. Also note that, once a cell has been named, the Microsoft Excel Name Manager must be used to edit the name of the cell. This Name Manager can be opened as followed:
Here you can find all defined names in your Excel and to what cells they refer. If you want to edit a cell name, click on the Edit Button. The following panel will appear:
In this panel you can edit both the name as the cell it refers to. This is the easiest way to correct a label. Note that you can also refer two labels to the same cell, something that happens quite often during renaming. The image below shows a Cell with two references. Both a MAQUETTE as a CURRENT query are referring to the same cell. One will override the other and the order is unpredictable. Make sure you resolve these issues. You can delete the name, or let it refer to another cell.
In order to generate scores and explanations, you need to have either one named cell: SCORE, or two named cells: SCORE_CURRENT and SCORE_MAQUETTE. In Planning games, the SCORE_MAQUETTE is most used, in TimeLine games both are important to indicate the difference between the planned situation and the current situation.
- Excel quick start 7.jpg
- Excel quick start 8.jpg
Last but not least is the explanation. This is the text shown in the lower left corner of the Indicator panel during a game session. The explanation text can be written in html format. The cell should be named EXPLANATION. Note that when using explanation texts, you also want to include numbers and or text calculated by your excel. You can include '%s' values, which will be replaced by VAR_# cells, starting from VAR_0, VAR_1 etc. If the explanation text has n '%s' parts and any VAR_# between 0 and n is not present, that explanation text will not be evaluated. Instead, an error is shown containing the following message:
Tips
- Divisions by zero can be solved as in this example (Cell E24): =C2/IF(D2,D2,1)
- Instead of using the VAR_# method for the explanation text, you can also use the concatenate function of excel:
CONCATENATE("<nowiki>
",A2, ": ", E2, " m2 and in Safe Zone: ", F2, " m2
")