Custom calculation in JavaScript help needed please

[Kimmeree]

Hi. I am taking an Excel sheet and converting it into a PDF form, but I am having a hard time figuring it out since I have never done it before. I am not super familiar with JavaScript so I haven't been able to figure the right code for it. This is the formula from Excel: =IF(COUNTIF(F19:F23,0)=0,AVERAGE(F19:F23),"")  This is the formula in cell F24. This means that you take the average of the range of F19-F23 unless it is zero or blank, then it returns a blank cell. Correct me if I am wrong. I think that I have to make an array of the fields Q4i1-Q4i5 and look for any zeros to automatically fill in the AvgQ4Score field. Is this correct? If so, how do I do that with JS? I tried a few things, but it is not working. 

Here is some of the different code in the console, I am just not sure how to tie things together or if I am even going in the right direction:

var aNumFields = new Array("Q4i1", "Q4i2", "Q4i3","Q4i4","Q4i5");
myAverageFunction(aNumFields);

function myAverageFunction(aNumFields)
{
// n = number of fields that have a numerical value
var n=0, sum = 0;
for ( var i=0; i<aNumFields.length; i++) {
var v = this.getField(aNumFields[i]).value;
if ( v != "" ) {
n++;
sum += v;
}
}
if ( n == 0 ) event.value = "";
else event.value = s

//this was just to try to see if I could get anything at all to work, which it did

event.value = ( this.getField("Q4i1").value + this.getField("Q4i2").value + this.getField("Q4i3").value + this.getField("Q4i4").value + this.getField("Q4i5").value )/5; 

if(this.getField("Q4i4").value = 0)
event.value="";  

I appreciate any help! Thanks. I did attach a picture that has fields Q4i1-Q4i5 that average out to the AvgQ4Score field on the bottom, hopefully it is easy to read.

[Nesa Nurani]

See if this is what you want:

var avg = 0;
var sum = 0;
for(var i=1; i<=5; i++){
if(this.getField("Q4i"+i).valueAsString != "" && !isNaN(this.getField("Q4i"+i).value)){
avg++;
sum += Number(this.getField("Q4i"+i).value);}}
if(avg != 0)
event.value = sum/avg;
else
event.value = "";

[Kimmeree]

Thanks for taking the time to respond! This is almost right, but the "AvgQ4Score" field isn't blank when there is a zero in the array, it just averages it out. Is there a way to fix that? 

[Nesa Nurani]

You probably won't be entering text into those fields anyway so just replace this part:

!isNaN(this.getField("Q4i"+i).value)

with this:

Number(this.getField("Q4i"+i).value)!= 0

[Kimmeree]

The fields Q4i1 - Q4i5 do actually change. Each one will potentially have a different number (it is a score of a test). I need the AvgQ4Score field to calculate the average of Q4i1-Q4i5 unless there is a zero or left blank. If left blank, then there will still be an average score in the AvgQ4Score field, but if a zero is entered, then the AvgQ4Score should be blank. The jpg that I sent in the last response shows that I put a zero in the Q4i4 field, but then the AvgQ4Score is 80. I need it (the AvgQ4Score field) to show up as a blank field, not 80, and I am not sure if that is possible or not. I did what you said in the previous response, and it does make the AvgQ4Score 0 when any of the fields have zero in it, but I need it to average things out if there are no zeros in the fields and if there are any fields left blank. I hope this makes sense.

Thanks for all your help thus far. It is really useful. 

[Nesa Nurani · Accepted solution]

Try this:

var avg = 0;
var sum = 0;
var x = 0;
for(var i=1; i<=5; i++){
if(typeof this.getField("Q4i"+i).value == "number" && Number(this.getField("Q4i"+i).value)== 0)x++;
if(this.getField("Q4i"+i).valueAsString != ""){
avg++;
sum += Number(this.getField("Q4i"+i).value);}}
if(avg != 0 && x==0)
event.value = sum/avg;
else
event.value = "";

[Kimmeree]

It worked!

Thank you so much for taking the time to help me out!