Help Needed: Field Editor Formula to Dynamically Adjust Average When Questions Are Skipped | XM Community
Skip to main content

Hi everyone,

I’ve been struggling with this issue for hours and could really use help from someone who’s figured this out or knows if it’s even possible.

I need help with a Field Editor formula in the Data & Analysis tab. I’m averaging multiple items under a construct (for example, 3 questions for “Self-Stigma”) and want the average to calculate correctly even if a participant skips one of the items. If someone skips a question, the formula should automatically adjust the denominator and average only the responses that were actually answered.

Here’s what I tested:

  • I used a custom formula like this:
    (Q1 + Q2 + Q3) / 3

  • When I unchecked the box labeled “Treat empty values as 0,” the entire formula returned a blank cell when even one of the questions was unanswered — no average was shown.

  • When I checked the box, the formula did calculate an average, but it was wrong.
    For example, if the participant answered two of the three items with a total of 9 and skipped the third, it still divided by 3, returning an average of 3, when the correct average should be 4.5 (i.e., 9 divided by 2). So it treated the skipped item as a zero, which isn’t what I want.

I’ve tried every workaround I could find, including IF-based formulas that check if a value is blank, but no matter how I write it, Qualtrics returns an error and won’t accept the syntax. I even tried several versions recommended by Qualtrics AI, including this structure:

(Q1 + Q2 + Q3) / (IF(Q1 != "", 1, 0) + IF(Q2 != "", 1, 0) + IF(Q3 != "", 1, 0))

Still didn’t work — the formula wasn’t accepted in the Field Editor.

What I’m looking for:

  • A way to dynamically calculate the average only from answered questions.

  • A setup that works within the Field Editor (no post-export fixes).

  • A method that does not use Scoring Categories, since those only give totals, not averages.

Has anyone successfully set up a Field Editor formula that excludes skipped responses from both the numerator and denominator? Or is this a known limitation?

I’m completely stuck and think it may require input from a Qualtrics programmer. Any help or insight would be hugely appreciated.

Thanks in advance,
Dan

Hi,

 

This is a limitation. Field editor formula wont change dynamically based on the inputs. It will calculate based on the formula written.

Two options,

  1. if the survey is not launched yet, you can calculate all these in the survey flow.
  2. If its done, you have do the calculation after exporting the file

               

Scoring will allow you to do that, but you need to add an ED in your survey flow to capture it:

 

Thank you! I will try that route. Much Appreciated

Leave a Reply


Terms & Conditions
OSZAR »