How to implement this formula in Excel Form (Survey123)

[GeorgeSuresh]

=AVERAGEIF(B25:B29,"<>NA")

I understand that Excel Form is NOT Excel Spreadsheet. (This works beautifully in Excel Spreadsheet.)

However, my customer would like this implemented in the Survey123 app that I'm developing.

How would I go about solving this issue?

Tags: survey123 numbers‌ #survey123 #Excel form

[JamesTedrick]

Hi George,

I assume that the value being averaged is in a repeat. This can be done with at least 1 hidden question, though possibly 2 are required depending on the form layout.

The first question needed is a calculate question to store whether this repeat should be calculated or not.  This can be done with the following function:

if(${q} != "NA", 1, 0)

This will store 0 if the value is NA, and 1 otherwise.  A sum() function outside the repeat will then count the number of answers that are not NA

if the question with the values being stored is a select_one question, you can make the "NA" value 0 - a zero value will not impact the average calculation and you can then do sum(${q}/sum(${<COUNTING_QUESTION>).  If the question is not a select_one, you would need to have a second calculate question to store the values that should be used in the average; it will need the following formula:

if(${q} != "NA", ${value_question}, 0)

One thing to note in constructing these is to set the bind::type column to int or decimal (depending on the value type) so that formulas treat them as numeric values (instead of text values).

[GeorgeSuresh]

Hi James,

Thanks so much for your prompt response. Much appreciated!

An example would greatly help enhance my understanding of your elegant solution (it is currently hard for me to comprehend to be useful).

To give you additional info,

The value being averaged is NOT in a repeat, since the question being asked has a different text in the question. (Do you suggest that I try a 'repeat' feature?)

However, it is a 'select_one' question. The answer choices are: NA, 0, 1, 2, 3

[JamesTedrick]

Hi George,

Find an example with both attached.  Whether to use repeats or not depends on the survey design - if the questions are asking about scores in distinct categories, repeats may not make sense.  If they are asking about multiple observations of the same property, repeats would make sense.

A couple of issues complicate matters slightly:

• select_one questions cannot receive a bind::type value, so the values must be manually converted to numbers as part of the calculation.  In the separate fields version, this is done in the averaging function.  In the repeat version, this is done with a second hidden field
• Given that 0 is a valid answer, you would need to have a second, hidden question as described in my first response.

[GeorgeSuresh]

Thanks James, for the example.

In the example with 'no repeat', how do you avoid a possible 'Divide by Zero'?

[JamesTedrick]

Hi George, 

You could wrap the divisor in an if() statement checking their sum for 0 and instead place a 1.