Ignoring zeros when averaging

[Rob_at_Source360]

I have browsed the forums and have tried some samples, but I can't make any headway.  I have a numeric field on an form in Acrobat X that I want to be an average of several groups of check boxes.  For the simplicity sake of my test, I have three groups of 5 check boxes.  For each group of 5 boxes, they have an export value of "1", "2", "3", "4" and "5" respectively.  The first 5 have the name "Prop1", the second group is named "Prop2" and the third is "Prop3".  If I take my numeric field where I want the average and go to the calculations tab and select "Average" and then chose "Prop1", "Prop2" and "Prop3", it works as long as none of the groups have a blank check box.  I want it to ignore any group with a blank check box (where the value of the group would be "0" or null) and only average the groups that have values.  For instance, if the group "Prop1" has the "3" check box selected, and the group "Prop2" has the "3" check box selected and the group "Prop3" has the "3" check box selected, then the average is "3" as it should be.  But, if I un-check one of them, the average changes to "2".  The reason I say the value of an un-checked group is "0" is because if I create a new numeric field and set it to be the sum of the group, when I have a box selected, the calculated field shows that value, but if I have no box selected, the value is zero.  So, I may need to be checking for null and "0" as I can't tell what the true value is for a group of check boxes when none of them are checked.  I can not use the group names as my field in the formula and instead use my calculated field that sums the group as I know this shows zero when no box is checked.  I have them off to the side and can leave them and use them for calculations if that is better and I will just hide them.

So, more simply put, how do I average 3 fields (or 3 check box groups), but ignore any field that is blank or has a value of zero so that it is ignored in the averaging assuming those field names are "Prop1", "Prop2" and "Prop3".  Also, I am assuming this javascript should go in the calculations tab for the field and use the custom script section at the button to place the javascript.

[Rob_at_Source360]

I have tried the following, which works as long as each of the three groups has a value.  If one of the groups does not have a value, it says "NaN" in the field:

var fld1=this.getField("Prop1").value;

var fld2=this.getField("Prop2").value;

var fld3=this.getField("Prop3").value;

var num=0;

if (fld1!="")

{num+=1}

if (fld2!="")

{num+=1}

if (fld3!="")

{num+=1}

if (num==0)

{event.value=""}

else

{event.value=(Number(fld1)+Number(fld2)+Number(fld3))/num}

[try67]

Yes, this is a common problem... I've asked Adobe to implement an option to calculate an average ignoring zero (or blank) fields, but it hasn't been implemented (yet?). Which is why I wrote my own code that does it:

function calcAverage(fields, ignoreBlanks, ignoreZeros) {
    var total = 0;
    var n = 0;
    for (var i in fields) {
        var f = this.getField(fields);
        if (f==null) {
            console.println("Error! Can't locate a field called: " + fields);
            continue;
        }
        if (f.valueAsString=="" && ignoreBlanks) continue;
        if (Number(f.valueAsString)==0 && ignoreZeros) continue;
        total+=Number(f.valueAsString);
        n++;
    }
    if (n==0) event.value = "";
    else event.value = total/n;
}

Place the above code as a doc-level script and then you can use it like this as the custom calculation code of your target field:

calcAverage(["Prop1", "Prop2", "Prop3"], true, true);

[Rob_at_Source360]

Well, that solved my NaN problem, and it seemed to work for a second, but now I am noticing that the calculated average is not correct after making several selections.  For instance, right now I have my three fields visible, and each one has a value of 1, but the average says1.33333333.  So, it does not seem to be properly updating the value of the average each time I change one of my check box selections.  The value showing in the sum field I created for the check box group is correct, but the average field, which is using the javascript to average those three fields, is not correct.

[try67]

Do the other fields have a calculated value as well?

[Rob_at_Source360]

Yes, I have three groups of radio buttons (have used check boxes as well).  Each group has 5 buttons and each button has a value from 1 to 5.   Sicne you can't see the value of a radio button on the screen, I have three calculated fields off to the side of that row of buttons that does a sum on the group.  The average field then uses those three calculated values to do the average.

In the screen shot below, you can see my three selected radio buttons from my three groups, the calculated value of the group which is off to the right and then above, the average.  The average calculation is using the three fields below it, yet the average is not correct.  It is like there is lag and it is not being re-calculated consistently everytime a selection is made.  I was using check boxes earlier, so that I could check and un-check, and if i did this a couple of times, it would catch up and the average would be correct.  I switched to radio buttons to see if the check boxes were the problem, but the weird thing is, the value in the field I am using always updates correctly overtime I make a choice, so the average should be correct all the time as well.

[try67]

It sounds like a field calculation order problem. You need to set it so that the field with the average is calculated after the rest of the fields.

Note: This is not the same as the tab order. You can set it in Form Edit mode, via Tasks - Other Tasks - Set Field Calculation Order.

[Rob_at_Source360]

Thanks again.  I wish I understood!  Some of the samples I tried were creating a dynamic array of fields that were not null.  This is much shorter and I can't for the life of me figure out how it works, but it does.  I just added a 4th group and updated the average field with the new group summary field and it works.

[try67 · Accepted solution]

I've made a small adjustment to this code, so that you can now use it directly with your radio-buttons (if you set their "Radio button choice" values to numeric values), and you won't need to use those text fields just to convert the "Off" values to zeros.

The new code:

function calcAverage(fields, ignoreBlanks, ignoreZeros) {
    var total = 0;
    var n = 0;
    for (var i in fields) {
        var f = this.getField(fields);
        if (f==null) {
            console.println("Error! Can't locate a field called: " + fields);
            continue;
        }
        if (f.valueAsString=="" && ignoreBlanks) continue;
        var v = Number(f.valueAsString);
        if (isNaN(v)) continue;
        if (v==0 && ignoreZeros) continue;
        total+=v;
        n++;
    }
    if (n==0) event.value = "";
    else event.value = total/n;
}

Beers can be sent here...

[Rob_at_Source360]

Im using grouped check boxes now, which allow you to turn one off after you have turned it on (which we need).  Will this work with them?  They have an export value instead of the radio button choice.  Same concept?

[Rob_at_Source360]

Nevermind, I went ahead and tried it and yes, it works now on the group name and the calculated fields are no longer needed.  Thank You!

[Rob_at_Source360]

Below is another example (back to checkboxes now).  I checked the three 5's and the average was correct.  Then I checked the 4 box in the second row and my calculated value changed to a 4, but the average did not update and still says 5.

[Rob_at_Source360]

That was it!!!!!  Your a genius and I owe you a beer

[gc55]

This is exactly what I was looking for! I have been searching for hours and tried various scripts but nothing quite worked. Thanks so much!

[Cwcavinder]

Where does this go into the form