What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Can I import excel formula to spreadsheet widget

    Asked by andolphoneto on June 22, 2016 at 11:50 AM
    excel spreadsheet widget import formula excel formula
  • Profile Image
    JotForm Support

    Answered by BDAVID on June 22, 2016 at 02:04 PM

    We have a widget called Spreadsheet, in which can setup formulas similar to Excel: https://widgets.jotform.com/widget/spreadsheet 

    https://www.jotform.com/help/252-How-to-Add-a-Widget-to-your-Form 

    Let us know if you need more help.

  • Profile Image

    Answered by andolphoneto on June 22, 2016 at 05:58 PM

    Thank you David, is working very well, except this formula below:

     

    =IF(E12<=0,0,IF(E12<=L6,0,IF(E12<=L7,-E12*J7/100+M7,IF(E12<=L8,-E12*J8/100+M8,IF(E12<=L9,-E12*J9/100+M9,IF(E12>L9,-E12*J10/100+M10,"Erro")))))

     

    Could you help me to find what parameter is not working in form? I've tested this in excel and works very well there.

    Thank you

  • Profile Image
    JotForm Support

    Answered by BDAVID on June 22, 2016 at 07:46 PM

    Unfortunately, the If function is not supported in the Spreadsheet widget. Let us know if you have more questions.

  • Profile Image

    Answered by andolphoneto on June 22, 2016 at 09:35 PM

    I've took of the "IF" from formula, and know this message came "Invalid left-hand side in assignment"

    Do you know what is this?

    the formula is now this: e10 =e9<=0,0, e9=h1,0, e9=h2,-e9*f2/100+i2, e9=h3,-e9*f3/100+i3, e9=h4,-e9*f4/100+i4, e9>h4,-e9*f5/100+i5

  • Profile Image
    JotForm Support

    Answered by jonathan on June 23, 2016 at 12:33 AM

    I was able to see the error on the form https://www.jotformz.com/form/61734744553662 as well.

     

    it look like the formula you used 

    e10 =e9<=0,0, e9=h1,0, e9=h2,-e9*f2/100+i2, e9=h3,-e9*f3/100+i3, e9=h4,-e9*f4/100+i4, e9>h4,-e9*f5/100+i5

    is not a valid formula in the Spreadsheet widget.

    As already mentioned by BDAVID, complex nested formula is not supported by the Spreadsheet widget.

    I removed that formula in my test form, and it become fine after.

    I am afraid you will have to redo the formula you want to add on the widget and use only very basic calculation formula.

    Hope this help. Let us know if you need further assistance.

     

  • Profile Image

    Answered by andolphoneto on June 23, 2016 at 07:34 AM

    Hi Jonathan, now I've made a very simple formula: e10 -e9*7.5+1713.60

     

    but still doesnt work , do you know what I've made wrong?

     Error at A1: Field E10 contains invalid value: "-e9*7.5+1713.60"

  • Profile Image
    JotForm Support

    Answered by Boris on June 23, 2016 at 08:51 AM

    In order to have your calculations in the spreadsheet performed correctly, please make sure to separate all mathematical symbols with a space before and after the mathematical symbol, so that this:

    e10 =-e9*7.5+1713.60

    becomes this:

    e10 = -e9 * 7.5 + 1713.60

    Otherwise, the spreadsheet will have a hard time understanding where one value ends and another begins. Please also do note that for a value to be calculated, there must be an equals sign after the cell at the start pf the line. For example, this is a calculation:

    e10 = -e9

    This is not a calculation, but rather regular text instead:

    e10 -e9

    Please let us know should you run into any other issues along the way, and we will be happy to help.

  • Profile Image

    Answered by andolphoneto on June 23, 2016 at 12:36 PM

    How can I put a negative number in a cell?