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.
Calculate average value while excluding empty fieldsAsked by shahabeddini on June 04, 2015 at 05:13 AM
Yes, this is possible with our forms.
In your Form Calculation field, please use the More functions button represented with three horizontal dots (...), and scroll down to the avgNoZero() function. It will perform the task that you seek.
I have made a small demo form here, so that you can see the feature in action:
Please let us know if you need further assistance with this, or kindly open a new thread for any other questions or issues you may have with your forms, we will be happy to help.
Thanks a big time. :)
You are kindly welcome. :)
This function excludes 0 values as well as Negative values. Is there any way to exclude just blank fields?
You are correct, this function will exclude empty fields, fields with zero value (0), and fields with a negative value.
Please allow for a little time so that I can test and see if I can find a workaround for you.
I will let you know if I find a way to exclude only empty fields. Thank you.
Thank you for holding. I am sorry for taking the time, but I'm afraid that I was unable to find a working solution to this problem.
I can't seem to make it count only the filled fields for calculating the average value, unless I use the avgNoZero() function, but this function counts negative and zero values as false and leaves them out of the counting.
Please let us know if you find a working solution yourself. I'll be keeping an eye on this issue and I'll try a couple more things, time permitting. If I manage to work around it, I will certainly let you know by means of this thread.
Thanks for your time consideration Boris. I will give it a try to find other solution. Please let me know if you find a solution.
Unfortunately I don't have that much expertise. Is it possible to lead me how to do this with more details and links?
However, this can still be done using condition logic, although it might use a couple of fields but hiding them should be alright aesthetically. Here's my test form: http://form.jotformpro.com/form/51554641585965?. Try adding some inputs and leaving others blank. See if that works for you. You can clone it to explore how I set it up.
Here's how I set it up.
1. I'll have 3 fields for each item.
Text box 1 "Item 1" is the input text box
Text box 2 "Count as 1" is the indicator if it will be used for the total number of items to be used in the average formula. If Text box 1 is filled, then we'll insert "1" in Text box 2.
Text box 3 "Real Value of 1" is the text box where we will insert the real value. Let's say if Text box 1 is filled then we'll insert Text box 1's value there, if it's empty then we'll insert "0" on it.
2. This is how my conditional logic is setup. Notice item 1 here.
3. At the last part, we'll have 3 fields that uses the Form Calculation widget.
"Count Them" is the total number of items, we will based this by adding all the "Count as 1" fields.
"Total" is the sum of all the "Real Value..." fields.
"Average" is the average formula. "Total" / "Count Them" = "Average".
I hope that helps. It might be confusing but you'll understand it along the way.
Do let us know if there are things that are quite unclear.