nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

LaurenceTait

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)) ......

Form 6.xlsx

All comments

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.

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

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.

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

Form 6.xlsx

Reuben

hi Laurence,

Thanks alot. This helps

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.

Form 6.xlsx

SusanWS_nysm

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.