How to implement this formula in Excel Form (Survey123)

Question

=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 · Answer

Hi George,

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

GeorgeSuresh · Answer

Thanks James, for the example.

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

JamesTedrick · Answer

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.

Conditional_Average.xlsx

topo5.jpg