I have a formula that is multiplying several different values, and the result is a long decimal. The .round family of formulas don’t work on it, regardless if I use .tonumber
Any ideas?
The issue here is actually that this is an invalid formula, and the formula engine is doing its best to interpret it but is a bit confused 
. Coda formulas currently only support dot-chaining syntax in certain cases, so you’d actually have to rewrite this as RoundDown([Column A] + [Column B]). The formula preview that’s shown is grayed out in this case which means that it’s the result from the previous, last valid formula - valid results are black.
BTW, we’re currently working on improving writing formulas, so we welcome any feedback!
2 Likes
FEEDBACK ON IMPROVING WRITING FORMULAS
- Note: Not sure if the formula editor is technically a “modal,” but that’s what I’m calling it below.
- Note: Some of these observations apply to other modals as well, like the conditional formatting modal.
MODAL RESIZING
“Internal content” of the formula editing modal includes: the formula, help tips, computed value, error messages, @ref lookups, etc. As this internal content changes, the modal automatically resizes. This in itself is probably fine, but its resizing largely triggers the issues discussed below.
a) It might be easier to ditch the resizing, and instead create a fixed size that is resizable by the user (draggable corners), with default user settings (small, medium, large, auto-resize) or device-based sizing default options.
- Note: Have wanted better sizing options for formula editor in spreadsheets for many years.
MODAL RE-POSITIONING
Automated resizing of the modal triggers automated re-positioning of the modal, which seems to be anchored to the row. THIS IS A DISORIENTING USER EXPERIENCE, because as the modal jumps around, one’s visual focus must constantly reacquire the target character location in the formula string.
b) I find myself constantly wishing that I could just de-anchor the modal and manually position it where desired on my screen. Could also give the user some default setting options here (top of screen, bottom of screen, left, right, etc).
MODAL VERTICAL SPACE CONSTRAINTS
Very often, especially when working with lengthy formulas, the script that distributes vertically the modal’s internal contents isn’t up to the task, and a good chunk of content (often parts of the formula itself) get cut off by the bottom of the modal (I think the top also cuts it off, but I’d have to double check that). The only fix I have found for this is to literally deselect the existing row, and select instead a different row (usually toward the top or toward the bottom of the screen), where the modal has more vertical space to maneuver, allowing it to render its full contents.
c) (a) and (b) might alleviate this issue. Scroll bar might also be useful (though maybe not optimal).
OBJECT SELECTION within FORMULA
When the same object is used repeatedly in a formula, selecting one of its latter instances in the formula, and then deleting it, actually deletes the FIRST instance of the object on the formula instead. (As opposed to positioning the cursor and then using delete or backspace.)
d) ?
VISUAL NAVIGATION of FORMULA
When a formula gets lengthy, it just turns into one giant text blob (with color coded objects 
). This makes it difficult to visually parse the logic and syntax of the formula.
e) Some basic beautifying would go a long way here. Line breaks, line indentations, isolation of expressions, etc.
- Note: Have wanted better visual navigation (beautifying) of spreadsheet formulas for many years.
IDE as a CONCEPTUAL GUIDE
If a core premise is “docs in which the user can build apps, too”, then the user is probably going to eventually want some sort of ~BASIC~ IDE functionality to do that job.
f) You have invented a new formula language
- Note: Basic IDE functions I’ve wanted in spreadsheet formula editors for years: beautifying formulas, code commenting on formulas, find/replace, act on multiple instances simultaneously (change name, delete). This kind of basic stuff. I literally copy/paste formulas into Word docs to get these types of functionality when working with huge formulas.
$.02… hope it helps! 
I have to get to work now!! 
1 Like
@Ander thanks for the input! Some thoughts in response:
Automated resizing of the modal triggers automated re-positioning of the modal
Any chance you could get a screen capture of this? This is not expected. By design the builder should scroll internally when there’s not enough space, but never re-position as you type. Example:
https://cl.ly/76356886863b/Screen%252520Recording%2525202018-12-28%252520at%25252002.43%252520PM.gif
MODAL RE-POSITIONING
A couple other users have also asked for this and it’s on our good ideas list 
. Thanks for re-raising the suggestion. The ability to drag the formula builder would be particularly useful in my experience for cases when the builder happens to cover column names you’re trying to reference.
When the same object is used repeatedly in a formula, selecting one of its latter instances in the formula, and then deleting it, actually deletes the FIRST instance
Sure enough. I just wrote myself up a bug to go fix this. 
Thanks for reporting.
When a formula gets lengthy, it just turns into one giant text blob (with color coded objects
We’ve got some good stuff coming on the color piece of this specifically. Stay tuned for this. Line breaks & indentations is something we’d also love, but it’s a bigger project.
In the meantime, it’s often helpful to break up sub-expressions into separate columns and then reference these columns. This kind of factoring also happens to have performance benefits.
You have invented a new formula language
This is a cool idea. Something we’ve talked about internally is having ways to quickly view the definitions of all tables, formulas, etc. in one place - which is along the lines of what you’re getting at I think. There’s lots to explore in this space.
4 Likes
Pay attention to how the modal height resizes based on:
- the vertical position of the row
- the contents being rendered inside the modal.
Also note how the formula itself gets cut off on top or bottom depending on vertical positioning of the row, and the contents being generated inside the modal.
I didn’t bother trying to record all this interactive complexity while the user is actually working in the formula itself, but it gets even more wonky when the formula itself starts rendering different content.
This is my first go with CloudApp, but hopefully these gifs will give you a sense of what I’m talking about.
https://cl.ly/213a033bf86bhttps://d1wuojemv4s7aw.cloudfront.net/items/1X2i3o272M2C0L0m1E2G/Screen%20Recording%202018-12-30%20at%2011.15.14.67%20AM.gif(image larger than 4096KB)
Hi Ander, thanks for the links. Sorry for the delayed response - these got triaged to my spam folder for some reason. I see the issue and will add to our list to triage.
One tip in the meantime: Coda works best when you break large formulas into smalls pieces (separate columns for instance). Each small formula is much easier to debug this way because you can see the output of just that sub-expression.
Incidentally, most of the positioning oddities you’re seeing are unique to long formulas. Unless I’m mistaken, small formulas won’t have these problems.
1 Like