I am gathering data on a Google Sheet that I’m syncing to Coda. The data is ratings for various Exercises that can range from 80 to 140 responses. Each Exercise can have any number of Respondents.
In order to make a comparison between the exercises and make a chart that allows a fair comparison of progression I want to normalise the data (eg in an 80 response exercise each response is 1/80th of all responses, in a 140 response exercise then it’s 1/140th).
The data comes in with 140 columns for the responses (columns are blank if it does not use the full 140) along with columns for the Exercise and Respondent number.
In order to create the normalised chart I have to manually transpose the data so that I have columns for Exercise, Respondent and a calculated column of Response proportion. (ie 1/80, 1/140 etc). I assume I need to have a Button to do it rather than be automatic but I’d like to automate it as much as possible.
The Coda sheet below shows an example of the input data (with only 14 columns) and the desired outcome data and chart.
Any suggestions on how to go about doing this? My main concern is that in the example I have 4 respondents but what to do if I gain a fifth and have the table expand automatically. Or I could just put in a lot of columns and review to make sure that I am not going over.
Thanks Jake but unfortunately I have to collect the data the way I do (ie non-Coda) and use a webhook to get it here as Coda can’t do what I need it to do. I know Coda does forms but I simplified the collection process for this example.
Is there any way you can have a different output format? The main issue seems to be that 1 response = 1 column… if you can somehow not have this it would make your life a lot simpler. The quickest workaround would be to create a summary reporting out of your source table. Now I assume you know all of this probably…
For this kind of custom work, I’d probably recommend python. Is there anyway you can change your chart output so that you can have an easier data structure to work with?