calculate sum of fields but ignore N/A

[Brittany128]

Hi Adobe, I have successfully applied this script to my PDF form, but what I actually need is not an average of the fields... rather a sum of the fields - except where N/A is selected, then it should sum only the fields with numbers in them. In brief, if the form has 10 ratings where the user can choose N/A, 0, 1, 2, 3, 4, 5 - if the selection of one rating is N/A then it should not be included in the final total, which needs to then be calculated as a total of 9 ratings. The final score will be shown as a percentage achieved out of a total.

Please help!

// Sum non-N/A values;

var aFieldNames = new Array("rating1", "rating2");

// counter for non-N/A values;

var nCount = 0;

// variable to sum non-N/A values;

var nSum = 0;

// default value for result if no sum computed;

event.value = 0;

// process array of field names;

for(i = 0; i < aFieldNames.length; i++) {

if(this.getField(aFieldNames).valueAsString != "N/A") {

// field does not have a value of "N/A";

nCount++; // increment counter

nSum += Number(this.getField(aFieldNames).value); // add value to sum

} // end value not N/A;

} // end loop processing one field;

// compute the sum;

if(nCount != 0) {

// non-zero divisor so we can compute the sum;

event.value = nSum / nCount;

}

[try67]

The technical solution to your question is easy. Just change the last line from:

event.value = nSum / nCount;

To:

event.value = nSum;

I would also change these lines:

if (this.getField(aFieldNames).valueAsString != "N/A") {

     nCount++; // increment counter

     nSum += Number(this.getField(aFieldNames).value);

}

To:

var v = this.getField(aFieldNames).valueAsString;

if (!isNaN(Number(v)) nSum+=Number(v);

However, you have a logical problem you need to consider. If it's a sum, what's the difference between selecting "0" and not selecting anything at all? Or selecting "N/A"? You need to re-think your scales.

[Brittany128]

Thank you try67, that's already making a lot more sense... please bear with me, there needs to be a "0' for no score, but there also needs to be the "N/A" because some fields are not applicable... however, they then need the total to score to be reduced by the skipped fields so I can show a percentage score (which I will handle with a separate field)

I'm getting a syntax error - missing ) after condition   23: at line 24

// Sum non-N/A values;

var aFieldNames = new Array("rating1", "rating2");

// counter for non-N/A values;

var nCount = 0;

// variable to sum non-N/A values;

var nSum = 0;

// default value for result if no sum computed;

event.value = 0;

// process array of field names;

for(i = 0; i < aFieldNames.length; i++) {

var v = this.getField(aFieldNames).valueAsString;

if (!isNaN(Number(v)) nSum+=Number(v); // add value to sum

} // end value not N/A;

} // end loop processing one field;

// compute the sum;

if(nCount != 0) {

// non-zero divisor so we can compute the sum;

event.value = nSum;

}

[try67]

Sorry, my mistake. Change this:

if (!isNaN(Number(v)) nSum+=Number(v);

To:

if (!isNaN(Number(v))) nSum+=Number(v);

[Brittany128]

No worries, still better than my efforts... but it's still giving me a syntax error (undefined this time) 27: at line 28... see the full script below...

// Sum non-N/A values;

var aFieldNames = new Array("rating1", "rating2");

// counter for non-N/A values;

var nCount = 0;

// variable to sum non-N/A values;

var nSum = 0;

// default value for result if no sum computed;

event.value = 0;

// process array of field names;

for(i = 0; i < aFieldNames.length; i++) {

var v = this.getField(aFieldNames).valueAsString;

if (!isNaN(Number(v))) nSum+=Number(v); // add value to sum

} // end value not N/A;

} // end loop processing one field;

// compute the sum;

if(nCount != 0) {

// non-zero divisor so we can compute the sum;

event.value = nSum;

}

[try67]

You made some changes to the code that you shouldn't have... Use this:

// Sum non-N/A values;
var aFieldNames = new Array("rating1", "rating2");
// variable to sum non-N/A values;
var nSum = 0;
// process array of field names;
for (var i = 0; i < aFieldNames.length; i++) {
    var v = this.getField(aFieldNames).valueAsString;
    if (!isNaN(Number(v))) nSum+=Number(v); // add value to sum
}
event.value = nSum;

[try67]

Actually, I was overlooking the obvious simple solution to this. You don't need any code at all. You can use the built-in Sum function under the Calculate tab and it will work just as well...

[Brittany128]

Thank you try67, that seems to be doing the job in my test... perhaps I did over complicate it the more I thought about it.

[Brittany128]

Hi, thanks for previous assistance... I've just been advised of a previously unknown issue with the script for calculation to add non-N/A values. This is the custom calculation script I have used.

// Sum non-N/A values; 

    var aFieldNames = new Array("rating1", "rating2", "rating3", "rating4", "rating5", "rating6", "rating7", "rating8", "rating9", "rating10", "rating11", "rating12", "rating13", "rating14", "rating15"); 

    // variable to sum non-N/A values; 

    var nSum = 0; 

    // process array of field names; 

    for (var i = 0; i < aFieldNames.length; i++) { 

        var v = this.getField(aFieldNames).valueAsString; 

        if (!isNaN(Number(v))) nSum+=Number(v); // add value to sum 

    } 

    event.value = nSum; 

If the user selects a value field, but then they change their mind and make the field N/A - then the percentage starts to ADD the field value - and the percentage goes above 100% - which means it's not a valid percentage as it should always be 100% or less. It's probable that I have an incorrect validation script somewhere but i just don't know how to identify it. Would it help to post the entire form?

This is the custom validation script...

var a = this.getField("score");

var b = this.getField("total questions not NA");

event.value = (a.value / b.value) * 100

Please help!

[try67]

Why is that a validation script? It should be a calculation script.

And you should add a condition checking that the value of b is not empty or zero first.

[Brittany128]

Apologies, it is a calculation script. How would i express the condition checking the value of b is not empty or zero?

[try67]

Use this code:

var a = Number(this.getField("score").valueAsString);
var b = Number(this.getField("total questions not NA").valueAsString);
if (b==0) event.value = "";
else event.value = (a/b) * 100;

[Brittany128]

Still adds the N/A to the final percentage if the user decides to select N/A after selecting a field value.

Would it be possible to send you my completed form to have a look over and see where I have gone wrong. I can pay for your time as I really need to resolve my issue.

I'm based in New Zealand so time may be a little out.

[try67]

Yes, you can send it to try6767 at gmail.com and I'll look at it right now.

[gkaiseril · Accepted solution]

The script you copied was for computing the average or a groups of form fields and excluding the "N/A" value. Since the computation of the average requires the sum divided by the count of the items and division by zero in JavaScript may not result in a numeric value, this division needs to be excluded. And the is what the test for a count of zero does.

This is way just cutting and pasting a script from one PDF form to another without editing can cause a number of problems. As noted one could use the "Field is the ____ of the following fields:" or one could even use the "Simplified field notation" since both methods exclude any non-numeric values from their calculation.

The copied script was provided for the explicate exclusion of the "N/A" items from the count for the computation of the average. The first calculation option uses the total number of fields in the calculation of the average.

This scale and the inclusion of the "N/A" option is very common in consumer product service surveys as well as employee opinion surveys where they may also be grouped for the computation of the average of the respondents.