Using Formula with Calx

Using Formula with Calx2013-10-27T11:31:24+00:00

Yes, Calx will calculate the value based on given formula, just like Excel does. Calx able to parse from simple to complex formula, because it is uses javascript parser generated using jison grammar file in it’s core, a port of bison for javascript.

Thanks to Zach Carter for creating jison parser generator https://github.com/zaach/jison.

Defining the formula

Calx require formula to be defined to do the calculation, You can define the formula inside the data-formula attribute.

the code above will tell Calx to add value of the element with id=A3 with the value of the element with id=B3 and display the result inside the element with id=C3, that’s it, simple, right?

All input type=”text” with data-formula attribute present will be marked as read-only by the Calx until you pass ‘readonly’ config key as false.

But I thought Calx can do more than just add two values, then what else can be done with Calx?

Copy another cell value

You can copy other cell’s value by referring it’s id inside the formula

Standard arithmetic calculation

Calx can parse standard arithmetic calculation such as addition, subtraction, multiply, and divide. You may also put them into parentheses to modify it’s precedence:

Power and square root

Calx use ^ and SQRT() to define power and square root calculation:

Modulus

Modulus operator can can be called by simply write MOD in the formula:

Math constant

The only constant defined in current Calx version are Phi and e, you can get phi and e value by write PI and E in the formula

Maximum, minimum, average, and sum

This functionality can be accessed by simply writing MAX, MIN, AVG, and SUM inside the formula, it only work if your element’s id mimic the Excel cell index (A1,B1 and so on)

Conditional and comparative operator

Calx support IF() conditional function with comparative operator to compare element’s value, supported comparative operator are ‘<‘, ‘>’, ‘>=’, ‘<=’ and ‘<>’

IF operator can be used by write IF([condition],[true],[false]) in the formula:

Logical operator

Currently, Calx only support 2 logical operator, AND and OR. It used within the IF statement to combine two or more comparative operator

Other mathematical function

Other mathematical function currently supported by Calx are:

  • ABS : to get an absolute value of a number
  • ROUND : to get rounded value of a number
  • CEIL : to get rounded up value of a number
  • FLOOR : to get rounded up value of a number

Simply write FUNCTION_NAME($CELL_ID) inside the data-formula attribute.

Other planned function

More mathematical, financial and statistical function will be added in near future, or you can implement it by yourself, it’s easy 😉

 

39 Comments

  1. Galih September 17, 2016 at 9:18 pm - Reply

    bookmarks this project

  2. Julio June 16, 2016 at 12:09 pm - Reply

    Hey there, so I’m working on a project where I import data on an ajax function and fill out a bunch of text boxes and tables. In one of the tables I have values that return in the ajax array and I need to call the sumif function but I only get “#ERROR!”

    Here’s my code:

    if(deal.hasOwnProperty(‘Payment’)){
    currentRow = 0;
    $.each(deal.Payment, function(i, pmt){
    currentRow++;
    var user = pmt.User.split(” “);
    $(‘#payment_table’).append(‘\
    \
    ‘ + pmt.Date + ‘\
    ‘ + pmt.Amount + ‘\
    ‘ + pmt.Category + ‘\
    ‘ + pmt.Note + ‘\
    ‘ + pmt.PayType + ‘\
    ‘ + user[0] + ‘\
    \
    ‘);
    });
    $(‘#PD1’).attr(‘data-formula’, ‘SUMIF(TB1:TB’ + currentRow + ‘,”DOWN PAYMENT”,TA1:TA’ + currentRow + ‘)’);
    $(‘#PD2’).attr(‘data-formula’, ‘SUMIF(TB1:TB’ + currentRow + ‘,”* DEFERRED”,TA1:TA’ + currentRow + ‘)’);
    $(‘#PD3’).attr(‘data-formula’, ‘SUMIF(TB1:TB’ + currentRow + ‘,”PAYMENT”,TA1:TA’ + currentRow + ‘)’);
    $(‘#PD4’).attr(‘data-formula’, ‘PT4-SUM(TA1:TA’ + currentRow + ‘)’);
    }
    $(‘#deal_form’).calx();

    Any ideas why this function returns this value? BTW, if I change the criteria range to numbers it works!, it’s only when I try to use a string, is when it doesn’t work.

    Thanks in advance!

  3. Guru December 7, 2015 at 7:21 pm - Reply

    Can we use excel formula’s as it is in Calx ??

    • ikhsan December 7, 2015 at 7:31 pm - Reply

      Yes, that is what calx for, most of formula function is supported in calx 2.*

  4. Praveen August 26, 2015 at 2:01 am - Reply

    Dear Sir,

    I have downloaded your sample jquery-calx-master file and it is working local sever.
    but not working in hosted server.

    I am enclosing herewith the link for your kind reference
    http://asianindus.com/jquery-calx-master/sample/basic.html

    kindly advise to sort this issue.

    Thanks & Regards,
    Praveen
    +91 – 95000 59686

  5. John January 27, 2015 at 1:28 am - Reply

    Is it possible to overwrite a manually set by a data-formula?

    My first would be setting readonly into false but what’s next?

    • ikhsan January 27, 2015 at 4:29 am - Reply

      Hi John,

      I can’t see the code, you can add it by using the “crayon” button.

      Regards,
      Ikhsan

      • John January 27, 2015 at 5:33 pm - Reply

        The value $item is set by a select menu, when this value is set to 0 I want to set the value manually in the input field. How can i do this?

        Thanks!

        • ikhsan January 27, 2015 at 6:24 pm - Reply

          John,

          Maybe you can use the ‘IF’ statement

          Regards
          Ikhsan

          • John January 30, 2015 at 12:55 am

            That would be fine when I’ve got another field to give the manual value, but I want the value to be filled in the same field.

            I have to fill in something for the true statement.

  6. shannon ribbons September 11, 2014 at 4:57 am - Reply

    Hi there, wonder if you still read this? 😉
    I am trying to put a value into a field and then base the final calc on that value. I’m using a drop down selector to define a value which is passed into a field, then that field is used in the calc but it doesn’t work… here’s the full code:

    Apologies for the mess I have just been throwing stuff around in desperation. You can see the sample here:
    http://osisdesign.co.uk/sandbox/calc_a2.html
    You can see after clicking the drop down you get a valie in the next box, but if you click in that it reverts to zero and it refuses to pass its value to the final calculation…
    Hope this makes sense – thanks;

  7. Barry Corrigan July 4, 2014 at 10:09 pm - Reply

    Hi Ikhsan,

    Love the plugin. But on a select field I have a reset function in place

    $(‘#form1_package’).change(function(){
    $(“#form1_booth_package”).val(“”);
    });

    Where if a user chooses another option the other select field resets the the default value which is set to “0” But calx is not updating the total when the select field resets.

    Any ideas? – Here is my fiddle to demonstrate what I mean http://jsfiddle.net/GJ9rU/2/

    • ikhsan July 5, 2014 at 3:31 am - Reply

      Hi Barry,

      you could try to trigger change event on the select box

      regards
      Ikhsan

  8. blondeau June 20, 2014 at 8:55 pm - Reply

    Hi,
    First of all, thx for your awesome work…

    I implemented with success Calx and have a little question :
    I created a button which auto check another hidden one, with a simple jS function. (button A= value and button b= fixed discount)
    The reason why there is another discount button is that i’m forced to display A value and in another cell the discount (B value)…
    I’d like to know how, when you deselect button A (which auto uncheck button b), my sum display automatically the right sum without have to refresh my browser ? (So if A= checked, B=checked) and if A=unchecked, b=unchecked, but Data formula still display B value in the sum, even if B=unchecked)

    To resume, i’d like to autorefresh my sum without have to F5 my page…

    thanx by advance
    Lionel

    • ikhsan June 21, 2014 at 8:45 pm - Reply

      Hi Lionel,

      you can try $('#selector').calx('update'); or $('#selector').calx('refresh');

      • lionel June 24, 2014 at 9:03 am - Reply

        Hi Ikhsan, tx for your answer…
        Tried but no luck for me, doesn’t work… 🙁
        i tried another way but still doesn’t work too…

        $A = 1000
        data-formula =”($A)”

        if $A = is checked so :
        data-formula=”($A-1000)” but -1000 only if $A is checked….

        Is there a solution to substract 1000 only if $A is checked ?
        TX by advance !

  9. Abdullah Musani June 6, 2014 at 8:57 pm - Reply

    Hello,
    I wish to use this library in jquery grid. I have some complex calculation in grid. Is there any possibility to do so. If yes, Please give me an example to follow.

    Awaiting for your reply
    Thanks.

  10. WK April 28, 2014 at 12:19 am - Reply

    Hi!

    I found your plugin and it works great. Still I have some problems with it and hope you have some suggestions. I saw you are active on SO-site too, so I posted question there and hope you have time to look on it:

    http://stackoverflow.com/questions/23325715/how-to-calculate-form-field-values-both-ways

    Thank you anyway for your great work!

    Wbr,

    Gunnar

  11. TESTE April 9, 2014 at 12:02 am - Reply

    CONCATENATE function does not exist

    • ikhsan April 12, 2014 at 7:35 pm - Reply

      I am sorry if I can’t satisfy everyone, but I am working hard to make the next version ready to release

    • ikhsan January 30, 2015 at 7:01 am - Reply

      Hi TESTE.

      you can now use CONCATENATE function or its shortcut like A1&A2 in jQuery Calx 2

  12. Jared March 21, 2014 at 12:26 am - Reply

    Hi, I am currently trying to show a text error messages for a loan estimator calculator. I noticed you said something about maybe having this in version 1.2. Is there any chance that feature is available yet. I tried calling a hidden input with a value=”text” but it just returned a number. For instance:  <input type=”text” id=”B3″ value=”This is the text.” style=”display:none;”/> <input type=”text” id=”B4″ data-formula=”$B3″/>

    Thanks for the help.

    • ikhsan March 21, 2014 at 6:14 am - Reply

      Hi Jared,

      I am sorry that the 1.2 will take long time to release, but currently you can use onupdate option to make this possible, please download the master branch instead of 1.1.9 tag here https://github.com/ikhsan017/calx

  13. Pavel February 18, 2014 at 7:53 am - Reply

    Hi Ikhsan,

    please add operations with dates … thanks and regards from russia 😉

    • ikhsan February 18, 2014 at 8:15 am - Reply

      Will try to add lot feature on the next update 🙂

  14. Sana December 26, 2013 at 10:00 pm - Reply

    Hi Ikshan ,

    I am writing a if condition on a column using your plugin . Documentation says, IF($A > $B,true,false) , I am trying to pring Error , Perfect strings instead of true or false. Can you please tell me how i can add string messages ? Is that possible or should i use only numeric or columns to be displayed if true or false.

    Thanks,

    • ikhsan December 26, 2013 at 10:43 pm - Reply

      Hi Sana,

      Currently it only support numeric value, ver. 1.2.0 will support string value as well, also support more function.

      It will be released in about later next month

      Regards
      Ikhsan

  15. emdee October 21, 2013 at 7:06 pm - Reply

    Hi, Ikhsan,

    It is possible to have rounded numbers to 2 decimals everywhere ? Now, it’s ok,(12.55) but when i click to input there are many decimals. (12.55114054504)

    Thanks

    • ikhsan October 21, 2013 at 10:01 pm - Reply

      currently, no built in function that support round to specific decimal digit, but you can use some trick like

      data-formula=”ROUND(($A+$B)*100)/100″

  16. Ramesh October 1, 2013 at 1:12 pm - Reply

    Hi Ikhsan,
    Please give solution immediately to the above problem.
    Actually in manual AND,OR,etc operators are not present in “jquery-calx-1.1.4.min” script.
    so obviously it will not support & i am get Error 404 not found “/…/calx-1.1.4/jquery.min.map”
    Please help me regarding it & i am stuck in here…!!

    • ikhsan October 27, 2013 at 11:37 am - Reply

      Hi Ramesh,

      sorry for late reply due to my work load, but I just push the 1.1.9 version to github repository, and added AND and OR operator

      you can use it by write [condition AND condition OR condition] inside the formula

      data-formula=”IF($A=1 AND $B=1, 1, 0)”

      regards
      Ikhsan

  17. Ramesh September 30, 2013 at 1:39 pm - Reply

    Hi Ikhsan,
    I tried what u gave as a solution but
    1.IF(AND($C21>=0,$C21=0),($C21<=5)),$C21,0);
    here in 2 ways it is not working due AND operator (i think so), it is not able to work

  18. Ramesh September 30, 2013 at 1:24 pm - Reply

    Hi Ikhsan,
    I tried what u gave as a solution but
    1.IF(AND($C21>=0,$C21=0),($C21<=5)),$C21,0)
    here in 2 ways it is not working due AND operator (i think so), it is not able to work

  19. Ramesh September 27, 2013 at 6:26 pm - Reply

    =IF(30>=F24>=0,30,30)
    =IF(F24>200,(F24-SUM(D42:D44)),0)
    =IF(C21>=67,C21,0)
    =IF(500>=F24>=0,500,(F24-500))

    How to use these type of excel formula from calx ?
    I tried but it was not working.
    Show atleast 1 example of the above ..formulas ..!!

    Also tell me any websites to solution to it ..!

    • ikhsan September 27, 2013 at 6:49 pm - Reply

      Hi Ramesh,

      =IF(30>=F24>=0,30,30)
      currently not possible, but will add new keyword AND, OR, and NOT to make such comparison, so it will be
      IF( OR(($F24 >= 0), ($F24 < = 30), 30, 60)for =IF(F24>200,(F24-SUM(D42:D44)),0)
      you can write
      IF($F24 > 200, ($F24-SUM($D42,$D44)), 0)

      for
      =IF(C21>=67,C21,0)
      you can write
      IF($C21>=67, $C21, 0)

      regards
      Ikhsan

  20. FLy September 13, 2013 at 10:38 pm - Reply

    1. how to specify multiple IF conditions in a single formula?
    2. you planned count function?

    thanks, excellent plugin.

    • ikhsan September 20, 2013 at 11:20 pm - Reply

      Hi Fly,

      nested if could be done like the way excel IF statement does

      IF( $A1 = 0 , $B1 , IF( $A1 = 1 , $C1 , $D1 ))

      Maybe in near future will add more function

Leave A Comment