Errors and volatile functions
Two things come up in every model sooner or later: a formula that can’t produce a value, and a formula whose value changes on its own. This page covers how Ixion represents error values and how they travel through a model, what makes a formula cell volatile, and what you get when you read a formula cell before it has been calculated.
We’ll reuse the set_formula() and calculate() helpers from the
earlier pages, with the modified cells as a parameter of calculate()
this time:
void set_formula(
ixion::model_context& cxt, const ixion::formula_name_resolver& resolver,
const ixion::abs_address_t& pos, std::string_view formula)
{
ixion::formula_tokens_t tokens = ixion::parse_formula_string(cxt, pos, resolver, formula);
cxt.set_formula_cell(pos, std::move(tokens));
}
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);
}
And one more helper that prints the result of a formula cell along with its type:
void print_result(
const ixion::model_context& cxt, const char* name, const ixion::abs_address_t& pos)
{
ixion::formula_result result = cxt.get_formula_result(pos);
std::cout << name << ": " << result.str(cxt) << " (" << result.get_type() << ")" << std::endl;
}
Error values
An error is one of the things a formula_result can
hold, next to a number, a boolean, a string or a matrix. The kinds of
error are the values of formula_error_t, and each has
the name you’d see in a spreadsheet cell:
Value |
Name |
Meaning |
|---|---|---|
|
|
A referenced result is not available. This is also what the cells of a circular reference end up with. |
|
|
Division by zero. |
|
|
The expression could not be evaluated. |
|
|
A function or named expression is unknown. |
|
|
Two ranges were expected to intersect but don’t. |
|
|
A value of the wrong type was used, such as a string where a number was needed. |
|
|
No value is available, as produced by |
Let’s produce one. C1 divides A1 by B1, which is zero, D1 adds one to C1, and E1 asks whether C1 is an error:
ixion::model_context cxt;
cxt.append_sheet("Sheet1");
auto resolver = ixion::formula_name_resolver::get(
ixion::formula_name_resolver_t::excel_a1, &cxt);
ixion::abs_address_t Sheet1_A1{0, 0, 0};
ixion::abs_address_t Sheet1_B1{0, 0, 1};
ixion::abs_address_t Sheet1_C1{0, 0, 2};
ixion::abs_address_t Sheet1_D1{0, 0, 3};
ixion::abs_address_t Sheet1_E1{0, 0, 4};
cxt.set_numeric_cell(Sheet1_A1, 10.0);
cxt.set_numeric_cell(Sheet1_B1, 0.0);
set_formula(cxt, *resolver, Sheet1_C1, "A1/B1");
set_formula(cxt, *resolver, Sheet1_D1, "C1+1");
set_formula(cxt, *resolver, Sheet1_E1, "ISERROR(C1)");
calculate(cxt, {}, {Sheet1_C1, Sheet1_D1, Sheet1_E1});
print_result(cxt, "C1", Sheet1_C1);
print_result(cxt, "D1", Sheet1_D1);
print_result(cxt, "E1", Sheet1_E1);
C1: #DIV/0! (error)
D1: #DIV/0! (error)
E1: true (boolean)
C1 gets the division error, and D1 gets the same error, because an error
used as an operand becomes the result of the operation. That’s how an
error spreads to everything that depends on the cell, until a formula
handles it explicitly, as ISERROR() does in E1. An error is a regular
result in every other respect: it’s cached in the cell, it takes part in
dependency tracking like any value, and calculation carries on with the
rest of the model.
To find out which error a cell holds, check the type of its result and ask
for the error value. get_formula_error_name() gives you
the display name, and to_formula_error_type() goes the
other way:
ixion::formula_result C1_result = cxt.get_formula_result(Sheet1_C1);
if (C1_result.get_type() == ixion::formula_result::result_type::error)
{
ixion::formula_error_t error = C1_result.get_error();
std::cout << "C1 error: " << ixion::get_formula_error_name(error) << std::endl;
std::cout << "C1 is division by zero: "
<< (error == ixion::formula_error_t::division_by_zero) << std::endl;
}
C1 error: #DIV/0!
C1 is division by zero: true
The typed accessors can’t return an error, so asking for the numeric value
of a cell in error throws a formula_error instead,
carrying the same error value:
try
{
double value = cxt.get_numeric_value(Sheet1_C1);
std::cout << "C1 value: " << value << std::endl;
}
catch (const ixion::formula_error& e)
{
std::cout << "C1 cannot be read as a number: " << e.what() << std::endl;
}
C1 cannot be read as a number: #DIV/0!
If you don’t know what type of value to expect from a cell, go through
get_formula_result() and check the type
first, as print_result() does.
Errors clear the same way they appear. Once B1 holds a usable value, the next calculation replaces the errors downstream:
cxt.set_numeric_cell(Sheet1_B1, 4.0);
calculate(cxt, {Sheet1_B1}, {});
print_result(cxt, "C1", Sheet1_C1);
print_result(cxt, "D1", Sheet1_D1);
print_result(cxt, "E1", Sheet1_E1);
C1: 2.5 (value)
D1: 3.5 (value)
E1: false (boolean)
A circular reference is the one error the calculation itself produces.
When formula cells depend on each other in a cycle, none of them can be
calculated first, so all of them get #REF!, and the cells outside the
cycle are calculated as usual:
ixion::abs_address_t Sheet1_A3{0, 2, 0};
ixion::abs_address_t Sheet1_B3{0, 2, 1};
ixion::abs_address_t Sheet1_C3{0, 2, 2};
set_formula(cxt, *resolver, Sheet1_A3, "B3+1");
set_formula(cxt, *resolver, Sheet1_B3, "A3+1");
set_formula(cxt, *resolver, Sheet1_C3, "A1*2");
calculate(cxt, {}, {Sheet1_A3, Sheet1_B3, Sheet1_C3});
print_result(cxt, "A3", Sheet1_A3);
print_result(cxt, "B3", Sheet1_B3);
print_result(cxt, "C3", Sheet1_C3);
A3: #REF! (error)
B3: #REF! (error)
C3: 20 (value)
Volatile functions
Most formula cells only need re-calculating when something they reference
has changed. A formula that uses NOW(), TODAY() or RAND() is
different: its value changes with no change to any cell. Such a function
is volatile, and so is any formula cell that uses one.
Ixion handles this in the dirty query. A volatile formula cell is
reported dirty on every call to
query_and_sort_dirty_cells(), whether or not anything
was modified, and so are the cells that depend on it:
ixion::abs_address_t Sheet1_A5{0, 4, 0};
ixion::abs_address_t Sheet1_B5{0, 4, 1};
set_formula(cxt, *resolver, Sheet1_A5, "RAND()");
set_formula(cxt, *resolver, Sheet1_B5, "A5*100");
calculate(cxt, {}, {Sheet1_A5, Sheet1_B5});
double A5_first = cxt.get_numeric_value(Sheet1_A5);
// Nothing has been modified, yet the dirty query still returns cells.
std::vector<ixion::abs_range_t> sorted = ixion::query_and_sort_dirty_cells(cxt, {}, nullptr);
std::cout << "dirty cells with nothing modified: " << sorted.size() << std::endl;
ixion::calculate_sorted_cells(cxt, sorted, 0);
std::cout << "A5 changed: " << (cxt.get_numeric_value(Sheet1_A5) != A5_first) << std::endl;
double A5_value = cxt.get_numeric_value(Sheet1_A5);
double B5_value = cxt.get_numeric_value(Sheet1_B5);
std::cout << "B5 follows A5: " << (B5_value == A5_value * 100) << std::endl;
dirty cells with nothing modified: 2
A5 changed: true
B5 follows A5: true
The two dirty cells are A5 and its dependent B5. Volatility is recorded when the cell is set, at the same time as its references, and from then on the cell comes back dirty on every calculation.
Results that aren’t there yet
A formula cell you’ve just inserted has no result until it’s calculated,
and neither has a cell whose calculation is still in progress on another
thread. What happens when you read such a cell depends on the model’s
wait policy, a formula_result_wait_policy_t value you
can inspect with
get_formula_result_wait_policy().
The model context manages the policy itself. While
calculate_sorted_cells() is running, the policy is to
block: a formula cell that needs a result still being computed on another
thread waits for it. Outside of a calculation, the policy is to throw,
and reading a cell without a result throws a
formula_error with the #REF! error value:
ixion::abs_address_t Sheet1_A7{0, 6, 0};
set_formula(cxt, *resolver, Sheet1_A7, "A1+1");
try
{
double value = cxt.get_numeric_value(Sheet1_A7);
std::cout << "A7 value: " << value << std::endl;
}
catch (const ixion::formula_error& e)
{
std::cout << "A7 not calculated yet: " << e.what() << std::endl;
}
calculate(cxt, {}, {Sheet1_A7});
std::cout << "A7 value: " << cxt.get_numeric_value(Sheet1_A7) << std::endl;
A7 not calculated yet: #REF!
A7 value: 11
The complete source code of this example is available here.