How to calculate HTML table columns (using javascript or jquery) with a user defined equation

Question

I need some help with dynamically calculating an HTML table column using data from other columns and using a user-defined equation.

For example, if the user inputs the equation C1 + C2 * 0.5 + C3 * 0.8 into a input box the table would need to calculate the last column based on the data from the columns defined in the equation (C1 = column 1, C2 = column 2...).

My table data looks like this:

Student ID | Homework 1 | Homework 2 | Exam points | Final Grade
1            8.75         7.60         55.50         -
2            9.00         4.50         63.00         -
3            7.75         7.40         45.50         -

If the user typed in the equation C1 + C2 * 0.5 + C3 * 0.8 in the input the table should perform the operations and fill the column Final Grade based on that equation.

The result should look something like this.

Student ID | Homework 1 | Homework 2 | Exam points | Final Grade
1            8.75         7.60         55.50         56.95
2            9.00         4.50         63.00         61.65
3            7.75         7.40         45.50         47.85

The first row in final grade would be calcualted like this (8.75 + 7.60 * 0.5 + 55.50 * 0.8).

This is my body in HTML:

<div>
    <input id="equation">
</div>
<table>
    <tr>
        <th>Student ID</th>
        <th>Homework 1</th>
        <th>Homework 2</th>
        <th>Exam points</th>
        <th>Final grade</th>
    </tr>
    <tr>
        <td>1</td>
        <td>8.75</td>
        <td>7.60</td>
        <td>55.50</td>
        <td class="final-grade">-</td>
    </tr>
    <tr>
        <td>2</td>
        <td>9.00</td>
        <td>4.50</td>
        <td>63.00</td>
        <td class="final-grade">-</td>
    </tr>
    <tr>
        <td>3</td>
        <td>8.75</td>
        <td>7.60</td>
        <td>55.50</td>
        <td class="final-grade">-</td>
    </tr>
</table>

Any help would be greatly appreciated!

Answer 1

Here's an example that uses the evil eval (!).

• Works with any number of cells, uppercase or lowercase C identifier.
• If the subject cell is invalid the result will be "NaN".
• If the provided equation is invalid the result will be "-"

Basically it creates an object with references like {"c1":8.75, "c2":7.60, ...} , than it evals the input string replacing the c* occurrences with the respective object value.

function calcGrades() {

  const val = this.value.toLowerCase().trim();
  
  $("#grades tbody tr").each((i, TR) => {
    let res;
    const refs = $("td",TR).get().reduce((ob, TD, i) =>
        [ob["c"+i] = parseFloat(TD.textContent), ob][1], {});
        
    try { res = eval(val.replace(/c\d+/g, $1 => refs[$1])).toFixed(2) } 
    catch (err) { res = "-" }
    
    $(".final-grade",TR).text( res );
  });
}

$("#equation").on("input", calcGrades).trigger("input");

<input id="equation" type="text" value="C1 + c2 * 0.5 + C3 * 0.8">
<table id="grades">
  <thead>
    <tr>
        <th>ID</th><th>Homework 1</th><th>Homework 2</th><th>Exam pts</th><th>Final</th>
    </tr>
  </thead>
  <tbody>
    <tr><td>1</td><td>8.75</td><td>7.60</td><td>55.50</td><td class="final-grade">-</td></tr>
    <tr><td>2</td><td>9.00</td><td>4.50</td><td>63.00</td><td class="final-grade">-</td></tr>
    <tr><td>3</td><td>7.75</td><td>7.40</td><td>45.50</td><td class="final-grade">-</td></tr>
    <tr><td>4</td><td>0</td><td>0.0</td><td>0</td><td class="final-grade">-</td></tr>
    <tr><td>4</td><td>foo</td><td>bar</td><td>baz</td><td class="final-grade">-</td></tr>
  </tbody>
</table>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

Answer 2

Well, the way you are doing is very fragile, the user would input a single different character in the input and then all calcuation can fail.

But besides that, there's a way of doing that keeping the way you want (I don't recommend), but let's go to the explanation:

1. Add classes to your HTML table, in a way that you can identify each row and column.
2. Create a function that get the values from each row (using a loop) and another function that you can call to calculate those values.
3. Inside the function that calculates is where comes the solution and the problem at the same time. For now, it works only with 3 columns and considering that the user knows that only c1, c2 or c3 are valid column names.
4. With the equation value, you replace the column names with the column values. Then you check if there's some other character remaining, if yes, then the equation is wrong and a message or an error should be displayed.
5. If no other characters remain and just numbers and operation symbols are in the equation, then we use the BAD IDEA eval() method, which evaluates the equation as it is.
6. I added the eval function inside a try...catch block to let it a little more secure (Please, read about why eval is not a good solution here: LINK)
7. Well, if the equation was typed correct and no malicious code was introduced by eval, then you will have the final value to return.

Below code as example:

let btn_calc = $("#btn_calc");
let equation = $("#equation");


btn_calc.on("click", calculate);

function calculate(){
  let rows = $(".values");
  for (var i = 0; i < rows.length; i++){
    let singleRow = $(rows[i]);
    let grade = singleRow.find(".final-grade");
    let n1 = singleRow.find(".c1").text(); 
    let n2 = singleRow.find(".c2").text();
    let n3 = singleRow.find(".c3").text();
    let total = returnValue(n1,n2,n3);
    if (total != false){
      grade.text(total)    
    } else {
      return;
    }
  }
}

function returnValue(n1,n2,n3){
  let calc = equation.val().trim();
  let result = 0;
  debugger;
  if (calc != null && calc != ""){
     calc = calc.replace(/c1/g,n1).replace(/c2/g,n2).replace(/c3/g,n3)

     var regx = new RegExp("[a-z]", "gi");
     debugger;
     if (calc.match(regx)){
      console.log(calc);
      alert("Your equation is invalid!");
      return false;
     }

     try{
      result = eval(calc);
      result.toFixed(2);
     } catch (ex){
      alert("Error in your equation! " + ex);
     }
  }
  return result;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div>
  <input type="text" id="equation"/> (Valid Column names: c1, c2, c3)
  <br>
  <input type="button" value="Calculate" id="btn_calc"/>
</div>
<table>
  <tr>
        <th>Student ID</th>
        <th >Homework 1</th>
        <th >Homework 2</th>
        <th>Exam points</th>
        <th>Final grade</th>
  </tr>
  <tr class="values">
        <td>1</td>                              
        <td class="c1">8.75</td>
        <td class="c2">7.60</td>
        <td class="c3">55.50</td>
        <td class="final-grade">-</td>
    </tr>
    <tr class="values">
        <td>2</td>
        <td class="c1">9.00</td>
        <td class="c2">4.50</td>
        <td class="c3">63.00</td>
        <td class="final-grade">-</td>
    </tr>
    <tr class="values">
        <td>3</td>
        <td class="c1">8.75</td>
        <td class="c2">7.60</td>
        <td class="c3">55.50</td>
        <td class="final-grade">-</td>
</tr>

You're lucky that I had some free time to code this for you... I should send the bill hahaha

Answer 3

As long as your html structure stays simple and you are okay with using eval (which is actually not recommended), defining the following JavaScript code and invoking it will do the trick for you. You will need to ensure that c1...c3 have valid values in them.

    function calcTotals() {
        const c1 = 'parseFloat(fg.previousElementSibling.previousElementSibling.previousElementSibling.textContent)'
        const c2 = 'parseFloat(fg.previousElementSibling.previousElementSibling.textContent)'
        const c3 = 'parseFloat(fg.previousElementSibling.textContent)'
        const equation = document.getElementById('equation').value
        try {
            const fg = document.querySelector('td.final-grade')
            eval(equation.replace('c1',c1)
                         .replace('c2',c2)
                         .replace('c3',c3))
        } catch(e) {
            alert('Please enter a valid equation')
        }
        document.querySelectorAll('td.final-grade').forEach(fg => {
            fg.textContent = eval(equation.replace('c1',c1)
                                          .replace('c2',c2)
                                          .replace('c3',c3))
        })
    }