Row and Column Analysis

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:

image

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:

image

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.

1 Like

Hello @Joseph_B,

I totally understand the frustration! I work with a lot of financial schedules and estimates which are typically (from legacy activities) setup in similar data schema.

As part of my work to make past information more flexible, I have “de-pivoted” / “flattened” a lot of excel tables into something that is less table and more row-focused.

This can be done automatically within excel prior to data import, and doing so allows for a lot more flexibility once you are inside Coda.

I know this isn’t exactly what you are asking, but it is one route that has made my life easier when approaching the same problem, thanks!

2 Likes

Thanks for the share. Appreciate the alternate approaches to this issue.

@Al_Chen_Coda If you don’t mind, tagging you for your expert insight into the intersection of Coda and Excel/Sheets.