Excel - Revision history

Maxim@tygron.com at 11:31, 13 July 2026

2026-07-13T11:31:51Z

← Older revision		Revision as of 11:31, 13 July 2026
Line 130:		Line 130:
	{{article end		{{article end
	\|seealso=		\|seealso=
			* [[Indicator]]
	* [[Var (Excel)]]		* [[Var (Excel)]]
	* [[TQL]]		* [[TQL]]

Maxim@tygron.com at 11:31, 13 July 2026

2026-07-13T11:31:33Z

← Older revision		Revision as of 11:31, 13 July 2026
Line 131:		Line 131:
	\|seealso=		\|seealso=
	* [[Var (Excel)]]		* [[Var (Excel)]]
	* [[Excel Indicator]]
	* [[TQL]]		* [[TQL]]
	\|howtos=		\|howtos=

Frank@tygron.nl at 13:10, 8 July 2026

2026-07-08T13:10:20Z

← Older revision		Revision as of 13:10, 8 July 2026
Line 90:		Line 90:
	===X queries===		===X queries===
	{{main\|X query (Excel)}}		{{main\|X query (Excel)}}
	~~For use-cases in which an Excel is used to aggregate~~ data, ~~for example to compute specific~~ scores per [[Neighborhood]] for ~~display in~~ an [[Indicator]], ~~it is not desirable to create~~ individual queries for ~~each piece of~~ data ~~that needs to be retrieved~~. Rather than stating each individual [[Neighborhood]] for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all [[Neighborhood]]s should be retrieved, regardless of how many or how few there may be. Such a query would look as follows:		When aggregating data (e.g., computing scores per [[Neighborhood]] for an [[Indicator]]), creating individual queries for every data point is inefficient. Rather than stating each individual [[Neighborhood]] for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all [[Neighborhood]]s should be retrieved, regardless of how many or how few there may be. Such a query would look as follows:

	<code>SELECT_LANDSIZE_WHERE_AREA_IS_X</code>		<code>SELECT_LANDSIZE_WHERE_AREA_IS_X</code>
Line 102:		Line 102:
	===VARs===		===VARs===
	{{main\|Var (Excel)}}		{{main\|Var (Excel)}}
	Calculated numbers can be automatically injected into the EXPLANATION output cell by defining specific cells as VAR cells. These effectively serve as additional output cells. The {{software}} ~~will take the value~~ in the EXPLANATION cell ~~and inject into spots marked~~ with ~~"%s" the numbers which are output in~~ the VAR cells.		Calculated numbers can be automatically injected into the EXPLANATION output cell by defining specific cells as VAR cells. These effectively serve as additional output cells. The {{software}} replaces "%s" placeholders in the EXPLANATION cell with values from the VAR cells.

	A cell named <code>VAR_0</code> will have its numeric value inserted in the EXPLANATION output cell's content, which for example would be "The value is %s, and now we know.".		A cell named <code>VAR_0</code> will have its numeric value inserted in the EXPLANATION output cell's content, which for example would be "The value is %s, and now we know.".
Line 121:		Line 121:

	===Excel formatting and descriptions===		===Excel formatting and descriptions===
	Large spreadsheets and workbooks can become complex and difficult to navigate. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. ~~Text~~ next to or above cells to explain the ~~reason of method of a~~ calculation ~~are a simply~~ method ~~for this~~. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.		Large spreadsheets and workbooks can become complex and difficult to navigate. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Adding descriptive text next to or above cells is a simple way to explain the calculation's purpose or method. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.

	<!--		<!--

Maxim@tygron.com at 17:07, 7 July 2026

2026-07-07T17:07:38Z

← Older revision		Revision as of 17:07, 7 July 2026
Line 1:		Line 1:
	~~[[File:Tql_webinar.png\|link=https://www.tygron.com/nl/2019/05/22/tql-en-indicators/]]~~
	~~[[Category:files]]~~

	==What is an Excel file==		==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.		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.
Line 142:		Line 139:

	{{Indicator nav}}		{{Indicator nav}}

			[[Category:files]]

Maxim@tygron.com at 17:07, 7 July 2026

2026-07-07T17:07:02Z

← Older revision		Revision as of 17:07, 7 July 2026
Line 5:		Line 5:
	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.		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.		Excel files can be created and edited using a variety of programs. A common program used for this is Microsoft Office 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 {{software}}, the file format .xlsx is specifically required.		For the {{software}}, the file format .xlsx is specifically required.

	==How do Excel files relate to the {{software}}==		==How do Excel files relate to the {{software}}==
	One of the core functions of the {{software}} 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 {{software}} ~~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 {{software}} can perform calculations using user-defined Excel files.		One of the core functions of the {{software}} 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 allow for flexible configuration of projects using relevant, accurate, and third-party approved calculation models, the {{software}} supports calculations via 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 {{software}} can perform calculations using user-defined Excel files.

	Excels can also output text to provide information about the current state of the [[Session]] or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with [[HTML]]. The {{software}} will interpret the text output of an Excel file as [[HTML]] if possible.		Excels can also output text to provide information about the current state of the [[Session]] or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with [[HTML]]. The {{software}} will interpret the text output of an Excel file as [[HTML]] if possible.
Line 22:		Line 22:
	====Output cells====		====Output cells====
	[[File:Explanation_named_cell.jpg\|framed\|right\|In this image the cell B2 is named "EXPLANATION". This serves as an output cell. For its content it refers to a different cell.]]		[[File:Explanation_named_cell.jpg\|framed\|right\|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~~ of the Excel file is the output. The {{software}} will detect each output cell, and calculate their contents. If those cells reference other cells, those other cells will be calculated as well. The {{software}} will continue this until the contents for all output cells are calculated. Each component which uses an Excel file for its calculation has one or more required output cells. The output can then be used during a [[session]]. Additional output cells are created when [[TQL]] <code>update</code> statements are added to the Excel.		The primary component of the Excel file is the output. The {{software}} will detect each output cell, and calculate their contents. If those cells reference other cells, those other cells will be calculated as well. The {{software}} will continue this until the contents for all output cells are calculated. Each component which uses an Excel file for its calculation has one or more required output cells. The output can then be used during a [[session]]. Additional output cells are created when [[TQL]] <code>update</code> statements are added to the Excel.

	'''Explanation'''<br>		'''Explanation'''<br>
Line 32:		Line 32:
	====Input cells====		====Input cells====
	[[File:Query_named_cell.jpg\|framed\|right\|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 {{software}}.]]		[[File:Query_named_cell.jpg\|framed\|right\|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 {{software}}.]]
	~~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 {{software}}, the content of the input cells will be overwritten by the {{software}} with the proper, requested value.{{clear\|right}}		Input cells are also essential for the functioning of the Excel file. 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 {{software}}, the content of the input cells will be overwritten by the {{software}} with the proper, requested value.{{clear\|right}}

	====Calculation cells====		====Calculation cells====
	[[File:Calculation_cell.jpg\|framed\|right\|In this image the cell D2 contains a formula. It's not directly overwritten by the {{software}}, but the results change based on input from the {{software}}. It is referred to by an output cell, so this is a calculation cell.]]		[[File:Calculation_cell.jpg\|framed\|right\|In this image the cell D2 contains a formula. It's not directly overwritten by the {{software}}, but the results change based on input from the {{software}}. It is referred to by an output cell, so this is a calculation cell.]]
	In between, there are the calculation cells. ~~It's likely you don't want to output the data~~ from the {{software}} ~~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 [[Green (Indicator)\|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. <br style="clear:right;">		In between, there are the calculation cells. Data from the {{software}} often requires processing before being output. 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 [[Green (Indicator)\|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. <br style="clear:right;">

	====Unreferenced cells====		====Unreferenced cells====
Line 120:		Line 120:
	Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go.		Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go.

	Do note that using this technique makes it very easy to create a very deep chain of commands to process by the {{software}}. When combining many hundreds of rows, the {{software}} may indicate it was unable to process the entire file due to ~~something called~~ a ~~"Stack~~ overflow". Make sure you use this combining technique only for limited amounts of rows.		Do note that using this technique makes it very easy to create a very deep chain of commands to process by the {{software}}. When combining many hundreds of rows, the {{software}} may indicate it was unable to process the entire file due to a stack overflow. Make sure you use this combining technique only for limited amounts of rows.
	<br style='clear:right'>		<br style='clear:right'>

	===Excel formatting and descriptions===		===Excel formatting and descriptions===
	~~When dealing with larger~~ spreadsheets and workbooks~~, it is easy for the structure to~~ become ~~overwhelming~~ and ~~thus less easy~~ to ~~understand~~. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.		Large spreadsheets and workbooks can become complex and difficult to navigate. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.

	<!--		<!--

Frank@tygron.nl at 14:35, 20 April 2026

2026-04-20T14:35:48Z

← Older revision		Revision as of 14:35, 20 April 2026
Line 138:		Line 138:
	\|howtos=		\|howtos=
	* [[How to prepare data in Microsoft Excel]]		* [[How to prepare data in Microsoft Excel]]
			\|api=*[[Api session items excelsheets]]
	}}		}}

	{{Indicator nav}}		{{Indicator nav}}

Rudolf@tygron.nl at 11:05, 25 February 2026

2026-02-25T11:05:00Z

← Older revision		Revision as of 11:05, 25 February 2026
Line 1:		Line 1:
	[[File:Tql_webinar.png\|link=https://www.tygron.com/nl/2019/05/22/tql-en-~~upgrades~~/]]		[[File:Tql_webinar.png\|link=https://www.tygron.com/nl/2019/05/22/tql-en-indicators/]]
	[[Category:files]]		[[Category:files]]

Frank@tygron.nl at 16:41, 21 February 2023

2023-02-21T16:41:25Z

← Older revision		Revision as of 16:41, 21 February 2023
Line 136:		Line 136:
	* [[Excel Indicator]]		* [[Excel Indicator]]
	* [[TQL]]		* [[TQL]]
			\|howtos=
			* [[How to prepare data in Microsoft Excel]]
	}}		}}

	{{Indicator nav}}		{{Indicator nav}}

Frank@tygron.nl: /* Excel formatting and descriptions */

2023-02-21T16:39:40Z

Excel formatting and descriptions

← Older revision		Revision as of 16:39, 21 February 2023
Line 130:		Line 130:
	COUNTIF and COUNTIFS don't seem to be processed correctly yet.		COUNTIF and COUNTIFS don't seem to be processed correctly yet.
	-->		-->

			{{article end
			\|seealso=
			* [[Var (Excel)]]
			* [[Excel Indicator]]
			* [[TQL]]
			}}

	{{Indicator nav}}		{{Indicator nav}}

Frank@tygron.nl: /* Excel formatting and descriptions */

2023-01-26T09:01:56Z

Excel formatting and descriptions

← Older revision		Revision as of 09:01, 26 January 2023
Line 131:		Line 131:
	-->		-->

	{{~~article end~~		{{Indicator nav}}
	~~\|seealso=~~
	* [[Excel indicator]]
	* [[Indicator]]
	}}