PowerOne can handle multiple data types. One of those is tables. A table is a collection of numbers that all relate to each other.
Table variables are defined using brackets and semi-colons and can be either one-dimensional (a single column) or two-dimensional (multiple columns and rows).
[https://.../t/help_variables|Learn more about variables].
Within templates, tables are created by separating rows with returns and columns with semi-colons. For instance, a single-column table with values 3, 4, 5 and 6, in the template, would appear as follows (select the row to view its values):
table = [3;4;5;6]
A table with one row of values 1, 2, 3 and a second with values 4, 5, 6 would appear as follows (select the row to view its values):
table = [[1;2;3]; [4;5;6]
And a table with no default data would be designated with only brackets:
Internally those tables use brackets [ ] to separate rows and columns. That same single-column table is written as follows:
The multi-column table as follows:
And finally the empty table is as follows:
The "bracket notation" version is how tables are designated when creating templates.
In addition, tables of unit values can be created. Tables support the same units as number rows. An empty table of length units can be designated as follows:
And the single-column table of meters could look like this:
Note that tables store all units as a single type. The first unit found in the table, at row 1, column 1, is the unit used to store the rest of the table. So while each cell can have a different unit of the same type (length, area, volume, etc.), it will always be stored as the unit in row 1, column 1. When the table is presented next time, all values will be presented as the unit in row 1, column 1.
There are two types of tables. The first type of table is entered by users and used for calculation. Examples are [https://.../t/c8486805c46d57d87501 | Cash Flows] and Statistics ([https://.../t/978bb40758d638ba7847 | one-variable], [https://.../t/3f45c711c20b46b0c0be | two-variable]) templates, where the user enters a series of data points in a table and those data points are used to calculate information. These types of tables are meant to be editable by the user in the template itself.
A second type of table is used for looking up values. Lookup tables retain data or information that assists when calculating. Lookup tables are generally used internal to the template so their data is not exposed to template users. Marking these types of tables as [https://.../t/help_variables | constants] in **details** ensures that lookup tables are used correctly. See the [https://.../t/help_functions_tables|Math Functions: Tables/Examples] section for an example of creating and using lookup tables.
Indexes, Item, Length and Width
Each value in a table is at a specific index. The first value in the table is at index 1. For instance if the resulting table is 5, 7, 9 then 5 is at index 1, 7 is at index 2, and 9 is at index 3. We can retrieve a value at an index with the item() function:
numbers = [5; 7; 9]
Value at Index 1 = 5
Value at Index 2 = 7
Value at Index 3 = 9
You can determine the maximum number of rows by using the function length():
and the maximum number of columns by using the function width():
number of rows = 3
number of columns = 1
Every row of a table must have the same number of columns, and every column of a table must have the same number of rows.
While it is not required, naming columns has two purposes. First, it helps remind you and anyone using your templates what values are in each column.
Second, naming columns helps PowerOne fill in missing values for you. Each row must have the same number of columns. PowerOne adds missing data, filling it in with 0, and truncates excess data.
To add columns, select **details** then add names to **Column Names**, one per line.
The occurrences column is common in certain types of tables. For instance if the value 10,000 is repeated 6 times in your data, often it is easier to say that the value 10,000 occurred 6 times then to type 10,000 six times. [https://.../t/c8486805c46d57d87501|Cash Flows] and [https://.../t/978bb40758d638ba7847|One-Variable Statistics] templates use this table form.
To create these special tables, create a regular table, give it two **Column Names** and then set the **Last column is occurrences** checkbox.
The name of the last column doesn't matter; just that there are at least two columns and the last one is intended for occurrences. Now when entering data in the table, you can optionally include an occurrences value. For instance a table where value -50,000 occurs once, 10,000 occurs 6 times and 20,000 occurs once would be entered like this in the template (select the row to view its values):
table = [[-50000;1];[10000;6];[20000;1]]
Note that entering only a single occurrence is optional and doesn't appear in the template. In bracket notation, it looks like this:
As of version 6.3 released on September 4, 2020, tables store units in their full precision and round only on presentation, the same as number variables. Decimals and Units in details only impact the displayed value, not the stored one. Occurrence columns, however, are always truncated to 0 decimal places.
In version 6.2 and earlier when entering tables, the value displayed is also the value stored. This is different from number variables which store their entire value and only show you a portion of them. To store as much of the value as possible (approximately 12 places at most), set **Places** to float in the **details**. If you prefer to show a certain number of decimal places, however, set Places to a specific number of places. All values in the table, except columns marked as occurrences, will be rounded to that number of decimal places. Occurrence columns are always truncated to 0 decimal places.
While there are some specific [https://.../t/help_functions_tables|math functions] for tables, such as item(), length() and width() in the examples above, tables can generally be used with any math functions that also work for individual numbers. When individual numbers are used then individual number results are returned. When a table is involved, a table is returned. For instance, you can add two numbers, a number to a table or add two tables:
3 + 4
3 + [5; 6; 7]
[5; 6; 7] + 3
[1; 2; 3] + [4; 5; 6]
Adding two numbers, as in the first example, does what you'd expect: add 3 and 4 to produce 7. When performing math between a number and table, however, math is performed on each item in the table. In the second and third examples, 3 is added to each of the table items, returning a new table:
3 + [5; 6; 7] is the same as [3+5; 3+6; 3+7], which returns table [8; 9; 10]
[5; 6; 7] + 3 is the same as [5+3; 6+3; 7+3], which returns table [8; 9; 10]
Finally, when performing math on two tables each value at the same index is calculated independently and then placed into a third table, which is returned. In the fourth example, each value of the table is added to the value at the same index to create a new table:
[1; 2; 3] + [4; 5; 6] is the same as [1+4; 2+5; 3+6], which returns table [5; 7; 9]
When working with two tables they must be the same length or an 'Invalid dimensions' error will be returned.
While performing math on a table generally returns a table, there are also some functions available that return a singular number instead. length() and width(), from the section above, are two such examples. In addition, sum(), avg(), npv() and others are available for performing math on a table. See the sections on [https://.../t/help_functions_statistics|Statistics] and [https://.../t/help_functions_finance|Cash Flows] for additional math functions specific to returning singular results from tables of data.
Finally, there are multiple functions available for manipulating tables themselves. See the [https://.../t/help_functions_tables|math functions] section on Tables for details.
A look-up table is a table whose value is set and never re-calculated. Look-up tables are useful when a table consists of data used in equations. Look-up tables may never change or changes only occasionally, making it easier to understand and update a template.
To set a look-up table, create a table variable with a default value then select **details** and check the **constant** checkbox. Note that look-up tables (constants) are hidden by default.
See the examples in [https://.../t/help_functions_tables | Math Functions: Tables] for details.