Let's Mock Excel a Bit

1

Here’s the original formula in Excel:

=IF(
  INDEX(PayD_Work_Days, 
    ROW()-3,
    YEAR(INDEX(Pay_Periods,1,J$1)) - YEAR(FY_Start)+1
  ) = 0,
  0,
  INDEX(PayD_T4Gross, 
    ROW()-3,
    YEAR(INDEX(Pay_Periods,1,J$1)) - YEAR(FY_Start)+1) 
  / INDEX(PayD_Work_Days, ROW() - 3, YEAR(INDEX(Pay_Periods,1,J$1)) - YEAR(FY_Start)+1)*I4
)

Here’s the formula in Coda:

WithName(thisRow.Period.Year(), currentYear,  

SwitchIf( 
  currentYear==fiscalStart.Year(),
    thisRow.[Team Member].[Earnings First Year] / thisRow.[Team Member].[Total Working Hours First Year],   
  currentYear==fiscalEnd.Year(), 
    thisRow.[Team Member].[Earnings Second Year] / thisRow.[Team Member].[Total Working Hours Second Year] 

) * thisRow.[Hours Worked]

)

Neither of these are simple, but you don’t have the slightest chance of understanding the first one and I’ll bet you can figure out what the second one does.

6 Likes
2

I used quite a few “no code/ low code” applications (like Notion, Airtable, Monday.com among others) and formula language in Coda is by far best implemented. Chaining feature is really great and general implementation makes it much easier to understand and write formulas. At least for me :smile:

If only now we could get option for a proper full sized window for writing formulas :smiley: As I started learning more of Coda that became easily the number one feature improvement that is currently missing (aside from large/complicated table performance improvements)

6 Likes
3

Very great example, @cnr !

Totally agree.

1 Like
4

I’m hijacking my own thread to talk a bit about this idea.

I think Coda has an opportunity to do more here than just allow large editing windows. They are uniquely positioned to completely change how people think about the experience of writing formulas.

They can make formula readability a first class goal.

One of python’s great attractions to even highly competent programmers is that it is extremely readable. There’s a famous aphorism that goes,

“75% of your time is spent reading code, not writing it.”

The exact number fluctuates, but the sentiment is the same. Maintenance is more common than creation.

Transitioning from excel to coda was brutal because excel is completely unreadable. No wonder companies end up using the same unchanged, brittle document for years.

With the beauty of Coda canvases, formula documentation should be more than mere comments. Formula writing should be an interactive, colorful, descriptive experience. More like Jupyter notebooks than blocks of grey text.

Documentation

image
image1094×1900 268 KB

In each of my documents I add a page like this dedicated to documenting formulas. It saves me time, catches bugs, and ensures that anyone that might come after me will have an easier time of maintaining the system.

For me, it is a very manual approach at the moment that involves a lot of copy and pasting, and renaming a column causes a lot of work, but imagine if you could do something like:

/editor

and then type in the name of the formula you wanted to edit:

[Next Comparison Button]

and it would bring in a live, full page-width formula editor.

We could pop this open when we wanted to dig deep on a formula and delete it once it works. Alternatively, we can dedicate an entire page to documenting all our formulas—color annotating and bolding the different parts like we would any other text document meant to be understood, not just tolerated.

9 Likes
5

@cnr Fully agree with you. That would be quite a big improvement for people that are actually building and maintaining docs.

We are now planning to go back to all documents we made for our company and start documenting everything (I am not still sure how should we do this, but we will find some good method I hope. I was thinking about someting similar to example you gave above, and you are right its really time consuimng manual work). Although there are two of us that do all the building work, after some time passes you just forget how you did some things or exact formulas and it takes time to get “back into it”, and even more if I need to edit something that colleague has built or vice versa. All this is even more troublesome because we are not only doing Coda, that’s not our primary field of job.
So some good and easy way of documenting everything would be really great. I totally agree with you.

3 Likes
6

For starters, I would be happy if we could include remarks on our code. This could be in the form of function that doesn’t do anything other then holding our remarks or starting a line with a semicolon or any other character that is available. Even though coda remains pretty readable, as functions get more elaborate it would help to include some documentation that stays invisible to our users. This would also be a great help for illustrating how to do’s.

6 Likes
7

Interesting comparison. If you used Excel tables, I wonder if you might get a similar formula as the Coda one since you can reference columns in an Excel table? I haven’t used this formula myself, but perhaps the new LET() function in Excel might add to the readability of the formula.

8

I was migrating from an Excel old document. So this is a real world formula that I had to parse. In this case, it’s much less about Excel’s current capability and much more about the millions of unreadable formulas that still do something useful even if no one knows what it is.

1 Like
9

Adding the ability to write formula comments with some standard line prefix like // or # would be awesome - and seems like potentially a quick feature to implement.

10

I think there are more elegant ways to implement commenting than the way it has been done traditionally.

We have a whole canvas to use, and colors and (literal) commenting features. Coda can be used for documenting so many things: how might we use it to also document itself?

2 Likes
11

Yea I agree having some sort of “tutorial” or “tooltip” features for docs would be great too.

12

TO me the holy grail is to have things self-document, as part of the process of developing it. And yes, it is as much about user-style and discipline, as it would be about technical assistance.

1 Like
13

@cnr A step towards a better formula builder: [Launched] Formula building, now bigger and better

4 Likes
14

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.