6 Replies Latest reply on Sep 20, 2013 8:35 AM by try67

# Need help creating a custom formula

I have a staff evaluation form for which I need to create a custom calculation script. We have a rating scale of 1,2,3,4,N/O (not observed). I need a formula that will not include any indicators that were marked "N/O" but will calculate an average of all the numbers chosen and place it on the form.

The formula needs to look at all fields and determine if they are a numeric value or N/O, add all the fields with a numeric value together and then divide by the total number of ratings that have a numeric value. For example:

1.01 = 4

1.02 = 3

1.03 = N/O

1.04 = 3

1.05 = 3

1.06 = 4

1.07 = N/O

I need the formula to add 1.01,1.02,1.04,1.05, and 1.06 together and divide by 5 instead of dividing by 7 because all the indicators were not observed.

• ###### 1. Re: Need help creating a custom formula

What are the names of the drop-down fields?

• ###### 2. Re: Need help creating a custom formula

The field names are 1.01, 1.02, 1.03, etc.

• ###### 3. Re: Need help creating a custom formula

This is a fairly common question. It will require a custom JavaScript to perform the the task of adding and counting selected fields then then performing the special calculation.

Have you searched for "average" or "averaging"?

The following script will average an array of numeric values or optionally numeric values not equal to a given value.

function AverageIFNE(aValues, cValue) {

var fAverage = "";

/*

var nCount = CountIFNE(aValues, cValue);

if (nCount != 0) {

fAverage = SumIFNE(aValues, cValue) / nCount;

}

*/

var nCount = 0; // variable for sum of non-null items;

var fSum = 0; // variable for count of non-null items;

if(cValue == "undefined") {

// process the array of field values with no restriction

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

if(!isNaN(aValues[i])) { // test for non-null value or other values;

nCount++; // increment non-null count;

fSum += Number(aValues[i]); // increment non-null count;

} // end not a number processing

} // end field name processing;

} else {

// process the array of field values with restriction

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

if(!isNaN(aValues[i]) && aValues[i] != cValue ) { // test for non-null value or other values;

nCount++; // increment non-null count;

fSum += Number(aValues[i]); // increment non-null count;

} // end not number processing

} // end field name processing

} // end cValue test

if (nCount != 0) {

// compute average for none zero count result

fAverage = SumIFNE(aValues, cValue) / nCount;

}

return fAverage;

}

You will need to create an array of values from your field to be processed and then just call the function with the array values to be processed and since the value you want to exclude is not a number you do not need ot specify the optional value.

// array of field names to process

var aFields = new Array ("Field1", "Field2", "Field3","Field4", "Field5","Field6", "Field7");

// create an array of the values for the named fields

var aValues = new Array();

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

aValues[i] = this.getField(aFields[i]).value;

}

// call average if ne function

event.value = AverageIFNE(aValues);

• ###### 4. Re: Need help creating a custom formula

Ah, OK. In that case you can use this code as the custom calculation script of your average text field:

var n = 0;

var total = 0;

for (var i=1; i<=7; i++) {

var f = this.getField("1.0"+i);

if (/^\d\$/.test(f.valueAsString)) {

n++;

total += Number(f.valueAsString);

}

}

if (n==0) event.value = "";

else event.value = total/n;

• ###### 5. Re: Need help creating a custom formula

I also have indicators that have field names that are 2.01, 2.02, etc; 3.01, 3.02, etc; and 4.01, 4.02, etc. Do I just change the 1.0 to 2.0, 3.0, or 4.0 to have this formula calcuate those ranges as well?

Thanks,

David

• ###### 6. Re: Need help creating a custom formula

Yes, you just need to change the base field name...