7 Replies Latest reply on Jul 10, 2015 2:11 AM by kehumphr

# Need help creating a custom script for a simple calculation

I am looking for a script that will allow me to take the average of several cells, but if the cell is blank, for it to be ignored.

For example, if I have five cells, and all five have numbers in them, I just need the total divided by 5.

But if I have five cells, and only four are filled in with numbers, then I want it to just be averaged by 4.

I think I explained it well enough, but let me know if you have other questions or need more info.

Cell 1Cell 2Cell 3Cell 4Cell 5Average
55347(5+5+3+4+7)/5=avg
5347(5+3+4+7)/4=avg

I don't want the blank cells to be calculated in (I don't want a zero averaged in), but I need a script that I can apply to several rows like the above.

• ###### 1. Re: Need help creating a custom script for a simple calculation

This is a generic custom calculation script that will do that for you:

```function calcAverage(fields) {
var total = 0;
var n = 0;
for (var i in fields) {
var f = this.getField(fields[i]);
if (f==null) {
console.println("Error! There's no field called: " + fields[i]);
continue;
}
if (f.valueAsString!="") {
total+=Number(f.valueAsString);
n++;
}
}
if (n==0) event.value = "";
else event.value = total/n;
}
```

You should place this code as a doc-level script, and then use it like this from your field (for example):

```calcAverage(["Cell1", "Cell2", "Cell3", "Cell4", "Cell5"]);
```
• ###### 2. Re: Need help creating a custom script for a simple calculation

I apologize but I am very very new when it comes to code. I fiddled around a bit and wasn't successful, so I assume I am doing something wrong.

The names of the text boxes I want averaged are "1-1" "1-3" "1-5" "1-7" "1-9" and the answer/code will be going into "DOR 1" (all minus the quotes).

Could you format with these names so I can see how it is supposed to work?

• ###### 3. Re: Need help creating a custom script for a simple calculation

As the custom calculation script of "DOR 1", enter:

calcAverage(["1-1", "1-3", "1-5", "1-7", "1-9"]);

• ###### 5. Re: Need help creating a custom script for a simple calculation

Wait a second, that simple version is doing the trick! Beautiful! Thank you so much!

What was the long form that you posted at the beginning if you don't mind me asking?

• ###### 6. Re: Need help creating a custom script for a simple calculation

No, you're not using it correctly. You should not change the first part of the code at all, just place it under Tools - JavaScript - Document JavaScripts.

Then place the second part of the code (the one line of code I provided) as the custom calculation script of your field.

• ###### 7. Re: Need help creating a custom script for a simple calculation

Ok I got it now. Awesome! Thank you so much for your help!