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.
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.