Formula groups

A formula group is a rectangular block of formula cells that share one set of formula tokens. This is how Ixion stores an array formula: an expression entered once over a range, like {=A1:B2*10} in a spreadsheet application, whose result is a matrix spread over the cells of that range. Each cell of the group is still an ordinary formula cell from the outside. You read its result the same way you read any other formula cell, and it reports its own value, not the whole matrix. The grouping is about storage and calculation: one copy of the tokens, one interpretation, one result shared by all the cells.

Note

Today a formula group always stands for an array formula. Grouping adjacent cells that merely happen to have the same formula, so that each cell is calculated at its own position, is not supported yet.

Creating a group

Let’s put some values in A1:B2 and create a group over D1:E2 that multiplies them by 10. We’ll also use a small dump() helper that prints the sheet in verbose mode:

void dump(const ixion::model_context& cxt)
{
    cxt.dump_sheet(std::cout, 0, ixion::sheet_dump_mode_t::verbose);
    std::cout << std::endl;
}
std::cout << std::boolalpha;

ixion::model_context cxt;
cxt.append_sheet("Sheet1");

auto resolver = ixion::formula_name_resolver::get(
    ixion::formula_name_resolver_t::excel_a1, &cxt);

cxt.set_cell_values(0, {
    {1.0, 2.0}, // A1:B1
    {3.0, 4.0}, // A2:B2
});

The formula is parsed at the top-left cell of the group, D1, and set_grouped_formula_cells() takes the range of the group along with the tokens:

ixion::abs_range_t D1E2{{0, 0, 3}, {0, 1, 4}};

ixion::formula_tokens_t tokens = ixion::parse_formula_string(
    cxt, D1E2.first, *resolver, "A1:B2*10");
cxt.set_grouped_formula_cells(D1E2, std::move(tokens));

dump(cxt);
+---+-------+-------+---+------------------------+------------------------+
|   | A     | B     | C | D                      | E                      |
+---+-------+-------+---+------------------------+------------------------+
| 1 | 1 [v] | 2 [v] |   | {A1:B2*10}@0/2 (#RES!) | {A1:B2*10}@0/2 (#RES!) |
| 2 | 3 [v] | 4 [v] |   | {A1:B2*10}@1/2 (#RES!) | {A1:B2*10}@1/2 (#RES!) |
+---+-------+-------+---+------------------------+------------------------+

All four cells show the same formula. The braces around it mark a grouped cell, and the @0/2 part gives the cell’s row offset within the group and the number of rows in the group. The formula is printed relative to the top-left cell of the group for every member, which is why D2 shows A1:B2 rather than A2:B3. The #RES! marker means the cell has no result yet.

There is a second overload that takes a cached result along with the tokens, for when you load a document that already stores the results of its array formulas. Just note that the result has to be a matrix with the exact dimensions of the group, or the call throws.

Calculating a group

A group is tracked and calculated through its top-left cell. The dependency tracker holds the whole group under that one cell, and the calculation of that one cell fills in the results of all the members. We’ll use a calculate() helper like the one from the Named expressions and tables page, with the modified cells added as a parameter since we’ll need them further down:

void calculate(
    ixion::model_context& cxt, const ixion::abs_range_set_t& modified_cells,
    const ixion::abs_range_set_t& new_formula_cells)
{
    std::vector<ixion::abs_range_t> sorted =
        ixion::query_and_sort_dirty_cells(cxt, modified_cells, &new_formula_cells);

    ixion::calculate_sorted_cells(cxt, sorted, 0);
}
calculate(cxt, {}, {D1E2});

dump(cxt);
+---+-------+-------+---+---------------------+---------------------+
|   | A     | B     | C | D                   | E                   |
+---+-------+-------+---+---------------------+---------------------+
| 1 | 1 [v] | 2 [v] |   | {A1:B2*10}@0/2 (10) | {A1:B2*10}@0/2 (20) |
| 2 | 3 [v] | 4 [v] |   | {A1:B2*10}@1/2 (30) | {A1:B2*10}@1/2 (40) |
+---+-------+-------+---+---------------------+---------------------+

Passing the whole range of the group as the set of new formula cells works as well as passing just the top-left cell, since the group is calculated as a unit either way.

Because the group is one listener as far as the tracker is concerned, a change to any cell it references marks the whole group dirty. Changing A1 re-calculates all of D1:E2, even though only D1 ends up with a different value:

ixion::abs_address_t Sheet1_A1{0, 0, 0};
cxt.set_numeric_cell(Sheet1_A1, 5.0);
calculate(cxt, {Sheet1_A1}, {});

dump(cxt);
+---+-------+-------+---+---------------------+---------------------+
|   | A     | B     | C | D                   | E                   |
+---+-------+-------+---+---------------------+---------------------+
| 1 | 5 [v] | 2 [v] |   | {A1:B2*10}@0/2 (50) | {A1:B2*10}@0/2 (20) |
| 2 | 3 [v] | 4 [v] |   | {A1:B2*10}@1/2 (30) | {A1:B2*10}@1/2 (40) |
+---+-------+-------+---+---------------------+---------------------+

Inspecting a group

Given a formula cell, get_group_properties() returns a formula_group_t, which tells you whether the cell belongs to a group, how big the group is, and an identity value that is the same for all the cells of one group. It can be streamed to an output stream, which prints all of that in one go. get_parent_position() maps a cell’s position to that of the top-left cell of its group; for a cell that is not grouped it returns the position you passed in. Here we go through all four cells and compare each one against D1:

const ixion::formula_cell* D1 = cxt.get_formula_cell(D1E2.first);

struct named_cell
{
    const char* name;
    ixion::abs_address_t pos;
};

named_cell cells[] = {
    {"D1", {0, 0, 3}},
    {"D2", {0, 1, 3}},
    {"E1", {0, 0, 4}},
    {"E2", {0, 1, 4}},
};

for (const named_cell& c : cells)
{
    const ixion::formula_cell* cell = cxt.get_formula_cell(c.pos);
    ixion::abs_address_t parent = cell->get_parent_position(c.pos);

    std::cout << c.name << ": " << cell->get_group_properties() << std::endl;
    std::cout << "  parent is D1: " << (parent == D1E2.first) << std::endl;
    std::cout << "  same tokens as D1: " << (cell->get_tokens() == D1->get_tokens())
        << std::endl;
}
D1: (formula_group_t: grouped=true; rows=2; columns=2; identity=0x5d7127db7ba0)
  parent is D1: true
  same tokens as D1: true
D2: (formula_group_t: grouped=true; rows=2; columns=2; identity=0x5d7127db7ba0)
  parent is D1: true
  same tokens as D1: true
E1: (formula_group_t: grouped=true; rows=2; columns=2; identity=0x5d7127db7ba0)
  parent is D1: true
  same tokens as D1: true
E2: (formula_group_t: grouped=true; rows=2; columns=2; identity=0x5d7127db7ba0)
  parent is D1: true
  same tokens as D1: true

All four cells report the same identity, which makes sense since they all belong to the same group. The identity values are guaranteed to be different between cells belonging to different groups in a given snapshot in time, though different groups may reuse the same identity value in different snapshots.

The last line of each cell’s output confirms that the members share one token store, which is the whole point of the grouping. Here, get_tokens() returns a smart pointer to the formula_tokens_store that stores cell’s tokens, and comparing two of them compares their memory addresses. So true here means the two cells point at the very same stored object.

Reading the results

A grouped cell has two views of its result. get_raw_result_cache() returns the result as stored, which for a grouped cell is the matrix shared by the whole group. get_result_cache() returns only the element of that matrix at the cell’s position within the group, as a single value. Note that model_context never reveals the raw result cache directly, so its cell value accessors, such as get_numeric_value() and get_formula_result(), always report individual cell results:

ixion::abs_address_t Sheet1_E2{0, 1, 4};
const ixion::formula_cell* E2 = cxt.get_formula_cell(Sheet1_E2);

const ixion::formula_result& raw = E2->get_raw_result_cache(
    ixion::formula_result_wait_policy_t::throw_exception);
std::cout << "raw result: " << raw.str(cxt) << std::endl;

ixion::formula_result single = E2->get_result_cache(
    ixion::formula_result_wait_policy_t::throw_exception);
std::cout << "E2 result: " << single.str(cxt) << std::endl;
std::cout << "E2 value: " << cxt.get_numeric_value(Sheet1_E2) << std::endl;
raw result: {10,20;30,40}
E2 result: 40
E2 value: 40

Unless you specifically want the matrix, the single-value view is the one to use, and it’s the one that makes a grouped cell indistinguishable from a plain formula cell.

How a group is stored

The behavior above follows from how a group is put together, and it’s worth knowing the mechanism so that the rules don’t look arbitrary.

Each member of a group holds a pointer to a shared token store and a pointer to a shared result slot, along with its own row and column offset within the group. The member at offset zero in both directions is the parent, and the parent is the only member that ever interprets the tokens. It does so once, at its own position, and stores the resulting matrix in the shared slot. The other members never calculate anything; when their value is requested, they look up the element of that matrix at their own offset. This is why tracking and calculating go through the top-left cell, why the formula prints relative to the top-left cell for every member, and why a cached result supplied up front has to be a matrix of the group’s size.

On the dependency side, the tracker records the range of the whole group, D1:E2 in our example, as the listener of the referenced range A1:B2. A single formula cell listening on a range is the common case; here it is a range listening on a range. The tracker holds a single entry for the group, and it can only mark that entry dirty as a whole. That’s why the edit to A1 above re-calculated all four cells.

The four members D1, E1, D2 and E2 as a 2x2 block with their row and column offsets, bracketed on the left toward one shared token store and on the right toward one shared result slot; D1 is highlighted as the parent, with its own arrow writing the result.

What each member of the group holds. Only the parent, D1, writes the result slot; the others read their element from it.

Matrix results without a group

A group is not the only way for a matrix to show up as a formula result. A single, ungrouped formula cell can produce a matrix too, for instance from an inline array:

ixion::abs_address_t Sheet1_G1{0, 0, 6};
tokens = ixion::parse_formula_string(cxt, Sheet1_G1, *resolver, "{1,2;3,4}*10");
cxt.set_formula_cell(Sheet1_G1, std::move(tokens));
calculate(cxt, {}, {Sheet1_G1});

dump(cxt);

const ixion::formula_cell* G1 = cxt.get_formula_cell(Sheet1_G1);
std::cout << "G1 grouped: " << G1->get_group_properties().grouped << std::endl;
+---+-------+-------+---+---------------------+---------------------+---+------------------------------+
|   | A     | B     | C | D                   | E                   | F | G                            |
+---+-------+-------+---+---------------------+---------------------+---+------------------------------+
| 1 | 5 [v] | 2 [v] |   | {A1:B2*10}@0/2 (50) | {A1:B2*10}@0/2 (20) |   | {1,2;3,4}*10 ({10,20;30,40}) |
| 2 | 3 [v] | 4 [v] |   | {A1:B2*10}@1/2 (30) | {A1:B2*10}@1/2 (40) |   |                              |
+---+-------+-------+---+---------------------+---------------------+---+------------------------------+
G1 grouped: false

G1 holds the whole matrix as its result, with no braces around the formula in the dump and no group behind it. Whether a matrix result gets spread over a range of cells or is contained within a single cell is a decision left to the application that builds the model, made by choosing between calling set_grouped_formula_cells() and set_formula_cell(), respectively.

Note

In practice there is rarely a reason to keep a matrix in a single cell like this, and spreadsheet applications generally don’t. It’s shown in this example mainly to demonstrate that it is doable.

The complete source code of this example is available here.