7. Editable formulas - relative references

Entering formulas into cells using relative or absolute cell references by row and column index

• Definition

  
  To permit entering formula into cell set <Cfg

  FormulaEditing

  ='1'/>.
  To restrict entering the formula to particular column, row or cell set its attribute FormulaCanEdit='0'.
  
• The formula can be entered if started by '

  =

  '. The formula prefix character can be changed by <Lang><Format FormulaPrefix='='/></Lang>.

• Relative references

  
  This example uses relative references by row index and column index. It is set by <Cfg> attribute

  FormulaRelative

  ='1'.
  In the formulas there can be addressed any the grid cell as column Index + row Index. The order can be changed by <Cfg> FormulaNames attribute.
  
  • The row and columns change their index whenever they or previous items are moved, deleted or added.
    All formulas (both absolute and relative) are updated accordingly after any Index change to refer still the same original rows and columns.
    
  • If relative formula is copied, it changes for the difference between indexes in old and new location. So the relative formula (without $) refers the new destination location.
    If absolute formula is copied, it never changes. So the absolute formula (with $) refers the original location.
    
  • If formula is moved (both absolute and relative), it changes for the difference between indexes in old and new location.
    But it does not change if all the source (referred) cells were moved too.
  • If formula references range(s), only the range bounds are absolutely defined and the range always contains rows and columns actually placed between the range bounds.
    

• Operators

  
  Default operators use standard C++/JavaScript syntax: +, -, *, /, ! (not), % (modulo), & (bit AND), | (bit OR), ^ (bit XOR), && (logical and), || (logical OR), <<, >> (bit shift), == (equals), != (not equals), <= (less or equal), >= (greater or equal), < (less), > (greater).
  There are added more operators: = (equals), <> (not equals), ?: (condition three arguments as "condition?result_true:result_false").
  Priority of operators is the same as in JavaScript and cannot be changed. Always you can use ( ).
  There are defined constants: pi (3.14), ln2 (ln(2)), ln10 (ln(10)), log2e (log2(e)), log10e (log10(e)), sqrt2 (sqrt(2)), sqrt1_2 (1/sqrt(2)).
  All the operators and constants are defined in <Lang><FormulaFunctions operators/><Lang>, it is possible to modify, add or delete the operators and constants.
  

• Functions

  
  There can be used also these aggregate functions:

  sum

  ,

  sumsq

  ,

  count

  ,

  counta

  ,

  countblank

  ,

  max

  ,

  min

  ,

  product

  .
  The functions accept as parameters value constants (e.g. 100), single cell reference (e.g. A1) or cell range reference (e.g. A1:B4).
  In the range the bounds are separated by colon '

  :

  '. The range separator can be changed by <Lang><Format FormulaRangeSeparator=':'/></Lang>.
  The functions accept more parameters separated by comma '

  ,

  '. The parameter separator can be changed by <Lang><Format FormulaValueSeparator=','/></Lang>.
  There are also defined standard JavaScript mathematical functions: abs(x), round(x), ceil(x), floor(x), exp(x), log(x), pow(x,y), sqrt(x), sin(x), cos(x), tan(x), asin(x), acos(x), atan(x,y).
  There are also defined date functions: date(year,month,day,hour,minute,second), date(date,format), time(hour,minute,second), time (time,format), now(), today(), year(date), month(date), day(date), weekday(date),weeknum(date), hour(date), minute(date), second(date).
  And one formatting function: text(value,format,type) to convert date or number to string.
  All the functions are defined in <Lang><FormulaFunctions/><Lang>, it is possible to rename, add or delete the functions.