Is it possible to use if and calculations in a jotform, similar to how they would work in excel

  • Profile Image
    martybrett86
    Asked on March 20, 2021 at 06:30 AM

    Hi wondering if it is possible to have a field that calculates 'if and' functions like Excel


    It is a points submission form for esports tournaments. 1pt is awarded per kill then additional placement points.


    If a team wins a game they get 15 points

    2nd-5th = 10pts

    6th-10th = 5pts

    11th-25th = 2pts


    On the form they enter their kills and finishing position for three games

    I would like it to operate so the team enter where they finished and the form allocates points and provides a total at the end.We currently have this set up in excel once we download the form data but would like to display the total on the form. The excel formula is =IF(AND(C2>=2,C2<=5),10,IF(AND(C2>=6,C2<=10),5,IF(AND(C2>=11,C2<=25),2,IF(AND(C2=1),15,0)))) where C2 is the relevant data for finishing position


    The score box would hopefully add all the kills (I see this is easily done) but also assign points for each finishing position then add the total


    Thanks

  • Profile Image
    VincentJay
    Answered on March 20, 2021 at 05:32 PM

    Hello,

    Yes, you can use calculation and condition here in JotForm. Please provide the workflow so we can assist you better.

    You can assign Calculation Values to a field by following this guide: https://www.jotform.com/help/301-how-to-assign-calculation-value

    The Form Calculation widget can compute the total of each field. Please check this guide for more information: https://www.jotform.com/help/259-how-to-perform-form-calculation-using-a-widget

    As for the IF conditions, please provide more information so we can check if that is possible to achieve. You can also visit this page for more information: https://www.jotform.com/help/57-smart-forms-conditional-logic-for-online-forms

    Let us know if you have any questions. Thank you!

  • Profile Image
    martybrett86
    Answered on March 21, 2021 at 06:44 AM

    Hi what more info would you require? The formula used in excel is =IF(AND(C2>=2,C2<=5),10,IF(AND(C2>=6,C2<=10),5,IF(AND(C2>=11,C2<=25),2,IF(AND(C2=1),15,0)))) where C2 is the relevant data for finishing position, and I would need to replace 'C2' with one of the jotform cells

  • Profile Image
    Patrick_R
    Answered on March 21, 2021 at 10:31 AM

    Hello! Considering your form, from what I can understand, the formula is:

    1. IF 'finishing position' IS GREATER THAN OR EQUAL TO 2 AND LESS THAN OR EQUAL TO 5 THEN POINTS BE 10

    2. IF 'finishing position' IS GREATER THAN OR EQUAL TO 6 AND LESS THAN OR EQUAL TO 10 THEN POINTS BE 5

    3. IF 'finishing position' IS GREATER THAN OR EQUAL TO 11 AND LESS THAN OR EQUAL TO 25 THEN POINTS BE 2

    And then, in the end; you need to calculate all the points.

    Please confirm whether this is correct. If not, then please correct me. We'll get back to you accordingly.

    Awaiting your response.

    Thank you!

  • Profile Image
    martybrett86
    Answered on March 21, 2021 at 11:49 AM

    Hi yes that is pretty much spot on,(with an extra one if finishing position = 1)


    Thank you!!

  • Profile Image
    Patrick_R
    Answered on March 21, 2021 at 01:22 PM

    Hello! Thank you for confirming. Please check this form: https://form.jotform.com/210795143285963

    In this form; considering the "Finishing Position" value for "Game 1", I'm applying points (as per the following criteria) in the "Points for Game 1" field

    For this, 4 CALCULATE conditions have been used.

    1. IF 'finishing position' IS 1 THEN POINTS BE 15

    2. IF 'finishing position' IS GREATER THAN OR EQUAL TO 2 AND LESS THAN OR EQUAL TO 5 THEN POINTS BE 10

    3. IF 'finishing position' IS GREATER THAN OR EQUAL TO 6 AND LESS THAN OR EQUAL TO 10 THEN POINTS BE 5

    4. IF 'finishing position' IS GREATER THAN OR EQUAL TO 11 AND LESS THAN OR EQUAL TO 25 THEN POINTS BE 2

    Please feel free to test it; if this solves your purpose; then you can apply the same conditions (simply copy/paste and then change the field names in new conditions) and field for all of your Games.

    You'll need 4 conditions per Game.

    If you want to clone this form into your own account to check the implemented conditions; then you can do so by following these instructions.

    I hope this helps. In case this doesn't solve your purpose or if you think I missed anything, then please point that out.

    Thank you!

  • Profile Image
    martybrett86
    Answered on March 21, 2021 at 02:34 PM

    That is excellent, thanks very much