Feedback Requested: Using Formulas Article

I don't think anything was better organized that what is published here: https://app.smartsheet.com/b/publish?EQBCT=2b345b6e3e424c88b2368926728b06ea

The problem is this was left to languish after the update. I liked that it was read-only and didn't open up inside of Smartsheet (due to it being a template) and was just "there" for me to bookmark, visit, CTRL+F inside of, get to the syntax and example, then go back to work. Can't get much better than this, I don't think. Perhaps some examples could be refreshed or improved, but not much beyond that. I learned through support where I originally asked that this get updated, that it belongs to the Customer Success Director, Stephen. Obviously, he hasn't gotten around to updating it, or decomissioning it.

Of course, I could publish the template myself after becoming an owner of the copy, but I don't want to have to do that with every formula update or improvement (that's your job, right?!). This is doubly true, because your team is going to work hard at continuing to bring more and more formulas to the product to help us do better as a Company, and that would simply be too many edits for us to keep up with .

Regarding the Help Page you do have, it's nice and has good explaantions. I would suggest a persistent menu list on the left (meaning it follows with you as you scroll) listing all the formulas, with hotlinks to the location on the page. Most people visiting here aren't browsing, they know they need to find out if the sum range or the criteria ranges come first in the =SUMIFS statement. They (read: I) can't ever remember!

Gwyneth,

My response and directed at the article itself. As I write this, I've got a list of general function requests to capture elsewhere.

1. Please see -

https://community.smartsheet.com/discussion/lookup-documentation-mismatch

Shaine already said it was being worked on - but should be rolled out together (imo).

2. Not a fan of the alphabetical listing - would at least not grumble about it if you had a section explaining the categories (Numeric Function, Advanced Function, etc...)

If the categories have no meaning, then they should be removed (hint: they have meaning)

3. Formula Basics:

add hints about disabling functions (typically by changing = to '=

4. Using Cell References:

I believe this is

If the column name contains multiple words or ends with a number, enclose its name in brackets

would be more correct

If the column name contains multiple words, contains special characters, or ends with a number, enclose its name in brackets

Also, adding the [] will not harm your formula and if not needed, Smartsheet will remove them.

(That's a statement, not a complaint)

Serious oversight: discussing cell reference types (absolute vs relative) or briefly describing them and providing a link to a more detailed discussion. (The brief mention in the Using Drag-Fill section is inadequate)

Also how copy/paste or drag/fill affects these references if added here.

5. Referencing a Range of Cells

This:

=SUM([Annual Budget]:[Annual Budget])

is prone to error and will lead to a #CIRCULAR REFERENCE

error - and as there is no example in the #CIRCULAR REFERENCE section, may be overlooked and confusing by new users.

6. Using Drag-FIll to Save Time

The image is misleading. Someone that doesn't know about Drag-Fill may not click on the here link but even someone experienced (like me) may have a double-take on the image - it is not telling the whole story. One image probably can't.

7. Function Reference

The text says:

You can also use formulas and operators to join the contents of cells that contain text or to calculate dates

but no example of this is provided in the table below.

This is especially if Cost1 and/or Cost3 are text values in the example.

8. Formula specific:

- statements like this (ABS function)

numeric_value—A referenced cell that contains a numeric value or a manually typed number.

do not tell the whole story as a "numeric value" could be a reference to a single cell or to a complicated formula that returns a numeric value.

- in general, please add that return value type - ABS, for example, returns a numeric value. If placed in a DATE column, an error will be returned.

ANCESTORS
Must be used inside another function such as COUNT, INDEX, or JOIN

Add why this is true, otherwise, it is just trial and error on the end-users part.

In general, providing the reason may avoid having to list (and potentially miss) formulas that will accept the argument as valid.

ANCESTORS returns a range - and can be (or should be - I haven't checked them all) be used in any formula that accepts a range as an argument.

AND

This statement is false:

This function must be used within another function, such as IF, SUM, SUMIF, COUNT, or COUNTIF.

AND returns a Boolean value and can be used directly in a Check box type column.

This example:

=IF(AND(Done1 = 1, Done2 = 1, Done3 = 1), "All Tasks Complete", "Tasks Incomplete")

is not clear if user does not know that a checked Checkbox returns a 1 value and 0 if not checked.

also, this

=IF(AND(Done1 , Done2, Done3), "All Tasks Complete", "Tasks Incomplete")

is equally valid and more clear to a non-programmer.

If there are assumptions on knowledge level in the article (and there likely are), then they should be added at the top level discussion section.

AVG and AVGW

Please point out the difference between a 0 value and a blank value in the results.

CHILDREN

same comment as ANCESTORS for "must be used within..."

The description is self-referencing. "CHILDREN returns child rows" tells the user nothing if they don't know what a CHILD or PARENT is.

Note: This is one of the reason I don't like the alphabetical listing. There is a concept to ANCESTORS and CHILDREN and others that make more sense to describe once and then  explain how that concept is leveraged using formulas. Repeating the same text over and over again is just adding eye-strain to the end-users learning experience.

(Hint: It isn't that they are "Advanced Functions" either).

The examples use data that is not provided to the end-user (that row 1 is a parent row) and why would someone have their data formatted that way (Task A as the parent)?

COUNT

Second example

=COUNT([Task Name]:[Task Name])

Result: 10

also gives misleading results because we can not see the whole sheet (only rows 1-5).

Perhaps a published link with the image of the sheet above.

COUNTIF

Sometimes, a collection is referred to as a collection (COUNT) and sometimes it is referred to as a range (COUNTIF). Pick one.

Please clarify that value is the criterion.

value—A value or reference to a cell containing a value.

The example and Notes references "<" and ">"

Notes

• The “<” and “>” can only be used with numeric values in a COUNTIF function.
• Greater than or equal to (>=), Less than or equal to (<=), and not equal to (<>)also work and are useful to know.

Please add that the criterion is compared to each cell in the collection (or range)

COUNTIFS

This

Counts the number of cells within a range that meet the specified criteria.

is misleading. Each range is compared to its own specified criterion. In effect, this a set of COUNTIF functions that all must return "true" for the cells to be counted.

It also does not warn that the size of the ranges must be the same.

For example this will not work:

=COUNTIFS([Column2]3:[Column2]6, 1, [Column3]3:[Column3]5, 1)

because the range sizes are mismtached.

The Notes references the COUNTIF function.

Notes

• The “<” and “>” can only be used with numeric values in a COUNTIF function.

DATE

It would be nice if known features/bugs were mentioned in the article.

For example:

This

=DATE(16, 9, 31)

returns 10/01/2016 without a compaint (which I am OK with, I just want to know it will do that)

In general, this a good example that some functions must be used in certain column types (if their return value is the final one to be displayed). If =DATE() then it must be in a DATE column. 

DATEONLY

The notes use of "Currently"

Currently only values in...

implies that this will be changed. If it is on the road-map, I would leave it in. Otherwise, I would remove the "currently" as it gives us false-hope.

DAY

(and other DATE functions that take a DATE argument)

also take Date/Time column types as arguments.

FIND

In Smartsheet there is a distinction between VALUE  and TEXT.

FIND does not look at VALUE, it looks at a TEXT string to find the character position of the matching TEXT.

While the word "string" may be foreign to many users, especially non-programmers, so is "child" and "ancestor" in the Smartsheet context. It is mentioned in the description, but not in the Syntax where it would perhaps clear things up.

In the example, you could also show that

=FIND("as", [Task Name]4) would return 2.

It is important to note here that FIND's parsing of find_value is case sensitive.

In general, equally important (applies throughout, I imagine) is what a function returns when it does NOT return a valid item - in FIND's case, returning a 0 is important because that result can lead to other complex formula possibilties.

I'm sure that is enough for now.

Hope that helps.

Craig

Gwyneth,

Thanks for asking for our feedback.  Both of these guys are Master Champion Formula creators , so while I find the sheet information good, I really like the Template version becuase it gives the actual formatting of the formula.

I don't mind the alphabetized list of the formula sheet since there is also a grouping by function at the beginning.  

The more detail the better especially on the error messages when troublshooting. I still find it more helpful to look at actual formula examples either on the template or on the community board.

thanks,

Tim

Wow. Just wow! Craig's response is so thorough and educational, as I am a new user to SS. Thank you for the details, suggestions, and examples. Well done, Craig. You should get a commission, royalties, or something from SS for your contributions.

It is disappointing that it appears that NONE of the suggestions I proposed were implemented. I stopped looking after ten.

The template is now 7 months old. Is there an ETA on the update?

Craig

Thanks for the update.

Craig