Calculated columns (cell formula)

Here is a simple calculation patch which adds 'formula' property to the grid cells. The formula should be a string, use normal javascript syntax and can include any global or Math object functions. The function column(i) is used to refer to any cell in the current row.


'column(1) + column(2)'
'myFunction(column(1), column(2), column(3))'

To assign cell formula use setCellFormula(formulaText, colIndex) -

grid.setCellFormula('column(1)+column(2)', 3); // col3 = col1+col2

Math functions reference -

The formula result is applied to the cell value and can be formatted as usual with AW.Formats.... classes.

Here is the full example which includes the patch and sample usage -

    <script src="../../runtime/lib/aw.js"></script>
    <link href="../../runtime/styles/xp/aw.css" rel="stylesheet"></link>

// ---------------------------------
// begin 'calculated columns' patch

var events = {

    onCellFormulaChanged: function(formula, col, row){

        function Lib(){};
        Lib.prototype = Math; // inherit all Math functions

        var param = new Lib;

        param.column = function(i){ // get value from column(i) in the same row
            return param.grid.getCellValue(i, param.r);

        var calc; // build js function from string formula

        try {
            calc = new Function('p', 'with(p){return (' + formula + ')}');
            calc = function(){return '#ERR: ' + err.description }; // syntax error

        function calculated(c, r){ // pass col, row indices to calc function
            try {
                param.grid = this;
                param.c = c;
                param.r = r;
                return calc(param);
                return '#VALUE: ' + err.description; // runtime error

        function formatted(c, r){
            var v = this.getCellValue(c, r);
            var f = this.getCellFormat(c, r);
            return f ? f.valueToText(v) : v;

        this.setCellValue(calculated, col, row);
        this.setCellText(formatted, col, row);
        this.setCellEditable(false, col, row);

        // dependencies

        if (!this._notify){
            this._notify = {};

        var i, src;
        var a = formula.match(/column\(.+?\)/g);

        for(i=0; i<a.length;i++){

            src = a[i].replace('column(', '').replace(')', '');

            if (!this._notify[src]){
                this._notify[src] = {};

            this._notify[src][col] = true;

    onCellValueChanged: function(value, col, row){
        if (this._notify && this._notify[col]){
            for (var i in this._notify[col]){
                this.raiseEvent('onCellValueChanged', '', i, row);

    onCellValidated: function(text, col, row){

        var f = this.getCellFormat(col, row);
        var v = f ? f.textToValue(text) : text;

        function formatted(c, r){
            var v = this.getCellValue(c, r);
            var f = this.getCellFormat(c, r);
            return f ? f.valueToText(v) : v;

        this.setCellValue(v, col, row);
        this.setCellText(formatted, col, row);

    new AW.UI.Grid;
    var obj = AW.UI.Grid.prototype;
    obj.defineCellProperty('formula', '');
    obj.setController('formula', events);

// end of 'calculated columns' patch
// ---------------------------------
    .aw-column-1, .aw-column-2, .aw-column-3 {
        text-align: right;
        width: 80px;

var myHeaders = ['name', 'price', 'amount', 'total'];

var myCells = [
    ['item1', 22.5, 10],
    ['item2', 10,  123],
    ['item3', 155.50, 1]

var str = new AW.Formats.String;
var num = new AW.Formats.Number;

var obj = new AW.UI.Grid;
obj.setCellFormat([str, num, num, num]);


obj.setCellFormula('column(1)*column(2)', 3); // calc total in column-3


Alex (ActiveWidgets)
October 10,
Note, that it is also possible to use custom functions in formulas -

function myFunction(a, b){
    return a*0.1 + b;

obj.setCellFormula('myFunction(column(1), column(2))', 3); // calc in column-3
Alex (ActiveWidgets)
October 10,

Will this patch be part of main library in 2.5 release.

Girish Khemani, Fidelity India
October 10,
I don't know yet. Do you think it is a good idea to include this 'cell formula' code into the standard package? Or I should leave it separately as an add-on?
Alex (ActiveWidgets)
October 12,
Hi Alex,

The size of the library increases every release, which is natural for any software.
I had looked about some time back on how I could reduce the size of aw.js by removing features I don't use.
One example is that I can remove the lines at the end of aw.js beginning with AW.HTTP.Request=, AW.CSV.Table= and AW.XML.Table= as I use another library to handle my XML HTTP requests and don't use csv and xml tables.
Right now I use the entire aw.js, but in future, if I need to crunch the size of the javascript in my application, I was thinking of the above and more removals in other .js files I use.

If this cell formula code is included, would it be possible to include it in a way in which it can be removed easily if we are not using the feature ?

Ankur Motreja
October 12,
Maybe you could include an "AW Lite" for basic functionality... and then "AW Standard" which includes these functions. I feel that when you have a grid like this it's important to offer as many functions as possible.

October 18,

Is it possible to set formulas in a header row instead of a cell row?


October 23,
how about i want to calculate the row and put it into my footer? please help me..alex
March 4,

This topic is archived.

See also:

Back to support forum