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

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

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!

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

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!

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

Thank you!!

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!

That is excellent, thanks very much