Highlighted

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

New Here ,
Apr 23, 2019

Copied

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.

TOPICS
PDF forms, Windows

Views

1.6K

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

New Here ,
Apr 23, 2019

Copied

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.

TOPICS
PDF forms, Windows

Views

1.6K

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
21 Replies 21
Most Valuable Participant ,
Apr 23, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Apr 23, 2019

Copied

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;

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Most Valuable Participant ,
Apr 23, 2019

Copied

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.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Apr 23, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 2
Most Valuable Participant ,
Apr 23, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 1
New Here ,
Apr 23, 2019

Copied

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?

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

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).

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
LATEST
New Here ,
Oct 31, 2020

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 31, 2020 0
New Here ,
Apr 23, 2019

Copied

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?

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

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

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Apr 23, 2019

Copied

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.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

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?

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

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"?

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

Hi Thom Parker

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?

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

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?

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Apr 23, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
New Here ,
Apr 23, 2019

Copied

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.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 23, 2019 0
Apr 24, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 24, 2019 0
New Here ,
Apr 24, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 24, 2019 0
Apr 24, 2019

Copied

Check the console of Acrobat for errors.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 24, 2019 0
Apr 24, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 24, 2019 0
Apr 24, 2019

Copied

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

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 24, 2019 0
Resources