How to I set formula to calculate average of only filled score out of 15 fields

[Rach889988]

Hi guys,

I need help with finding the formula how to set the average for only fields which are only filled.

The current Quanlitative score takes in all 15 averages.

I only want fields which are filled to count the average and not including the empty fields.

I would also appreciate if you could explain how to edit the formula if i need to expand the fields in future and only to take average of those filled.

[try67]

I posted code that allows you to do it. Try searching for "calcAverage" on these forums, especially the JavaScript one.

[Thom Parker]

Since your fields using a consistent naming pattern, the code can be done as a simple loop.

The code in the loop generates the field name and gets the field value. Then if it is not empty and a number, a count is incremented and the value added to the sum.

var nSum = 0; nCnt = 0;

for(var i=1;i<=15;i++)

{

    var nVal = this.getField("Qual" + i + "m").value;

    if(!/^\s*$/.test(nVal) && !isNaN(nVal))

    {

        nCnt++;

        nSum += nVal;

     }

}

var nAve = nSum/nCnt;

[try67]

Your code is missing assigning the final result to the event's value, as well as checking that nCnt is not 0, which would cause an error.

[Thom Parker]

Yes, of course. Details, Details.

var nSum = 0; nCnt = 0;

for(var i=1;i<=15;i++)

{

    var nVal = this.getField("Qual" + i + "m").value;

    if(!/^\s*$/.test(nVal) && !isNaN(nVal))

    {

        nCnt++;

        nSum += nVal;

     }

}

event.value = (nCnt > 0)?nSum/nCnt:"NA";

[try67]

Yes, where the devil lies... Now it's perfect! 🙂

[Rach889988]

Hi try67 & Thom Parker

Do you know what caused this error message and the fact that it does not refresh when I reset for form to blank &

only calculate average score for filled column under Manager's Rating column for both Qualitative and Quantitative?

Below screengrab for your advise.

*************************************************************************************************************************

This screengrab shows everytime any input of new value will popped up a error message on both qualitative and quantitative column (perhaps i copied the same script hence the same response).

*************************************************************************************************************************

This is the screengrab which i had edited the same code with a change of "Qual" to "Quan"; it works to count average but it does not count properly when i reset the form to blank value in Manager's Rating column (not required to count average for Employee's Rating column).

*************************************************************************************************************************

[Györgyi98BC]

Thanks a lot, Thom, worked well for me, and saved my life. Ok maybe just 2 days of it, but still! 🙂 

[Rach889988]

Thank you both for your help.  It works!!!!!

one more question.

May I know if there is anything i should change to the javascript if i needed to reduce the number of fields from 15 to 10 or expand my fields from 15 to 20?

[Rach889988]

Hi guys

2 questions.

#1-for Qualitative Score (which you had provided with Javascript)

Strangely, it works when i have values and when there is no value. 

May I know if I am missing anything on the javascript?

the calculation (score) does not change when the field is change to empty value.

#2 for Quantitative score

I copied the same javascript, changing the "Qual" to "Quan".  It works but everytime i change the value under Quantitative fields, it will prompt this error message shown below, is there any further edit i should do to the original javascript?

***********************************************************

This is what i had copied for Quantitative Score:

***********************************************************

var nSum = 0; nCnt = 0;

for(var i=1;i<=15;i++)

{

    var nVal = this.getField("Quan" + i + "m").value;

    if(!/^\s*$/.test(nVal) && !isNaN(nVal))

    {

        nCnt++;

        nSum += nVal;

     }

}

event.value = (nCnt > 0)?nSum/nCnt:"NA";

***********************************************************

[Thom Parker]

Note the numbers in the loop. These exactly match the numbers of the fields.

for(var i=1 ; i<=15 ; i++)

Do not use any formatting for the fields. The error message is built into the Number format. You don't need it any way because the user is not entering numbers.

You are using Dropdown fields for the number selection? The actual value of the field does not normally change when the user makes a selection. It changes when they move the focus off the field. If you want the change to happen right away, like most people do, then you have to select the "Commit value immediately" option on the field properties dialog.

[Rach889988]

Hi Thom Parker

Pardon me as this is the first time i am using javascript and does not know how it works except knowing that this is a command that Adode knows what we want them to do.

For the ratings, it is a dropdown menu.  The average scoring is not dropdown (text field).

I could not find the "Commit Value Immediately" in the text field properties.  Can you please guide me?

[Rach889988]

Hi Thom Parker

#1

I found "Commit value immediately" and it did change the value as one select value from dropdown menu.  However, I found that any change in the value will gives this error message.  How can we fix this (without showing)?

#2 - Value does not change to nil/zero when all fields are reset to no value.  How can we fix this?

#3 - I would need the same average and reset javascript for quantitative scoring (next section).  Can i simply copy and paste the javascript for quaNtative and change the "Qual" to "Quan"?

Really appreciate your advise on this.

[Rach889988]

Hi Thom Parker

I just reread your message and would like to clarify.

I do not have any formatting in the dropdown menu for the ratings but i do have formatting in the average score (for both quantitative and qualitative scoring) to round up/down to whole number.  Will this affect the javascript?

[Rach889988]

Ok we have new error.  I have a checklist with checkbox field which works without any issue.  I just tried out my form and now it strangely popped up error message even tough i did not do anything on this page.

Has this to do with the javascript?

[Bernd Alheit]

You will get the message because "NA" is not a number.

[Rach889988]

Thanks, Bernd.

Are you refering to the average or the check box?

So how can i avoid this error message? 

It can get confusing for my user.

[Thom Parker]

Change this line in the script.

event.value = (nCnt > 0)?nSum/nCnt:0;

The error is caused by the formatting on the Quantitative Score field. The script change changes the value entered into the field by the calculation to a 0, if no fields are filled.

The fact is you don't need to use any of the preset formatting options. The best way to format the number is to do it in the calculation.

This code is for calculation when no formatting options are applied in the Quantitative Score field:

event.value = (nCnt > 0)?util.printf("%0.2f",nSum/nCnt):"NA";

[Rach889988]

Hi Thom Parker

Not sure what went wrong.  Tried both lines in two different fields and nothing shows up (blank).

#1-Qualitative Score with Formatting to whole numbers

#2-Quantitative Score with No Formatting

#3-No value/average showing on both average scores

[Bernd Alheit]

Check the console of Acrobat for errors.

[Thom Parker]

Yes, Check the console window for errors.  There's something going on.

Just in general, it's best to develop scripts in isolation, meaning never use any formatting until you know the script is working. I would suggest removing all formmatting on all fields involved in this process

[Thom Parker]

A change in the check boxes causes the calculation script to rerun. It's the same error.