Math Functions: Tables Calculator

Math Functions: Tables

Tables are extremely flexible and useful in PowerOne. Think of them as a grouping of real numbers that is related to each other. They can be used for capturing similar data and for performing math on sets of data instead of individual data points. Most functions that support individual numbers also support tables.

Note that tables cannot be solved when solving equations. Instead, calculate tables or results from tables using direct computation only.

[https://.../t/help_variables_tables|Learn more about tables as variables.]

Creating Tables

⇥[ ]

To define a table variable, assign the variable to brackets [ ]. The template's user can then add data to the table.

You can also create lookup tables. To do so, assign either a single-column or multiple-column table to the variable and check **Constant** in the **details** view. A single-column table with values 3, 4 and 5 is defined as follows:

<[3; 4; 5]>

A multi-column table with the first row with values 3, 4, 5 and the second with elements 8, 9, 10 is defined as follows

<[[3; 4; 5]; [8; 9; 10]]>

See [https://.../t/help_variables_tables | Table Variables] section for more details.

__This function is only available in templates.__

Table Math

While there are some specific math functions for tables (outlined below), 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. Functions such as sum(), avg(), npv() and others are available for performing math on a table. See the sections on Statistics and Cash Flows for additional math functions specific to returning singular results from tables of data.

Tables in Equations

Tables can be created in equations as well. To do so, define the table directly within the equation itself. The table can consist of variables and other equations as long as it surrounds the correct bracket [ ] format for rows and columns. Note that PowerOne may interpret the equation as a variable. Make sure you set its **Type** to equation.

For instance, to create a table from variables Subtotal 1, Subtotal 2, and Subtotal 3:

Make sure the type is marked as an equation and Subtotal Table will be created automatically, consisting of a table with the values for three subtotals.

To calculate a sum, for instance, you can now use its function:

Table Information

⇥Length

++length(table)++

Returns the number of rows in 'table'.

__This function is only available in templates.__

**Examples**

See Example 1: Indexes, Items & Bounds Checking below

⇥Width

++width(table)++

Returns the number of columns in 'table'.

__This function is only available in templates.__

Modifying Tables

⇥Append

++append(table1; table2)++

Returns a new table where 'table2' is appended to the end of 'table1' as new rows.

++append(table1; table2; asColumns)++

Returns a new table where 'table2' is appended to 'table1' as rows if 'asColumns' is 0 and as columns if 'asColumns' is 1.

__This function is only available in templates.__

**Details**

- If 'asColumns' is 1 then 'table1' and 'table2' must be the same length or 'Invalid dimensions' is returned

- The two tables must have the same number of columns otherwise it returns an 'Invalid dimensions' error

⇥Column

++column(table; index)++

Returns a table representing the column at 'index'.

++columns(table; start; end)++

Returns a table representing the columns starting at 'start' index and ending at 'end' index.

__This function is only available in templates.__

**Details**

- The first column in a table is index 1

- 'index', 'start' or 'end' less than 1 or greater than the number of columns will return an 'Invalid dimensions' error

- A table with no values returns 'Invalid array dimensions' error

- 'end' less than 'start' returns 'Parameter out of range' error

⇥Row

++row(table; index)++

Returns a table representing the row at 'index'.

++rows(table; start; end)++

Returns a table representing the rows starting at 'start' index and ending at 'end' index

__This function is only available in templates.__

**Details**

- The first row in a table is index 1

- 'index', 'start' or 'end' less than 1 or greater than the number of rows will return an 'Invalid dimensions' error

- A table with no value returns 'Invalid array dimensions' error

- 'end' less than 'start' returns 'Parameter out of range' error

⇥Subset

++subset(table; startRow; endRow; startColumn; endColumn)++

Returns a table representing the rows between 'startRow' and 'endRow' (inclusive) and 'startColumn' and 'endColumn' (inclusive) within table.

__This function is only available in templates.__

**Details**

- The first row and column in a table is index 1

- 'endRow' less than 'startRow' or 'endColumn' less than 'startColumn' returns 'Parameter out of range' error

- 'startRow' or 'endRow' less than 1 or greater than the number of rows returns 'Invalid dimensions' error

- 'startColumn' or 'endColumn' less than 1 or greater than the number of columns returns 'Invalid dimensions' error

⇥Compress

++compress(table)++ : converts a single- or multi-column table without occurrences into a table with occurrences. The occurrences column is added as the last column.

__This function is only available in templates.__

**Example**

returns:

<[[4; 2; 2]; [3; 4; 1]]>

Visually, compress() used on this table:

4; 2

3; 4

4; 2

returns:

4; 2; 2

3; 4; 1

⇥Uncompress

++uncompress(table)++ : converts a multi-column table where the last column is considered the occurrences into a table without occurrences. Single column tables return themselves without change.

__This function is only available in templates.__

**Example**

returns:

[[3; 4]; [4; 2]; [4; 2]]

Visually, uncompress() used on this table:

3; 4; 1

4; 2; 2

returns:

3; 4

4; 2

Table Items & Searching

⇥Item

++item(table; index)++

Returns the value from the first column in 'table' at 'index'.

++item(table; row; column)++

Return the value at the intersection of 'row' and 'column' in 'table'.

__This function is only available in templates.__

**Details**

- The first row or column in a table is index 1

- 'index', 'row' or 'column' less than 1 or greater than the number of rows or columns will return an 'Invalid dimensions' error

**Example**

See Example 1: Indexes, Items & Bounds Checking below

⇥Lookup

++lookup(table; value)++

Returns the row's index where 'value' exactly matches a value in the first column of 'table'.

++lookup(table; value; column)++

Returns the row's index where 'value' exactly matches a value in 'column' of 'table'.

++lookup(table; value; column; method)++

Returns the row's index where 'value' uses the 'method' approach to finding a value in 'column'. 'method' set to -1 returns the value that is less than or equal to 'value', 'method' set to 0 returns an exact match, and 'method set to 1 returns the value that is greater than or equal to 'value'

__This function is only available in templates.__

**Details**

- 'value' matches the first value found. While 'column' can be in any order for an exact match ('method' not provided or set to 0), less than ('method' set to -1) or greater than ('method' set to 1) searches require 'column' to be in ascending sort order

- The first index in a table is index 1

- If no match is found then returns 0

**Examples**

See Example 2: Lookups and Example 3: Lookup Method below

⇥Value Lookup [vlookup]

++vlookup(table; value; returnColumn)++

Returns the row's value in 'returnColumn' where 'value' exactly matches a value in the first column of 'table'.

++vlookup(table; value; inputColumn; returnColumn)++

Returns the row's value in 'returnColumn' where 'value' exactly matches a value in 'inputColumn' of 'table'.

++vlookup(table; value; inputColumn; returnColumn; method)++

Returns the row's value in 'returnColumn' where 'value' uses the 'method' approach to finding a value in 'inputColumn' of 'table'. 'method' set to -1 returns the value that is less than or equal to 'value', 'method' set to 0 returns an exact match, and 'method set to 1 returns the value that is greater than or equal to 'value'.

__This function is only available in templates.__

**Details**

- The first row in a table is index 1; the first column in a table is index 1

- If no match is found then returns 'Parameter out of range' error

**Examples**

See Example 2: Lookups and Example 3: Lookup Method below

⇥Row Lookup [rlookup]

++rlookup(table; value)++

Returns the table's row where 'value' exactly matches a value in the first column of 'table'.

++rlookup(table; value; column)++

Returns the table's row where 'value' exactly matches a value in 'column' of 'table'.

++rlookup(table; value; column; method)++

Returns the table's row where 'value' uses the 'method' approach to finding a value in 'column'. 'method' set to -1 returns the value that is less than or equal to 'value', 'method' set to 0 returns an exact match, and 'method set to 1 returns the value that is greater than or equal to 'value'

__This function is only available in templates.__

**Details**

- The first row in a table is index 1

- If no match is found then returns 'Parameter out of range' error

**Examples**

see Example 2: Lookups and Example 3: Lookup Method below

Sorting

⇥Sort

++sort(table; column)++ : returns a table that is sorted in ascending order based on 'column'. When it sorts, all rows are re-ordered based on the new position of column.

++sort(table; column; descending)++ : returns a table that is sorted based on 'column' where 'descending' is 1 for descending order or 0 for ascending order. When it sorts, all rows are re-ordered based on the new position of column.

__This function is only available in templates.__

**Details**

- The first column in a table is index 1

- 'column' less than 1 or greater than the number of columns will return an 'Invalid dimensions' error

Examples

In many sales situations, companies will offer a price break for additional units purchased. This chart lays out those price breaks with each tier of pricing in the left-most column:

[https://equals.s3.amazonaws.com/help/lookup_table.png|lookup table]

For a single unit tier 1-4 lists the price as $50, for 5-29 units tier 2 lists the price as $45 per unit, for 30-99 units the price is $40 per unit (tier 3), and so forth.

To create a template using this chart, it would be entered as follows and should be marked as a constant since it doesn't change (referred to as a lookup table):

Pricing = [[1; 50]; [5; 45]; [30; 40]; [100; 35]; [1000; 30]; [10000; 25]]

Example 1: Indexes, Items & Bounds Checking

The tiers act as indexes into the rows of the table.

The item() function makes it possible to retrieve the number of units or price/unit for any tier. For instance, we can write an equation that will return the units given a tier:

We can also write an equation to retrieve the price per unit given a tier:

And here's the template to return the appropriate information:

Tier = 1

Units for Tier =

Price/Unit for Tier =

Note that entering a negative number, 0, 7 or a number greater than 7 will return an error since there are no tiers outside 1-6. Instead, we want to show 0 units and price per unit if the user enters bounds above or below the acceptable range. We can use if() and length() to guard against this:

And then use those equations to refine our example. Now change Tier to 7. Notice that we get 0 for Units for Tier and Price/Unit for Tier:

Tier = 1

Units for Tier =

Price/Unit for Tier =

Example 2: Lookups

Getting the units or price/unit is useful but the total price of the invoice still needs to be calculated. Since units are sold in packages we can use the lookup table to determine the price per unit then calculate a total price based on the number of units.

First, we will change the Units so the user can pick from a list. Since we have a finite set of pricing tiers, instead of bounds checking, which can make the formula complicated and keep the template user guessing as to the lower and upper bounds, we will instead convert Units to a list:

Now the user can only pick tiers, and do so with a readable value that indicates how many units they are picking.

Take a closer look at that list. It's a name followed by a default (1) then the list itself. List entries are in the form description:value. For instance, if the user picks option **30-99** then we want to return a value of 30.

Second, we determine the Price/Unit. To do so we will use the vlookup() function, which looks up a value given a table, a value and a column. Our formula looks like this:

vlookup() will look through the Pricing column for a match for Units and, if found, return the value in column 2. For instance, if units is 30-99, it will find a match for that in the Units column and then return the accompanying value in column 2, which is $40.

Finally, we use Price/Unit to calculate a total using this equation:

Our working example for you to try:

Units = 1

Price/Unit = 50

Total = 50

Also note that there are variations of the lookup function. lookup() returns the index, vlookup() returns the value in the designated column, and rlookup() returns the entire row. rlookup() is useful when we need multiple values from a row. We can first fetch the row then use that row (now its own table) and item() to retrieve values from the row.

Example 3: Lookup Methods

The lookup method used above focuses on a direct match. If units was entered as 310 for instance, no match would be found and PowerOne would return an error.

Instead of selling units in packs, management wants to sell individual units at the appropriate price. For instance, 1-4 units would still be $50 but 5-29 units would be $45 each, 30 to 99 units would be $40 each, etc.

To accomplish this we can make two changes in the Price/Unit equation and change Units to be a number instead of a list:

As before, vlookup() will look through the Pricing table and, using the value entered from Units, will try to find the closest match in column 1 and, with a match, return column 2. This time, though, we will tell the vlookup() function to match less than or equal to the Units in column 1 by setting the last parameter to -1. In this case Units set to 6, for instance, will find a match in the second row (5-29) and return the price per unit in column 2, or $45.

Again, a working example for you to try:

Units = 1

Price/Unit = 50

Total = 50

Keywords

Tier

Units for Tier

Price/Unit for Tier

Tier

Units for Tier

Price/Unit for Tier

Units

Price/Unit

Total

Units

Price/Unit

Total

-------------

Tables

[]

Length

length()

Width

width()

Append

append()

Column

Columns

column()

columns()

Row

Rows

row()

rows()

Subset

subset()

Item

item()

Lookup

lookup()

Value Lookup

vlookup()

Row Lookup

rlookup()