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

• 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

• 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

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

• 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

• 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.

Thank you!

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

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

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

That is excellent, thanks very much