Creating a Win-Loss Table, Coda vs Excel

#1

I am really enjoying Coda, and can’t wait to see what the future holds. One item I would appreciate insight on is how to create a head to head win/loss table in Coda (i.e. show each team’s record vs each other team). In Excel/Sheets, this was quite simple; you would list all the teams in one column, and then list them again along the top row. You could then fill in the corresponding records, and have a table that looks like this:

image

This is certainly doable in Coda, but my approach feels quite clunky and much less useful then on these other platforms. For clarity, each Team’s schedule and results are contained in other tables, so the need to reference each team to automatically fill-in records is critical.

One approach I tried was having Teams in a single column and again the column names for the other rows, but this is not ideal, since I will typically want to reference a Team to dynamically generate this data, and having it as a column name makes that difficult (no INDIRECT function equivalent).

Another approach is similar to the one above, but having the Teams along the top listed in the first row, as seen below. The problem here is that Coda does not operate on a cell basis, so trying to reference these Teams to generate the data would be less than ideal.

One viable option is to list every opponent for each team as the row items, then had a second column for the Record. This works for making the calculations, although it is not as pretty to look at:

There are table-less approaches, but those don’t quite hit what I am envisioning, as ideally you would see each team’s record against each other team in a tabular format.

Building one-off examples manually is fine, but if everything is to be dynamic (e.g. different people could have different leagues with different Team names), then balancing UI and with the ability to calculate is tricky. Appreciate any insights, ideas, or approaches.

0 Likes

#2

Hmm, I’m not sure I understand the goal of the output. It’s looking up games played between Team B and Team C (for example) and listing them next to each other (Team B Wins - Team A Wins)?

0 Likes

#3

The goal is to create a table like the first one I listed from Excel/Sheets. i.e. Wherever teams intersects you list the head to head record of the teams (e.g. Row Team A x Column Team B would show the win-loss record of Team A vs Team B)

0 Likes

#4

Ah, gotcha. Yeah, you’re stuck in excel brain. Think of this as a database, not a spreadsheet, if that helps at all. you should have no rows that act as labels. Only columns. I’ll tinker with it and see if I can help at all

1 Like

#5

Maybe this will help:
https://coda.io/d/Win-Loss_dLqulSn8CvX/Section-1_suWWD#_luBw0
Wrote this in there, but I’ll copy it here so other readers can potentially find it:

Essentially, you shouldn’t be trying to do a two dimensional chart.

The disadvantage of Coda is that doing 2-D things in the way you’re trying to do it is not possible (at least not in the excel brain way). The advantage is that doing things of any dimension is equally hard. So a 40 dimensional version of this chart will be just as difficult to represent (i.e. 40 teams playing one another) as the one with only 2 teams. This is because it doesn’t make you to think in excel way, instead it lets you use tables. Tables are more extensible.

The central thing to get is this: the only attributes that exist are columns. Think in terms of only columns to solve the problem

It’s a hard transition. So frustration is inevitable. But this is a great toy-problem to help you to learn the difference.

0 Likes

#6

Hey there!
I would suggest 3 visible columns. One with a Home Team (that would be a lookup or a lookup select list from a Team table), another one called Opponent (same type of column), and the last would be score. You should have also two other columns called Home Score and Opponent Score. You could fill those values with a button.
The score column should have a concatenate formula like =Concatenate(Home Score, " - ", Opponent Score).

Then you could group by Home Team on the left for the sake of clarity.

But actually, there are many ways to make a very nice Win-Loss table!

1 Like

#7

@tomavatars Thank you! This gave me an idea. Took what you said and went one step further:

I grouped along the side AND along the top. This allowed me to permit the calculations in Coda style while re-creating the Excel/Sheet visual of the table.

Aside, that is close to the formula I had been using for filling in the row.

2 Likes

#8

Appreciate it, but I am aware of these differences between the products, which is why I am trying to figure out how to transform what is needed into Coda.

Also, thanks for putting something together, however, I cannot access the document you linked. May need to make it public or grants access upon request?

0 Likes

#9

I also thought of the things like this. Grouping can be a powerful feature. Still need some polishing but it can make great tables.

I had another idea which I find visually fun.
You could add teams and logo in another table.
Then for your winloss table, column in order:

  1. Team Home logo
  2. Team Home Name
  3. Score Team Home
  4. < > =
  5. Score Team Opponent
  6. Team Opponent Name
  7. Team Opponent Logo

This configuration and some formatting, it would have a nice official look :slight_smile:

0 Likes

#10

I agree. There is definitely potential to go further and make it prettier.

0 Likes

#11

This is a great solution, looks great!

Could you share this doc, I’d like to learn from it.

0 Likes

#12

I don’t see how to make the doc public or automatically grant access upon request. I think I have granted access to your request though. Or else I don’t see the email requesting access.

0 Likes

#13

You can do this through drive - go into advanced permissions and give “view only” access to anyone with a link.

0 Likes

#14

Oh gotcha. But not on the coda page?

0 Likes

#15

Here is a view only link to a sample table and doc:

https://drive.google.com/file/d/1oTuMq10M6n8Gh_s2WX9-7UkzkOIPCbFS/view?usp=sharing

2 Likes

#16

Thanks sharing @Joseph_B, always good to learn from each other :bulb:

1 Like