Calculating the Average value of varied number of items.

[Reuben]

hello ArcGIS Survey 123 Users,

I had a question in my survey that requires calculating the Average Sum of 9 items like: (Item 1...Item 9) div 9.

In some cases, there were only 8 items instead of 9 items. In such case, my pre-calculation of  total Average formular will not automatically generate the required value (where I had to manually insert.).

Is there a way to calculate the Total Average value of a varied numbers of items? 

thanks 

[LaurenceTait]

You need to tell us where you are getting this list of items from. Is it being pulled from a csv?  Recorded in a repeat? Answers to a select_multiple question? There are at least 2 dozen ways you could be generating a variable list of items, and the method for calculating an average is different for most of them.

[SusanWS_nysm]

Dear Lawrence,

Thank you very much for answering Reuben's question - very helpful for a survey I'm currently working on (and trying to anticipate the "problem areas.") In a similar vein, what if the items did occur in a repeat, but the calculated field (based on the the sum of fields within the repeat) was in the main table, AND, there were no entries in the repeat table, but the default values were set to zero?

Here's a concrete example that might explain better what I'm asking:

The main table/survey represents a container and certain attributes. The repeat/related table references objects within the container and certain attributes. The calculated field - in the main table -- represents a composite "score" indicating a general quality of the objects (based on the answers in the repeat). Some containers are empty, however, and would not have any records/entries in the repeat/related table. Even if the values in the repeat fields are set to "0," would the lack of any records mess with the calculated field results in the main table/survey? If so, can you suggest a workaround?

Many thanks in advance!

Susan

[LaurenceTait]

That will work fine. So long as you don't give the repeat a relevant condition, it will always have at least one instance. If that instance is empty them mean will simply be 0. 

[SusanWS_nysm]

Thank you very much, Laurence! All the best, Susan

[Reuben]

hi Laurence,

The values were pulled from the CSV

[LaurenceTait]

Can you provide some details? Pulled by select_one_from file? By by geolocation? Pulled by cascading select?

Unless this is top secret, it will be a lot faster and easier if you provide enough details to understand what you are trying to do and how you are trying to do it. Ideally upload the xlsx form.

[Reuben]

hi Laurence,

So this situation is that, I want to automatically generate the Average scores of 9 items.

The scores for each of  the 9 items was calculated from the survey itself. I want to add-up those scores

and get the average score of the 9 items. This is my automated formular:

(${Score_item1} + ..... + ${Score_item9}) div 9. It work only when all the 9 items' score are available.

If I only have the scores of 8 items instead of 9, this formular would not generate the average score.

This is the Excel arrangement of my Survey: 

Hope its clear

[LaurenceTait · Accepted solution]

Oh, well that's easy.

Write a formula to convert each answer to 1 if it has been answered, and 0 if it hasn't. Add the result. that gives you the number of answered questions.

Sum the answer for each question. Divide A by B.

Note that this requires that you set the default of each answer to "0". You can't sum a null field. If there is a reason that you can't set the default to null, you will need to use a variant of the formula used to calculate how many questions have been answered : 

(if(${score_1} > 0, ${score_1} , 0))  + (if(${score_2} > 0, ${score_2} , 0)) ......

[Reuben]

hi Laurence,

Thanks alot. This helps