Input Table: how to exclude one column in the average score calculation

  • Ryan Berlin
    Asked on February 28, 2023 at 8:20 PM

    I would like to assign one column as Not Applicable and have any row where a user selects Not Applicable excluded in an average score calculation. Is this possible?

    Page URL: https://www.jotform.com/help/343-how-to-perform-form-calculation-in-the-input-table-field/
  • Rica Support Team Lead
    Replied on March 1, 2023 at 1:52 AM

    Hi Ryan,

    Thanks for reaching out to Jotform Support. Yes, it is possible by assigning a calculation value of 0 to the Not Applicable column. I've checked your form and I can see that you have already assigned a calculation value of 0 to the Not Applicable column.

    1677653452 63fef5cc1680c Screenshot 10

    I've also tested your form and the calculation is correct.

    1677653517 63fef60dd8f5f Screenshot 21

    Let us know if there’s anything else we can help you with. 

  • rberlinconsulting
    Replied on March 1, 2023 at 9:49 AM

    Thank you for taking a look. The challenge to resolve is I would also like to add a field for an average calculation (a number between 1 and 4) which would require dividing the Total Score (flagged by the red arrow in your image above) by the max score (the number in the parenthesis). The max score would be impacted by the N/A responses. In the example you completed, I would need to subtract 12 from 32 to create the correct equation and produce an accurate average score.

    Can this be done?

  • Marlon_T Jotform Support
    Replied on March 1, 2023 at 10:01 AM

    Hi Ryan Berlin,

    To confirm, above all the instructions provided, you'd like to add another field and assign it to compute the average calculation?

    The formula would be Total Score/Max Score. Could you give us the entire requirements of your form in order for us to know which fields should be included and excluded with all the formulas?

    For reference, feel free to check our guide on form calculation math function reference.

    Give it a try and let us know if you need any help.

  • rberlinconsulting
    Replied on March 1, 2023 at 11:11 AM

    I found it! For anyone else looking to do a similar calculation, I was able to use the function avgNoZero() as the calculation. The frustrating trick is that the default is to make the commas between data points a string (brown) but needed to be updated to black to create the actual calculation.

    Yay! There are a lot of threads with similar needs that have very complex solutions. This is a simple solution with no special criteria or considerations needed.