Execute Excel-like Formulas on your Tables and Grids

If you do much reporting I'm sure you've encountered needing to sum up a row, or divide, or average some data in a grid, much like you would do in Excel. Its actually not that difficult to whip up a simple, dynamic solution with javascript. I'm using jQuery, but it would be equally as simple with Mootools or Prototype. To do this I'll build upon an earlier post on coloring tables and grids with CSS3 and rgba, so if you're using IE, tough luck on it looking pretty for now, but the functionality is there. Lets get started.

Calculated Fields

First lets take our grid from before and add a couple columns, one for Sum and one for Average. Also lets add some classes to each td: qtr-1, qtr-2, qtr-3, qtr-4, sum, avg. Now the columns on each row are identifiable. Your html should resemble the following (I've cut down the number of rows for this example, more rows are shown in the example):

<table cellpadding="0" cellspacing="0" class="stripeTable">
    <col class="col0">
    <col class="col1"> 
    <col class="col0">
    <col class="col1">
    <col class="col0">
    <col class="col1">
    <col class="col0">
            <th>Qtr 1</th>
            <th>Qtr 2</th>
            <th>Qtr 3</th>
            <th>Qtr 4</th>
        <tr class="bg0 stripeRow">
            <td class="year">1990</td>
            <td class="qtr-1">987</td>
            <td class="qtr-2">456</td>
            <td class="qtr-3">159</td>
            <td class="qtr-4">159</td>
            <td class="sum"></td>
            <td class="avg"></td>
        <tr class="bg1 stripeRow">
            <td class="year">1991</td>
            <td class="qtr-1">1234</td>
            <td class="qtr-2">2345</td>
            <td class="qtr-3">346</td>
            <td class="qtr-4">457</td>
            <td class="sum"></td>
            <td class="avg"></td>

Now lets get to the good stuff, how we calculate simple formulas. Remember the class names we assigned to each td? This will be used in a simple string search and replace, then execute the resulting string. Here's the function to do this:

function calculateFormula($row, formula){

    formula = formula.replace(/(\{[a-z0-9_\-]+\})/gi, function(m){
        return $('.' + m.replace(/\{|\}/g, ''), $row).text();

    var answer = eval(formula); 
    return (isNaN(answer))? 0 : answer;

The first parameter is a jQuery object of each row, since we're doing per-row calculations, but this could really be any context you need. The second parameter would be the formula. The syntax we're expecting is "{className} + 123". We'll do a regular expression to search for anything wrapped in {} and replace it with the contents of the html object with that class.

Now we're ready to call this puppy. On page load we're going to get all table rows inside of tbody, which I've conveniently given class names stripeRow. Then we'll assign the text() of our sum and avg columns with the formula to calculate each. Incase it doesn't jump out at you do realize sum must be calculated before average, since average uses sum.


    $('.stripeTable .stripeRow').each(function(i){

        $t = $(this);

        $('.sum', $t).text(calculateFormula($t, '{qtr-1}+{qtr-2}+{qtr-3}+{qtr-4}'));

        $('.avg', $t).text(calculateFormula($t, '{sum}/4').toFixed(2));



And that is all there is to it! Here is the Demo. Enjoy!