29

Each year, Smartsheet hosts a hackathon called “Hack the Sheet.”

During this hectic, exhilarating, and productive week, our entire product and engineering teams get to work on projects they are super passionate about. We divide ourselves into teams based on interest, have some late nights, and are judged by peer and leadership panels. 

My team worked to build a number of new formulas, all highly requested by Smartsheet customers, that we believed would significantly improve user productivity. We set a goal of creating six new formula functions by the end of the hackathon. We ended up building 24! 

As many of these functions were on the wishlists of our most enthusiastic customers, we know they will help teams in every industry to be even more effective. For the complete list, check out the updated Smartsheet Formulas Example sheet and apply the filter named Formulas added in July 2019. 

We’re also releasing performance enhancements that will improve formula speed within your sheets and coming soon...formula support in contact columns!

We’re happy to share these changes with you, and look forward to many more to come.

Thank you,

Kara

Comments

It would be nice to have an "Updated on" for each function on the Functions List page

These are the ones just added:

  • AVERAGEIF
  • CEILING
  • CHAR
  • CONTAINS
  • DECTOHEX
  • DESCENDANTS
  • DISTINCT
  • FLOOR
  • HEXTODEC
  • ISEVEN
  • ISODD
  • MOD
  • MROUND
  • NPV
  • PERCENTILE
  • RANKAVG
  • RANKEQ
  • ROUNDDOWN
  • ROUNDUP
  • STDEVA
  • STDEVP
  • STDEVPA
  • STDEVS
  • UNICHAR

In reply to by Shaine Greenwood

Yeay! made my day :)

I know it's probably irrelevant to some, especially if you are just starting to use Smartsheet, but little bits of information like that can seriously help us keep moving forward. Also, it's a great way to show how feedback produces progress and results to your potential (as well as existing) customers.

In reply to by Ezra

Totally agree — this was an oversight on my part. I forgot to add the release date when writing these articles so the "new" tags didn't appear.

Good catch and thanks for your insight. :D 

Andree_Stara

Hi Kara,

24 Wow! Thanks! smiley

Performance enhancement! yes

Formula support in contact column! cool

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Hi Kara,

These are awesome! Great job to you and your team.

Are there any plans on a time formula? I know this is something that has been requested for quite some time. Just wondering if we can see it in the near future. 

Regards,

Brittany 

I've been playing around with some of these, and have found quite a few interesting uses. Whether or not I'll actually need them is a different story, but they are interesting uses no less. Haha.

 

I also know I am ready for that "coming soon" to change to "just released".

Me: Doing a happy dance. 

MORE FORMULAS! GREAT JOB! :D 

Thanks to Kara and her team !

And thank you Ezra for the overview!

Hi Kara

I am happy to get MOD and DESCENDANTS, so that I do not have to do the workarounds. Thank you team for your good efforts. Keep the good stuff coming.

 

Best Regards

Chak Khiam 

Based on the example for DESCENDENTS function/formula there is a potential bug in the value returned by this formula. Currently the value returned includes the parent row where as the actual value should exclude the requested row itself.

For example, below should return 3 for the formula COUNT(DESCENDENTS([Column Name]100) but currently it returns 4

where row 100 is 

  • Task 100
    • Sub Task of 100
      • Sub-sub Task of 100
        • Sub-sub-sub Task of 100

In reply to by SK

Thanks for letting us know! 

We have a fix ready for this, and should know by Tuesday when we can deploy it. I'll update the thread then.

Thanks!

New formulas and functions are great add! Thanks SS team.

Can we get some functions for PREDECESSORS and SUCCESSORS? Especially to get the predecessor task name from the row number

Currently there is no way to know what are the successors of a given task are, so if someone deletes the task then it breaks the dependencies without any warning. 

Also just displaying predecessor numbers in a report is of no use as there is no context of what the task associated with the number is. If we can have function to get the name of the task associated with the predecessor/successor # then that will be so much useful.

 

Thanks,

SK

 

In reply to by SK

Hi SK,

You're very welcome!

We have some ideas on showing predecessors/successors - I'd love to run them by you and see if they would work for your use cases.

If you'd like to send me an email at [email protected], I can give you an overview.

Thanks,

Kara

Very excited about the mention of possible formula support in the contact columns!!!smiley

yes awesome!!

Hackathon sounds like a fun time! 

Can you use the new CONTAINS in a COUNTIF formula?

To be more specific, when using a multi-select column I want to count how many times a certain value is selected.

 

In reply to by [email protected]

Yes you can. Andree's link to the published sheet is a great example of this very thing in action. You would first designate your range, then the CONTAINS function would be your criteria.

 

CONTAINS is a true/false type of function, and if you do not specify what type of result you are wanting then it defaults to true.

 

=COUNTIFS(range:range, CONTAINS("text", @cell))

is the same as 

=COUNTIFS(range:range, CONTAINS("text", @cell) = true)

.

To count how many cells do NOT contain "text", you would need to either use a NOT function or specify false (or even specify not true - user preference)

 

=COUNTIFS(range:range, CONTAINS("text", @cell) = false)

=COUNTIFS(range:range, CONTAINS("text", @cell) <> true)

=COUNTIFS(range:range, NOT(CONTAINS("text", @cell)))