ParamQuery grid and spreadsheet supports the commonly used Excel formulas mentioned in the table below.

In formulas, references to cells or range of cells are supported in this format.
  1. Absolute cell references e.g., $A$2, A$2, $A2, $A$1:$D$5, $A:$C, $5:$10 etc.
  2. Relative cell references e.g., A2, A1:D5, A:A, 6:8, etc.

If you find any required formula absent from the list, then you can either add it yourself as described below or let's know to consider them for addition in future versions.

Custom Excel formulas can be defined by

  1. extension of pq.formulas namespace
  2. Name of the formula should in CAPITAL letters
  3. Formulas receive range of cells as an array and a single cell as a single value as arguments

e.g.,

pq.formulas.SUMTREE = function( args ){
    //your custom logic here and return the final value.
}