I have seen and participated in discussions about similar topics in the past, but want to hit this question directly. When you receive a classic excel-styled table where you want to run an analysis on the rows and the columns, what’s the most straightforward way to do so in Coda?
For example, if I have a table of fruits and people, and each person scores each fruit, what is the easiest way to run an analysis on the scores of each individual fruit as well as each individual person.
When importing such a fruit-person table into Coda, I may have this:
Running analysis on any particular fruit is straightforward. e.g. =FruitScore.Apple.Average() tells me that the average score for Apples is 3.6.
The less-straightforward part is if I want to also analyze how, for example, Bill scored the Fruits. There are of course ways to analyze Bill’s scoring, but they are not as straightforward as a column analysis. The formulas become significantly longer and/or more complex.
I recognize that this is rooted in the design of Coda, and one of the distinctions between Coda and Excel/Sheets. I’ve read many polite and condescending comments to this end. But, it would be great to have an intuitive solution.
The most obvious solution that comes to my mind would be a transpose; i.e. make a new (non-view/non-connected) table with the columns and rows swapped. It would open the door to data integrity issues, but baby steps.
You can currently transpose data by exporting the data to Excel/Sheets and transposing there, but I advocate for a native solution. You can also manually create a transposed table, but again that seems like something you shouldn’t have to do.
Another approach is to create a new column that averages out the values in each row. For example:
While this approach is helpful, it does not account for any new fruit columns that may be added later.
Big fan and advocate of Coda, and interested to hear other thoughts and approaches.