How do i end my row with sum off all previous columns?

[Thierry_Dupont]

Hi,
New to Coda.
How do i create a last column which gives me the sum of all previous columns?
So i created number-type column, but can’t figure out how to get the formula right to make a sum of all the numbers in the previous 3 columns.
Can anyone please help as i can’t find it in the Help section of Coda.
Thank you.

[Marko_Petrovic]

You can use formula type for last column and write formula that will add up values of previous columns.

[joost_mineur]

Right click on the name of your column and use summarize with one of the available options. The value will appear under the row.

[Thierry_Dupont]

Thanks Joost, but thats not what i need.
I have 3 rows. 4 columns each. In the 4th column of each row i want to display the total (sum) of the 3 previous columns.

[Thierry_Dupont]

Thanks Marko. What i’m missing is the formula.

[joost_mineur]

type in a cell in the 4th column =sum(column name 1, column name 2, culumn name 3) and all cells in the 4th column will show the total of the fields to the left.

[Marko_Petrovic]

Is this what are you looking for?

Or I might have misunderstood what was your intention Unless you want sum of all previous cells which is totally different beast

[Thierry_Dupont]

Thanks a lot! That’s it!

[Thierry_Dupont]

Thanks a lot! That did it for me.

[Nikil_Ragav]

Is there a programmatic way to get all of the columns? I have 100 columns (I’m evaluating students’ progress on different dates, so I have a column for each date and a row for each person). I want to know their total progress, but typing in each column name manually into a formula is a no-go

[Nick_HE]

I’m afraid not. In Coda you need to model data in ways that are sometimes different than your spreadsheet instincts.

One way that you could model this data is with the following tables:

• Students (each row is a student). Columns:
  • Name
  • Student Number
  • Evaluations ( =StudentEvaluations.Filter(currentValue.Student = thisRow) )
  • Total Progress (=Evaluations.Score.sum())
• EvaluationDates (each row is a test). Columns:
  • Date
  • Evaluation Name
• StudentEvaluations (each row representing a certain student taking a certain evaluation)
  • Student (Lookup column to Students table)
  • Evaluation (Lookup column to EvaluationDates table)
  • Score

The StudentEvaluations table is what’s known in database land as a “join table”. It correlates records from two other tables, and usually adds a bit of extra info (in this case, the score; though you could also add other data like “special accommodation granted” checkbox or something).

If you have 30 students and 100 evaluations, there would be 300 rows in the StudentEvaluations table.

Now, this would take a bit of legwork to set up, but could be quite a flexible rig for you in the future. Main benefits:

• Automatic summing of any new evaluations
• Advanced filtering based on date ranges (want to see second-semester averages? September only?)
• Calculate total, mean, median, standard deviation for each evaluation (if you care)
• View all of a student’s evaluations in their own table (maybe easier than scrolling horizontally)
• Display a list of the students who have not completed a certain evaluation

Let me know if this sounds like something you’d be interested in and I can build you a sketch. In addition to the tables mentioned above, you would need to add the following to make it ergonomic to fill in the scores:

• A button that generates all the StudentEvaluation rows when you add a new evaluation
• Embedded table view in row popup, so that you can see all students listed and quickly enter their grades spreadsheet-style

This also sounds like the kind of thing @Scott_Collier-Weir might already have in his library somewhere

[Scott_Collier-Weir]

There is a way and I do in fact have a way to get all columns in a table programmatically

Haha thanks @Nick_HE for calling me out

Explain more of your context and use case and I’ll see if I can help you out!

But Nick is right - evaluating student progress by date using Columns as the controlling date value is not the Coda way to go.

I also am providing a free training and consultation session next Tuesday if you want to stop by! Happy to take a look at your doc then too and provide some direction towards restructuring in a more sustainable way.

See here for more details:

GetUnstuck | Pt.2 [FREE Training]

Hey there! tl;dr – GetUnstuck will be happening again! There’s a lot of interest and its a lot of fun! The next meeting will be held on Tuesday, March 8th at 9am (PST). If you’d like to sign-up to GetUnstuck on your Coda docs you can do so here → Sign-Up Sign up soon! We already have half the spots filled up. Curious what GetUnstuck is? In short, its a free one hour training session on Coda best practices, writing tricky formulas, designing your docs with visual aesthetics and intuit…

[Xyzor_Max]

@Scott_Collier-Weir

There is a way and I do in fact have a way to get all columns in a table programmatically

where can one get (or buy) this piece of deep coda magic, scott?

max

[Frank_Novak]

This is one of the difficulties I’m having right now with Coda is that Coda dictates the way you have to work in order for your work to be sustainable while using it. Evaluating student progress by date using Columns as the controlling date value is not the Coda way to go, but it may be Thierry’s way to go, for whatever reason.

I get that relational database power is wonderful, but even doing simple things with a Coda table can sometimes be so freaking frustrating. Right now, I am exploring ways to do my organization’s budget in Coda, but as of right now there’s NO WAY I’m going to attempt it. I’m sure it is mostly because I am too inept with Coda to set it up and manage it properly, but I also have a business to run, too, and I simply don’t have time to goof around with a program that won’t let me break out of the relational database format for a minute to put a sum formula in a row to add up three column cell values to the left.

I love the docs aspect of Coda, but tables are killing me right now and keeping me away from Coda as a more permanent Google replacement.

[joost_mineur]

Hello @Frank_Novak and @Nikil_Ragav ,

I understand your frustration(s), but I think we need a bit more nuance in defining the problems and the solutions.

1. Working with data is a bit of an art that many people can learn. But when the dataset gets larger and the required reports get more complicated, it can get tricky in a hurry.
2. Google sheets or excel are not easier to use than Coda (I would say it is the contrary), but if you have been using these packages for 10 years or longer, it takes some time and effort to adjust.
3. There are situations when excel has an advantage, for example when you have 200K rows or something like that. Or if the formulas get really complicated or specific.
4. In most day to day situations, Coda is a lot easier to user than Sheets or excel, but you have to be willing to
   a) learn working with Coda
   b) learn working with data.

Reading though the thread, I understand how easy it is to add a bunch of columns to the left, but in sheets or excel you have to adjust your formulas too when adding extra columns to the left. And then copy these formulas so many times for your entire sheet. And hope that things don’t get messed up because of deleted lines, references that don’t work anymore, etc. In Coda, I have to adjust only 1 (one) formula to accommodate for an extra row, that is not that complicated. And I agree, there is no formula that says add everything from A1 to ZZ1, , but …

Think about your data. If you need a formula like that, start thinking about your data structure. You probably need 2 tables in stead of one and all of a sudden, the problem is not that big anymore. The fact that you can do this so easily in excel doesn’t mean it is the right way of doing this. I am tempted to think that if you need a new column for every date, you are not using either of these programs in an optimal way.

And if you don’t have the time or desire to do this properly you have a choice: keep on doing things with tools you know (but eventually run into a dead end), or hire expertise, or take the time to learn it the hard way. About the last, take a subset of your data, build (with help of the community) a proper solution and than take your large dataset and see how nice your solution works.

And, most important, make a dummy doc you can share with the community. It is a lot easier to help if we are looking at something that resembles you doc, rather than guessing what you need.

Going back to the original problem:
I think your need:

1. a student table
2. an activity (dates) table
3. a data collection table

In tabel 3 you select for each row in column 1 the student, in column 2 the activity (date) and in column 3 the result/score or whatever data you are collecting.

Then you can make ANY kind of summary you want: per student, per date, high scores, avarages, you name it.

Please believe me that it should be done pretty much the same way in excel, but excel will let you get away with whatever you throw at it, until you don’t understand your own formulas anymore or you can’t get the results you may need some day.

Happy coding,
Greetings, Joost

[Nikil_Ragav]

I am familiar with join tables.

This is pretty close to what I currently have.

I have a

• Roster table (students)
• Assignment table (EvaluationDates)
• Student updates table (essentially your StudentEvaluations)
  • Each item is Student, Evaluation, Assignment answer, Status (in progress, stuck, feedback given, done)

What I tried to do is create a separate spreadsheet table, where each row is a student, each column is each assignment, and then for each column, I’m looking up their latest Status for that assignment from the Student updates table. I had to manually enter the assignment into each column formula.

^The idea is that spreadsheet view is easier for a teacher to view and track students’ progress.

I am interested in how you set up the embedded table view to see all students and enter their grades spreadsheet-style.

[Nikil_Ragav]

Re-reading what you are saying about the button, it sounds like you are only tracking one result to the assignment.

In my case, I want to see the change over time, so I am tracking in Status Updates join table (your StudentEvaluations) table every time the student status changes (e.g. if they go from In progress to Done, those are 2 separate entries.

In the spreadsheet-style table, I just take the Last() instance of a certain assignment so that I know their latest status.

[Nick_HE]

Ah ok great, you’re way ahead of me.

I don’t think I have a way to give you the full spreadsheet grid you’re looking for of all students and all assignments, BUT I can offer slices of that in both dimensions: a view of all students’ performance on one assignment, and a view of one student’s performance on all assignments. In both cases these would be editable (if you were entering scores). Hopefully these are needed at different times and the needed one can be selected when appropriate.

The way to do this is with detail views. Let’s talk about “one student’s performance across all assignments”

• One of the columns for the Student table should have a formula that lists all of the latest evaluations for that student (not just their scores, but the full row record from StudentUpdates).
  • I would calculate the ‘latest’ status inside the student updates table - a column called Is Latest that checks whether I’m the latest for this student/evaluation combo. I guess it would be like StudentUpdates.Filter(currentValue.Student = thisRow.Student AND currentValue.Evaluation = thisRow.Evaluation).Last() = thisRow
  • Then in the Student table’s Latest Evaluations column, formula would be StudentUpdates.Filter(currentValue.Student = thisRow AND isLatest)
• Pop open a row of the Roster table to see a Detail view (I like to let users do this with a button, formula Activate(thisRow))
• Edit the layout of this detail view, and you can choose to display the Latest Evaluations column as an embedded table (you can show just 2 columns in this embedded table if you want - the evaluation and their score - but you could also show other columns like their answer too which could be cool)

Similarly for seeing all students’ results on a given assignment, you’d

• Pop open the Assignment table row
• Display a table view of a “Student Scores” column, which would be a filtered list of all latest student scores for this evaluation

Once in a pop-up detail view, you can navigate to next/previous student or evaluation with the little arrow buttons, or close the popup and examine a different row.

Edit: Oh and what I meant about the button was, when you add a new evaluation, how do you go about populating all the rows in the join table (one for each student)? Or even more complex, the way you have it, adding rows to the join table for each stage of each evaluation for each student? Interested to see how you’re handling that (my head goes to buttons but haven’t thought through the whole interface).