In USolver, you can pack a data set into a single cell, which is called a Data Table. The data can be entered/imported by the user, loaded from a web service, or generated by a formula.

Generating by a Formula

In USolver, a formula can return more than just a single value—it can return a matrix. If a formula (or lambda function) returns an object, it will pack the result into a single cell as a Data Table.

For example, you can use the ARRAY function to generate random numbers. The ARRAY function takes 3 arguments: the first two are the dimensions of the matrix, and the third is the value for the cells. If the value is a string that starts with "=", it will be treated as a formula. For example:

=ARRAY(10, 10, "=RANDBETWEEN(1, 100)")

Querying Data from a Data Table

Packing data into a cell allows you to avoid messy spreadsheets, as the data only takes up a single cell. A formula can return a matrix of variable size, but since it is in a single cell, it will not overwrite other cells in your spreadsheet. The data, however, is still fully accessible in your calculations.

You can reference the entire Data Table as cell A1, or you can select a subset by specifying a column and a row, as if it were a function.

In this case, it will return values at the intersections of the specified rows and columns.

Single Cell, Row or Column

A1(1, 5) - selects the cell at row 1 and column 5 A1(1, *) - selects the entire 1st row A1(*, 5) - selects the entire 5th column

Selecting Multiple Rows/Columns

A1([1,3], *) - selects rows 1 and 3 A1([5..10], *) - selects rows 5 through 10 A1([2..5], [4..6]) - selects rows 2 through 5 and columns 4 through 6

Referencing a Cell/Range within a Data Table

A1("A3") - selects cell A3 within the Data Table A1("A2:B5") - selects range A2:B5 within the Data Table

Using Cell References and Variables

A1(C5, *) - selects the row referenced in the spreadsheet by cell C5 A1(B1, B2) - selects the cell (row referenced in B1 and column in B2) A1(X, *) - selects the row referenced in the spreadsheet by variable X A1(X, Y) - selects the cell referenced by X and Y A1(*, [X..Y]) - selects all rows between X and Y

Filtering Rows

A1().WHERE($A=10) - selects all rows where column A is 10 A1().WHERE($C>5) - selects all rows where column C is greater than 5 A1(*, [1..3]).WHERE($C>5) - selects all rows where column C > 5, but returns columns 1–3 A1(*, [$A, $F]) - selects columns A and F

Column Names and Line Numbers

A1(0, *) - returns column names (if not defined, then column letters) A1(*, 0) - returns line numbers A1([0..10], 3) - returns line numbers and then rows 1–10 (only 3rd column) A1(*, [0,1,2]) - returns column names and columns 1 and 2

You can access columns either by specifying their index, letter, or column name. For example, the next three formulas will return identical data (assuming that column "A" is defined as "ID" and "B" as "STATE_NAME"):

A1(1, [1, 2]) A1(1, [$A, $B]) A1(1, [$ID, $STATE_NAME])

The same way of querying data can also be applied to a regular range, for example:

A1:B7(1, 5) - selects the cell at row 1, column 2 (B2) A1:B7().WHERE($A=4) - selects all rows where there is a 4 in column A A1:B7().WHERE($B>5) - selects all rows where the value in B is greater than 5 A1:B7([1..2], $B) - selects rows 1 and 2 and returns only column "B"

If you access columns by letters, "A" is the first column in the range. For example, if your range is "H1:K10", then column "A" in the formula refers to column "H" in the spreadsheet.

Alternative to VLOOKUP

Row filtering provides an alternative to VLOOKUP. It is more expressive and more flexible, giving you more options for querying your data. If column names are defined, you can use them in your formulas.

A1(*, $YEAR) - selects column Year A1().WHERE($YEAR=2012) - selects all rows where Year is 2012 A1().WHERE($GENDER="M") - selects all rows where Gender is M A1().WHERE($YEAR=2012 && $GENDER="M") - selects all rows where Year is 2012 and Gender is M A1().WHERE($YEAR=2012 || $YEAR="2014") - selects all rows where Year is 2012 or 2014

In fact, Data Tables replace a whole range of Excel functions (and combinations of them), such as:

Excel: VLOOKUP(2015, A1, 5) USolver: A1(*, 5).WHERE($YEAR=2015)

Excel: INDEX(MATCH(2015, A1, 0)) USolver: A1().WHERE($YEAR=2015)

Excel: INDEX(A1, 2, 4) USolver: A1(2, 4)

Excel: AVERAGEIFS(A1, A1("B2:B5"), ">70", A1("B2:B5"), ">90") USolver: AVERAGE(A1(*, $SALES).WHERE($SALES > 70 && $SALES < 90))

Compared to other spreadsheet functions that return only a single value, querying Data Tables can return a matrix, which you can expand into the spreadsheet or pack into a single cell, making it a Data Table again.