OAK's expression pruning feature offers control over the expressions it can remove from formulas.
Being able to activate the functions separately means that a disagreeable formula such as
=IF(INDIRECT(...),OFFSET(...),VLOOKUP(...))
can be pruned in stages, so that it's possible to see what the awkward terms actually evaluate to and what underlying calculation the IF is performing.
The rules OAK follows when prunes these functions are:
BEFORE PRUNING |
AFTER PRUNING |
COMMENTS |
CHOOSE(K,item1,item2, ..., itemN) |
itemK |
|
COUNTIF(range,testvalue) |
COUNT(subrange) |
subrange is a multi area range consisting of those cells in range which satisfy test value. |
HLOOKUP |
a reference identifying the cell that containing the value that would be returned by the HLOOKUP given its current inputs |
See note 2 |
IF(test,truepart,falsepart) |
truepart OR falsepart |
Depending on whether test is TRUE or FALSE |
INDEX(range,x,y) |
subrange |
subrange is a portion of range, typically a single cell, but possibly a range one row high or one column wide (if x or y are zero) |
INDIRECT(...) |
a reference identifying the range that would be returned by the function given its current inputs |
|
LOOKUP(...) |
a reference identifying the range that would be returned by the function given its current inputs |
|
MAX(range) |
MAX(cell) |
MAX is retained to indicate what was there before, but has no effect as it is given just one parameter. |
MIN(range) |
MIN(cell) |
MIN is retained to indicate what was there before, but has no effect as it is given just one parameter. |
OFFSET(...) |
a reference identifying the range that would be returned by the function given its current inputs |
|
SUMIF(conditionrange,testvalue,valuerange) |
SUM(subrange) |
subrange is a multi area range consisting of those cells in valuerange in which the equivalent cells in conditionrange satisfy testvalue |
VLOOKUP(...) |
a reference identifying the cell that containing the value that would be returned by the VLOOKUP given its current inputs |
See note 2 |
NOTES
| 1 | Expressions are always relative after pruning, regardless of what combination of relative and absolute formulas went to make up the original expression. |
| 2 | The wording used to describe the results of pruning HLOOKUP and VLOOKUP is intentionally different from the wording used to describe the results of pruning INDEX, INDIRECT, and LOOKUP. This is because INDEX, INDIRECT and LOOKUP return references, which are dereferenced or not as appropriate in the context. For example, =ROW(INDEX(...)) returns a useful result, because Microsoft has engineered Excel so that INDEX returns a reference; it does not turn that reference into a value until required to to evaluate an expression. But HLOOKUP and VLOOKUP return values, not references; Excel will not let you even enter a formula such as =ROW(HLOOKUP(...)). Nevertheless, OAK does compute the reference that would be returned by HLOOKUP and VLOOKUP if they behaved like INDEX, INDIRECT and LOOKUP and it is that reference that is put in a pruned expression. |