Feedback Requested: Using Formulas Article

Gwyneth C

Hello Community!

We just made changes to the "Using Formulas" article in our Help Center that we’d like your input on. A few of the modifications include:

• Alphabetizing the list of available functions.
• Extensive function argument definitions.
• Up-to-date list of error messages.

We hope you’ll find that this makes the article easier to scan for the function you want help with. 

Please take a look and give us any feedback you have on this article: what works, what doesn’t work, what you’d like to see added to the article—anything that can help make this a fantastic resource when creating formulas.

Here's the link: http://help.smartsheet.com/articles/775363-using-formulas

Thanks!

Gwyneth C

Thank you Craig! This is great feedback, and I appreciate the level of detail. We'll get to work on #2 right away--I hope to have an update by early next week.

 

It'll take us some time to work through the rest of these ... but you can expect to see continued improvements in the coming weeks. 

 

Cheers!

Gwyneth

John Sauber

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!

 

J. Craig Williams

John,

 

With a later version, they went a Template instead of a Published sheet.

 

https://www.smartsheet.com/solutions/smartsheet-formula-examples

 

What I liked (and you said) is that the Published sheet could be kept up to date.

But that doesn't give a versioning system like the can have by recreating the Template each time.

What I don't like is thtat I need to click on "Use Template" and get the message that I already have the latest version. Or at least the latest version of the one that is accessed via the button. There was a time when that button was pointing to the wrong version (not the most recent one).

 

Seems like a live (published) link to a "master" would be best - except for the few days they want to take it off line for updates.

 

Neither is the perfect system. The documentation has to match the current feature set - which means regular releases (and I hope that "documentation updated" is a checkbox in their release process) -- no published links.

 

We've asked for features related to Templates before.

Here's one that could potentially solve our woes:

 

When I open a sheet that has been created from a Template, if the system "knew" which Template it was and that the Template had been updated I'd get a pop-up "this sheet was created from Template X which has been updated. You may want to get  newer version of the Template and recreate your sheet" (I don't want to give any developers nightmares by suggesting they do the reconcile).

My options would be:

1. OK - I'll do that later.

2. Remove connection to Template X (ie stop reminding me)

For this specific case, I'd go get the new Formulas template and be happy knowing I had the most recent one.

 

YMMV

 

Craig

 

 

J. Craig Williams

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 C

Hi John and Craig,

 

Yes, we're up to version 2.0 of the template (the published version John points to is 1.5e -- which is defintely out of date).

 

I completely agree that the Smartsheet-based resource for functions (the template that used to be a published sheet) and the Help content should be in sync. Working on getting that sorted out...

 

>> From your perspective, is the sheet version of this information (given that it's current and accurate) most useful as a published sheet or does the Template version give some benefit that a published sheet doesn't?

 

Thanks again!

 Gwyneth

 

Tim Meeks

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

Gwyneth C

Thanks Tim!

 

We're working on getting the template version updated.

Galen Garrison, PMP, CSM, MBA

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.

J. Craig Williams

Gwyneth,

 

Is there an ETA on the template update?

 

Craig

J. Craig Williams

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

Gwyneth C

Hi Craig,

 

Thanks for your persistence with this. Here’s an update:

 

The article was updated to include a category browse (by function type) in addition the alpha listing (this was #2 in your list of requests and recommendations).

 

 

We also “chunked” the content—separated out the functions reference material from the procedural “this is how you create a formula” content. The goal of this is to better address the needs of people just getting up to speed with formulas and functions from those who simply need to remember what the arguments are for a particular function or to see examples of how specific functions are used.

 

The collection of formulas and functions content now has a dedicated home: https://help.smartsheet.com/topics/formulas-and-functions

 

Some of the things you mentioned as missing or needing better explanation are now covered in a net new article: Create and Edit Formulas in Smartsheet https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

 

This new article includes a better explanation of absolute vs relative references and a summary table of the different options for cell references in general: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet#summary

 

And to be totally transparent about progress...the broader overhaul of the function definitions and keeping parity with the template is taking longer than I hoped.

 

KNOWN ISSUE: We’re tracking down what happened to the Formulas template. Should have an answer about that later today.

 

Cheers! Gwyneth

J. Craig Williams

Thanks for the update.

 

Craig