Using Rollup for specific values in a column

Hey guys, I have a question and I really hope you can help me fixing it.

As you can see in my picture, I have a list with different dimensions, subdimensions and statements. The main goal of my app is, that in the end the client can evaluate himself on the given statements on a scale of 1 to 5.

After the client has answered every statement, I would like to give him his overall result (for example: X/5) but also his results for each dimension (1. Strategy and Management, 2. Customer, 3. Culture, Organization and People, Operations, Technology and Innovation). For now each dimension has just two statements but the final app will contain many more statements. How can I calculate the average result for each statement. I checked out “Rollup” but that seems to work just for a whole column.

I highly appreciate your help. :v:

Cheers from Berlin, :de:
Aaron

I think this requires rollup over user-specific columns, which is not available as of now.

Would a form to submit all those answers back into the Sheet work for your case?

Thanks for your answer.

Not really, is there no option to exclude the specific values from the answer column and to copy them to another column in the sheet. By doing so, I should be able to calculate the average for each column again right? I already tried “If --> then --> else” function, so "If dimension contains Strategy and Management then show me the anwer) and that worked but "If–> Then–> Else does not allow me to calculate the average.

That average needs to happen using a rollup, but as of now rollup isn’t available for user-specific columns. I hope it will be available soon though.

1 Like

Yeah me too, I can not believe that there is no way around it. :frowning:

I’m assuming you only need the average for one user, and not the average of multiple users.

Create a relation linking dimension to dimension in the same sheet.
Create to rollup columns that each use the relation, but use one for count and one for sum.
Create a math column that divides the sum by the count.

This should give you the average for each dimension for each user, since you are already using a user specific column for the answers.

2 Likes

Hi Jeff, thanks for your answer. It already helped but now, I see the average of each dimension in my app to often (look at the picture). I just want to show the average of each dimension once.

What can I do about this problem?

Thanks in advance :slight_smile:
Aaron

You will need a separate sheet that only lists the dimensions and use that for your results tab. There you can create a single relation to your existing sheet and a lookup to pull back the average. If you also want to be able to open each dimension and see each related question, you can also create a similar multiple relation to use for an inline list within the dimension details.

1 Like

Hi Jeff,

thank you so much. Made my day!!

cheers
Aaron

2 Likes