From Excel to Coda.io — how to?
In spreadsheets like Excel you link cells. This unleashes an enormous potential well described by Al Chen when he wrote:
Spreadsheets have enabled us to take an unstructured problem, break it down into its component parts, and neatly change and edit those parts.
When you start a calculation in a spreadsheet like Excel, I often see two patterns. First when you want to have the sum of something like the spendings of office supplies, we have in Excel a way of doing this almost standardly and when we make a budget for a year we often see it as below, rather differently. For both scenarios we use in Excel a different pattern to break down the problem into its component parts.
Coda has tables and these are relational databases. In this article I focus on one key learning any user should be aware of when switching from a spreadsheet like Excel to Coda.io
Summarized: instead of linking cells, you link lists filled with items. Every manipulation has a list as starting point and not an individual cell.
Once you understand this, it may help you to shorten your trajectory in getting familiar with Coda. Head of Product Development at Coda Matt Hudson notes:
Relational tables are still a pretty difficult concept for most people to understand ー learnability itself will forever be a challenge.
Once you see ‘lists’ of data, you are halfway the solution you are looking for.
Using Coda does not make you a developer, you are not ‘coding’. What you learn is to combine functions to create formulas. The logic behind these building blocks (functions) is developed by a developper. We are simply Coda users who learn to create smart formulas.
Lists as building blocks
In Coda the budget can be constructed as in Excel, thus each column is a month, but often it is done differently. The budget is the column and the months are the rows. Just like the office supplies.
Applying the Coda logic, you turn your data (budgets per month) into a list. Besides many advantages others express eloquently, I focus on the list logic and why it matters.
Below the formula that shows the basic logic of our list:
thisTable.Budget
By default Coda assumes thisRow if you type the name of the column. Coda shows you the value of thisRow and only one of thisRow (like thisRow January or thisRow February). To have all the values of the column you have to ask for it using ‘thisTable’.
When you start a lookup or a filter, a good way to structure the function is to begin with the name of the table or ‘thisTable when you are inside the table. It filters then automatically on the complete list you link to via the column you mention.
In the above you see nothing at the end of the formula and the outcome is still the names of the month in a specific order (Feb, Aug, May, July). This is because of two logics inside Coda. First, if you do not mention anything, it will automatically take the display column and show the values as displayed in this column. Second it shows the months in order from the lowest budget to the highest budget (but all smaller than 8700). Apparently logical operators take the smallest numbers first. To have the months ordered from feb -> august, you add Sort() at the end as below and if you fill out nothing in Sort() .
thisTable.Filter(Budget > 8700).Sort()However showing information based on this formula is a bit silly. Why should you want to see on each row the 4 months that have a budget less than € 8700? In this case it is not useful. However if you use the conditional format to highlight rows that have budgets lower than € 8700, this is the logic you need. How that works we show by bringing our attention to the Checkbox logic.
Checkbox on thisRow
First we add a checkbox to mark the rows that fit our criteria. I am dyslexic and can use this kind of tricks to avoid mistakes in my evaluation of a formula when I write 7800 instead of 8700.
thisTable.Filter(Budget < 8700).Contains(thisRow)
As you can see, we ask in this column if the the outcome of this filter contains thisRow. If so the answer is True() and if not it is False(). Using this simple trick we have checked and unchecked boxes marking the rows that fit our criterium.
It is an easy application of the ‘thisRow’ logic whereby the complete list (the 12 months in a row separated by a comma) is evaluated by the outcome of the filter (budget less than € 8700 ) on each row via the function Contains(thisRow).The list we generated first with the 4 months and applied on row level, makes sense in a different way. It becomes actionable data.
The position in the list
The months in our example have numbers we recognize easily. February is 2, August 8, May 5 and July is number 7. We changed the Display column to the RowIndex. Behind the RowIndex we have a simple formula that produces the number, see below how we make use of Find()
thisTable.Find(thisRow)
Once you see this pattern, you understand how differently data is structured in Coda compared to Excel and how this logic typical for relational databases helps you to set up a calculation that is less prone to errors and more robust than in any spreadsheet like Excel.
With numbers in the displayColomn we can easier calculate using typical Array functions like Nth(), Slice() and others. These functions need numbers to operate. The downside is that when you do a lookup you get the DisplayColumn value and then a number is of not great use. It is trade-off.
Examples
Once you see that data can be read as a list and that each item or group of items in that list can be evaluated as part of a calculation (once it contains numbers), you unleash the power of Coda as a relational database. However — and here we are are back where we started — the starting point is structuring our data intelligently. The columns define the kind of data you handle, the rows the variations (or attributes) of this data type.
Top 3 value
Assume we want to know the sum of the top three most expensive goods in our list of office supplies. We need a few things. First we need the complete list, thus the thisTable’ variation, we need this list sorted correctly, we need the Sum() and last but not least we need a formula that helps us to slice the list into parts. Below how it works. If we take Sort(True()) or simply Sort() we have the sum of the lowest three values. This is because Coda assumes via Sort() an ascending order by default.
thisTable.[Office Supplies].sort(false()).slice(1,3).Sum()Inside Array functions as Sequence, Slice, Nth and others we often need a number. A top 3 is someting fixed and 1 is a number as is 3, so the Slice function is easy to fill out in this example. But what if you do not have a number directly availble?
Holidays — who goes to which summercamp?
I came accross an issue when creating an overview of the holidays for my boys and I published an article about it. The challenge was to link two tables. However a standard lookup was not possible, there was no corresponding column yet that could link the date on each row with the Holiday table that had only a start and end date. I decided to implement something simple based on the idea that everything can be seen as a list inside Coda. I thus created an extra column. This extra column contains al the dates between these two dates, it is a list of dates. Have a look below.
The formula creates a list (an array) of data points.
Sequence(thisRow.From,thisRow.To).FormulaMap(CurrentValue.ToDate())The second step is to create a simple lookup and it is all set and done. You can also see below that some values remain empty.
The trick was to see the dates as a date range, thus as a list of dates, to format it as such and then use that information to make the LookUp() work.
Nested Formulas to get numbers
This example is about injecting a number in an Array function via a formula instead of a hard coded number as in a former example. We look at a Time Table and we check if the breaks support the driving time for that specific trip.
The bus driver is allowed to drive 4.5 hours in row max and that twice a day; but 2 days out of 7 days it is permitted to drive 10 hours per day. Between the blocks of 4.5 hours driving the drivers need at least a 45min break. This break can be consumed at once or split in at first 15 minutes and second in 30 minutes. In case he drives 10 hours, thus 2 * 4.5 plus 1 hour, there is a need of an additional 30 minutes after nine hours before the last hour starts. Our questions is:
Can we provide a planner with a tool that checks if the bus driver can execute the planning within the limits of the EU directives?
The answer is yes. What we do is the following. We count the total driving time per work day (we use a Filter() and a FormulaMap() ). The outcome is a list with values, in our case time values. We check if the outcome of the calculation (the list) is smaller than or equal to 9 and if so we need a break of 45 min properly split (or taken at once) and if longer it cannot be longer than 10h and we need 45 min breaking time and one break of at least 30 min before starting the last hour driving. Below how I solved this puzzle.
What we compare are lists. We first generate a list that contains the sum of the driving time that day, second we show information related to this outcome. The moment we add a row, the list changes and maybe the outcome of the calculation as well. Once the formulas are written, a user doe not have to be afraid to corrupt the data structure, nothing can be broken. One can simply enjoy the information related to your choices.
A spreadsheet is not fit for such robust calculations. Even if you can make it work, the set up remains fragile. What makes Excel great, makes it also weak: the flexibility to let cells directly link to each other. Coda is more demanding and in return offers a stable and convenient solution.
Conclusion
Many of my clients believe that Excel is after the invention of email maybe the best tool they ever came across. And this is largely based on the flexibility it provides. Coda is different: everything is a list and items in a list can be compared to items in another list once your tables are correctly set up . And so we finish with a starting point for another blog: how to structure your document properly.
I hope you enjoyed it. If you do not follow the logic, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done. I live in Ghent and serve the BeNelux market.
