Skip to main content
Kimmeree
Kimmeree
Participant
March 19, 2022
Answered

Custom calculation in JavaScript help needed please

  • March 19, 2022
  • 1 reply
  • 1568 views

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.

This topic has been closed for replies.
Correct answer Nesa Nurani

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. 


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 = "";

1 reply

Nesa Nurani
Community Expert
Nesa NuraniCommunity Expert
Community Expert
March 19, 2022

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
KimmereeAuthor
Participant
March 19, 2022

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
Community Expert
Nesa NuraniCommunity Expert
Community Expert
March 19, 2022

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices