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

martybrett86Asked 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
2nd5th = 10pts
6th10th = 5pts
11th25th = 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

VincentJayAnswered on March 20, 2021 at 05:32 PMJotform Support
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/301howtoassigncalculationvalue
The Form Calculation widget can compute the total of each field. Please check this guide for more information: https://www.jotform.com/help/259howtoperformformcalculationusingawidget
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/57smartformsconditionallogicforonlineforms
Let us know if you have any questions. Thank you!

martybrett86Answered 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

Patrick_RAnswered on March 21, 2021 at 10:31 AMJotform Support
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!

martybrett86Answered 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!!

Patrick_RAnswered on March 21, 2021 at 01:22 PMJotform Support
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!

martybrett86Answered on March 21, 2021 at 02:34 PM
That is excellent, thanks very much