Formula Suggestion: [Column Name]@ to Imply "[Column Name] in This Row"

I would like to offer an idea to the Community to see if this has any support. There are plenty of examples of complaints throughout the community of sheets with complex formulas dogging down the sheet during saves. This can be exacerbated greatly by trying to move rows around the sheet, and the thousands of cell row references get updated, and all need to be saved just to move a row around!

An example: Let's assume I have a sheet with 1500 rows. I have the same formulas in all rows and these formulas only refer to other cells within their own rows. If I move row 1 to row 1500, all of the row numbers referred to in formulas across all columns, from row 2 to row 1500, get decremented by one. ALL OF THOSE ROWS AND CELLS GO THROUGH CHANGES UPON SAVE, JUST TO MOVE A SINGLE ROW TO THE BOTTOM OF THE SHEET!

What if, for formulas referencing cells in their own row, a symbol (the @, for instance) was used in place of the row number? I would wager that most of the formulas throughout the Smartsheet universe refer to other cells in their own rows. This would eliminate the entire issue of updating the row number when moving rows around, and having to save those changes all over the sheet!

This would also make formula writing and reading much simpler for formulas that only refer to cells within their own row.

Thoughts?

Find more posts tagged with

Formulas

Comments

Jim Hook

John, you pose an interesting suggestion. I've been one of the chief complainers about sheets getting too large and having to break them up. I just took a quick look at some of my most complex sheets to see if they use SUMIF(), SUMIFS(), COUNT() and COUNTIFS(). Some of them do and I'll experiment on one of them when I have time to see what happens. My most problematic sheets use many LOOKUP() functions in addition to a lot of nested IF()s and SUMIF() functions and could not be broken up into two smaller sheets. It doesn't look like the @Cell feature works on LOOKUP() functions. In my worst case the data was ultimately headed for use in an Excel database so I ended up coding the sheet in Excel thus solving my problem.

One way I've found to eliminate the problems of loading, editing and saving very large sheets is to make them typically ONLY run on Smartsheet's servers instead of in my browser. This approach can link in hundreds of data points from other sheets, process them and then link results out to their destination sheet without me ever having to open them. It can take a few seconds for this to happen but it's much faster than loading the sheet in my browser. I use this approach for alphabetizing/sorting an array of data on it's way to a display sheet since Smartsheet doesn't have functions or macros to automate these functions.

I do experience long save times (20 - 30 seconds) on sheets where I typically move rows around. I haven't been able to come up with a way around that because of the architecture I use for tracking up to 120 active projects without ever having to relink things as new projects come and old projects close.

Jim Hook

John, I did an experiment on one of my Smartsheets that has 1430 SUMIFS() formulas that look for a match on two criteria. The sum range on each formula is 400 rows. My goal was to see if converting these formulas to use @Cell in the criteria, instead of the non-@Cell formula, would decrease the time it took for the sheet to load and for the Save icon to go dim indicating that the formula engine had finished recalculating the entire sheet. Before I converted the sheet to use @Cell it took an average (three tries) of 17 seconds for the sheet to load and the Save icon to go dim. After modifying the 1430 formulas to use @Cell in two places the time to load and the Save icon to go dim INCREASED to 18.5 seconds.

Based on this I don't understand how the @Cell parameter makes the formulas more efficient. I'm running Chrome on a MacBook Pro, Maybe that has something to do with it.

John Sauber

Jim, I'm not talking about using @Cell, which is a feature that already exists. I'm talking about the potential for a new feature I'd like to see, which I'm referring to as [Column Name]@.

See here: https://app.smartsheet.com/b/publish?EQBCT=fcd4e23692de40d5b8308488f26231b0

Jim Hook

John, I'm sorry, I misunderstood your initial post. I think seeing the @ triggered me to think of the @Cell parameter which I had noticed but never used.

Your idea of using @ to indicate the same row as the formula is in makes sense. I may be an unusual Smartsheet user since only a small fraction of my formulas operate only in the same row. I make heavy usage of the advanced functions which mostly, if not always, operate on arrays of rows and columns and these are the functions that bog down my pages.

John Sauber

Another option: dropping the @ symbol, or the row number altogether, could imply “this row.” Then, you could have a formula that reads even more simply! Please forward this to your calc engine product manager as well. I’ve updated my post.

For the Profit Column in Row 234, for example instead of:

=[Unit Price]234 – [Unit Cost]234

OR
=[Unit Price]

We could write:

=[Unit Price] – [Unit Cost]

J. Craig Williams

I would vote against dropping the row # completely.

That would lead to user error. A lot, in my experience.

Explicit is nearly always better than implicit.

My $0.017

Craig

Rob Hagan

I agree with Craig as explicit is usually better.

I suggest that the syntactic expression of the reference to the current row may not change the underlying implementation or change the complexity of the calculation that SmartSheet has to carry out. The compiler designer may already have made such optimisations.

But, I am very interested to understand if there is any definitive reference material on what does affect the speed of calculation of a sheet. Does such a reference exist (apart from "bigger and more complex is slower")?

Thanks in advance,

Rob.

J. Craig Williams

When used together, four of my favorite words! (LOL)

I would also love to see some timing scheme. I would like to believe that the design choices are tested for browser optimization and speed. There is nothing I can pull directly from the API, but would like to see that too.

Craig