Formula to calculate the average of a number of fields

[mlbooth78]

I am trying to write a formula in java script to calculate the average of 42 numbers, where some of the fields can be empty.

[try67]

If a field is empty, do you want to treat it as zero and include it in the calculation, or ignore it?

[mlbooth78]

If the field is empty I would like for it to be ignored.

[mlbooth78]

var TotalMoistValue = 0.0;

var TotalMoistCount = 0;

if (this.getField("CRow1").valueAsString !== "") 

{

  TotalMoistValue += this.getField("CRow1").value;

  TotalMoistCount++;                                                                 

}

event.value = TotalMoistValue / TotalMoistCount;

Here is what I am curerently using.

[try67 · Accepted solution]

I've developed a function that allows you to do it easily. The basic code is:

/*

    @Params

        aFields : an array of the field names to use for the calcluation

        bIgnoreBlanks : a boolean that tells the function whether or not to ignore blank fields

        bIgnoreZeros : a boolean that tells the function whether or not to ignore zero values

*/

function calcAverage(aFields, bIgnoreBlanks, bIgnoreZeros) {

    var total = 0;

    var n = 0;

    for (var i in aFields) {

        var f = this.getField(aFields[i]);

        if (f==null) {

            console.println("Error! Can't locate a field called: " + aFields[i]);

            continue;

        }

        if (f.valueAsString=="" && bIgnoreBlanks) continue;

        var v = Number(f.valueAsString);

        if (isNaN(v)) continue;

        if (v==0 && bIgnoreZeros) continue;

        total+=v;

        n++;

    }

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

    else event.value = total/n;

}

You can then call it from the field's calculation script like this:

calcAverage(["Field1", "Field2", "Field3"], true, false);

Edit: Fixed some small spelling mistakes in the code

[mlbooth78]

How do I use this function? Where do I but the field names that the script is pulling from? For example I usually use

this.getField("CRow1").value

I have 42 rows that the information has to pull from and I want to make sure that I use the function properly. I am a novice to javascript, and any help would be appreciated.

Thank you for all your help!

[try67]

Put the names of the fields (like "CRow1") inside an array and supply it to the function as the first parameter.

You call the function from the custom calculation script of the field where you want the result to appear.

[Htet Htet24836823qww5]

It doesn't work for me. Could someone check my codes and fix them?

[responsible_sharpness98C6]

Could someone explain much more simple for beginners

I want to make cusomer saisfaction form which has 8 criteria and rate from 1 to 6 (built by Form) that means the value receives from cusomer and at the end will calculate the avarage of given points. Normally the default avarage program works well but I have a "not to be rated" field that mean the criteria should be ignored in the calculation!

I got the code and modified it but still doesn't work . (I made field and in field properties activate calculation and choosed the last option customize calculation and added the code , but doesnt work at all.

could someone help me?

BR

[try67]

Did you use the code I shared above? If so, how did you call it, and what were the exact results? Saying "it didn't work" is not very helpful to us. You need to provide more details.

[responsible_sharpness98C6]

I tried 2 ways :

1- I made a field and in calculation I choosed the 3rd option "custom calculation script"

2-Second way : I added the calculation function in Javascript tool

and call the function on the custom calculation i the field.

in both ways nothing showed up!

[try67]

Change the value of one of the fields and then press Ctrl+J. Are there any error messages in the Console window that opens?

[responsible_sharpness98C6]

Yes , here it is :

[try67]

Sorry about that, it's a small bug in my code. I fixed it in the version above, and if you can just replace all instances of "fields" in it with "aFields" for it to work properly (hopefully)...

[try67]

Actually, there were a couple more issues (again with the parameter names), so just copy it from above...

[responsible_sharpness98C6]

what is the exact procedure ?

Should I put the calculation it in Document Javascript tool and call it from field or can I put the function and calling same in the customze calculation field ?

BR

[try67]

Either way should work. If you have more than one field that you want to use it for, though, then the doc-level approach is better.

[responsible_sharpness98C6]

It works now, but it doesn't ignore emty fields or Zero fields !

It takes into account and ruines the calcluation

[try67]

Change the last parameter to true.

[responsible_sharpness98C6]

Thank you That was amazing Help.

Have a nice day wish you . Hope other can use this helpful code as well.

BR