Several NExS functions such as @CELLREF and @RANGEREF return a result that is itself a cell reference or range reference. This is a powerful facility, but it must be used with caution because NExS can not take these indirect references into account when determining the order of recalculation. The same caution applies to constraint expressions used in conditional statistical functions. As a rule, cells that are indirectly referenced by a function are not automatically recalculated. NExS provides a special construct to force a recalculation, referred to as an explicit dependency.
NExS does not recalculate the spreadsheet unless explicit dependencies have been changed, so you may need to force recalculation if you change the value of a cell that is referenced only indirectly through a function.
For example, suppose you want to count the numeric values
in the range C3..J100 that fall within the limits
specified in cells A1 and A2. The NExS formula to
compute this is
@CCOUNT(C3..J100,#>A1 && #<A2)
This formula will correctly count the numeric values in
the range C3..J100. However, if you change the value in
A1, NExS will not automatically recalculate the result,
because A1 is referenced only indirectly through the
constraint expression.
Using explicit dependencies to circumvent the limitation
described above, if you entered the formula below in the
form
@CCOUNT(C3..J100,#>A1 && #<A2)\A1\A2
NExS would take into account the dependencies on A1 and
A2 and update the spreadsheet just as you expect.
Another approach is to construct the condition string
with an expression that references the cells directly.
For example,
@CCOUNT(C3..J100, @STRCAT("#>",A1,"&&#<",A2))
In this example, A1 and A2 are directly referenced and
thus will properly trigger recalculation.