Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

How do I calculate the average of ONLY the populated fields?

Avatar

Level 1

I'm working on an Adobe form to streamline the process of summarizing course evaluations.  To protect the anonymity of the students, instructors with 10 or fewer students are given a typed summary of the evaluation results.  On the first half of the evaluation, the students are asked to rate a series of statements from 1-5.  Three of these are highlighted on the summary form with the average response. 

I've already set up the form with 10 unique boxes for each question and an 11th box which calculates the average.  Each field is named according to the question number and the student's "number" (I arbitrarily gave each student a number so that each field would be unique):

i.e. #1. Questionquestionquestion.  Q1S1  Q1S2  Q1S3  Q1S4  Q1S5  Q1S6  Q1S7  Q1S8  Q1S9  Q1S10  1AVG

The problem, of course, is that no matter how many students were actually in the class, the AVG field will always be calculated based on a group of 10...which isn't accurate or fair to the instructor.  I thought about just changing the form and removing the unused fields from the equation each time I make up a new summary, but the point of creating a form was to make it as quick and easy as possible to bang these things out... Plus, some of my coworkers might be using the form from time to time and I'd have to explain what they have to do and if they don't have Adobe Acrobat then they can't actually make the changes and blah blah blah...it just gets ridiculous really quickly!

So anyway, I tried reading some other posts for similar questions in an attempt to figure out a custom calculation script for myself, but I just couldn't focus on it.

I was hoping someone could explain how to write a custom calculation script that will omit any fields which are left blank... Or, even better...is anyone willing to write it for me?  At least just an example of the first guy, cause I could probably figure out how to get all the other ones from there.

Thanks.

1 Reply

Avatar

Level 7

In formcalc the function Avg will calculate the average of only the fields with a value in them. So you would would put in the calcuate event of the average field:

$ = Avg(Q1S1,Q1S2,Q1S3,Q1S4, etc)