6. Editable formulas - absolute references

Entering formulas into cells using absolute cell references by row id and column name

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

• Absolute references

  
  This example uses absolute references by row id and column name. It is default behavior set by <Cfg> attribute

  FormulaRelative

  ='0'.
  In the formulas there can be addressed any the grid cell as row Name (or row id) + column SearchNames (or column Name). The order can be changed by <Cfg> FormulaNames attribute.
  The row and columns never change their Name and id by any manipulation, so formula refers always the same row and column regardless on their position change.
  If formula is copied, it never changes and always references the original rows and columns and not their copies.
  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.