Excel formulas

ParamQuery grid and spreadsheet provides around 90 inbuilt Excel formulas which are mentioned in the table below. These formulas are most commonly used in day to day spreadsheets.

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.

Custom Excel formulas

If you find any required formula missing from the list below, then you can either custom add it yourself the procedure of which is described below or inform us 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 must be in CAPITAL letters
  3. Formulas receive range of cells as an array in the arguments e.g. "A1:D4" is passed as an array of length 16 having values lying in the "A1:D4" range
  4. Formulas receive single cell as a single value in the arguments

e.g.,

//Define custom Excel formula to display summary of children nodes in parent nodes of treegrid.
//this formula receives single argument.
pq.formulas.SUMTREE = function (ri) {
    var rd = grid.pageData()[ri],
        sum = 0,
        Tree = grid.Tree();

    Tree.eachChild(rd, function (node) {
        if (node != rd && !Tree.isFolder(node))
            sum += node.size * 1 || 0;
    })
    return sum;
}
And then it can be used in treegrid as Excel formula. 
SUMTREE(ROW()-1)
[ Check here for a working example ]

Inbuilt formulas