Calculate average value while excluding empty fields

  • Profile Image
    Asked on June 04, 2015 at 05:13 AM

    I need to calculate the average of some fields value.  Some of these fields may be left blank.

    In normal situation, the value of blanked fields are 0. 

    How can I exclude blanked fields  from calculation?

  • Profile Image
    Answered on June 04, 2015 at 05:30 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.

  • Profile Image
    Answered on June 04, 2015 at 05:31 AM

    Thanks a big time. :)

  • Profile Image
    Answered on June 04, 2015 at 05:45 AM

    You are kindly welcome. :)

  • Profile Image
    Answered on June 04, 2015 at 06:01 AM

    Hi again

    This function excludes 0 values as well as Negative values. Is there any way to exclude just blank fields?

  • Profile Image
    Answered on June 04, 2015 at 07:41 AM

    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.

  • Profile Image
    Answered on June 04, 2015 at 12:45 PM

    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.

    Kind regards.

  • Profile Image
    Answered on June 05, 2015 at 02:42 AM

    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. 

  • Profile Image
    Answered on June 05, 2015 at 07:54 AM

    Hi, Another approach will be t download you form's full source code then use custom JavaScript to compute the average of the fields, this will give you more flexibility to add the logic to just exclude zero values fields in the average computation

  • Profile Image
    Answered on June 05, 2015 at 07:59 AM

    Unfortunately I don't have that much expertise. Is it possible to lead me how to do this with more details and links?

  • Profile Image
    Answered on June 05, 2015 at 12:38 PM


    If you're not that familiar with Javascript, I believe using the full source code wouldn't be the best way to implement this.

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

    Thank you.