Aggregating scores when one of the options is text

Hello again,

Hope everyone's been doing well.

Quick Question. If you notice in the attached sheet, you will find a whole bunch of scoring options given for a question. One of them is "Unattempted". Now if I want to aggregate the scores at the end of the form, then this unattempted is throwing me off. Any ideas how to work around the same?

I had made another version that was supposed to have loops but that seems to be giving me an indexed repeat error. This may give a view on why I was having the Count of records calculated and how that was helping build average

Any reason not to give a score of 0 for 'Unattempted' ? (ie make name = 0 in your choices_* lists)

[BTW, its not recommended to publically post forms containing personally identifiable info, eg names]

1 Like

Hey thanks Xiph, my bad, I really should have masked it. I will take it down. rightaway

So I want to track 0s and unattempts separately. 0 is where the person has attempted and made a mistake, while unattempted is one where someone has not even made an attempt. Its a wider study of motivation to basic awareness vs. being incorrect at that point.

If I assign it 0, then in my output file I am not able to differentiate the ones that are really marked as 0 the ones marked as unattempted. Any ideas what j could around this? Can "unattempted" be ignored during numeric aggregation? Or can it be given some other kind of a demarcation? Also what might I be doing wrong w my index repeat? I will repost the file w the details masked, but since you have seen the file, I am hoping you might have had some thoughts

Instead of

${q1} + ${q2} + ...

you could have

if(${q1}!='unattempted', ${q1}, 0) + if(${q2}!='unattempted', ${q2}, 0) + ...

which will leave your 'unattempted' results alone (eg for later analysis) but will substitute 0 when adding them to the sum


Like always, very grateful. This is absolutely great. You have always been an immense life saver :slight_smile:

In the other sheet where I am trying a repeat function, I seem to encounter a "Indexed Repeat Error".

I remember we have spoken about this before, there is a whole Java Rosa element, something to do with \n\n and I tried going back on all our conversations, but not able to hit the element right.

Would you know what is wrong with this file?

(Pls ignore the aggregation formulae, I will make the edits you have suggested into the files) SS_Student_List_2018_19_test.xlsx (62.9 KB)

You have always been a life saver. I had thought of applying the same, rather I was planning to convert all scores into that kind of a breakout before summing them.

But was so unsure if by doing this, I will end up taking away values of other entries. I was also making the mistake of doing that at say a Q1 level and not at Q1A level.

Now that I see your formulae written out, this seems so much simple.

Thank you so much. I can't unsee how simple this looks now.

So, this formulae isnt working. Its throwing up an error. Tried quite a few times and way, looked for issues, can't trace a thing. Tried multiple ways. Issue persists.

if(${Q1A}!='unattempted', ${Q1A}, 0})
if(${Q1B}!='unattempted', ${Q1B}, 0))
number(${TQ1A} + (${TQ1B})

number(if(${Q2A}!='unattempted', ${Q2A}, 0} + if(${Q2B}!='unattempted', ${Q2B}, 0) + if(${Q2C}!='unattempted', ${Q2C}, 0) + if(${Q2D}!='unattempted', ${Q2D}, 0))

Try this:

number(if(${Q2A}!='unattempted', ${Q2A}, 0) + if(${Q2B}!='unattempted', ${Q2B}, 0) + if(${Q2C}!='unattempted', ${Q2C}, 0) + if(${Q2D}!='unattempted', ${Q2D}, 0))

Note, be very careful with your '}' vs ')' ...

Here's a form to try that parses fine:

foo.xls (19.5 KB)

Thank you! Can't believe I missed that. :slight_smile:

Though it still doesnt aggregate. Wherever I choose "unattempted" it does not add up

In the example you have shared, "Unattempted" is a text based entry. In mine its a selection picked up. You think I should try the same formulae without the quote ?

Trying if its case sensitive, will keep you posted.

It is case sensitive. It is working now. Thanks a lot. :slight_smile: ...

Any idea on the indexed repeat would be very helpful!

I owe you a lot Xiph

if you are having other problems - eg indexed repeat error - could you please open a new topic and perhaps post a (preferably) simplified form that exposes the error to debug. thnx

Thank you @Xiphware, I will do so.