Announcing Several Formula Enhancements Available Now

Kara Lumley

Hi Community, 

One of my favorite things about Smartsheet is our powerful formula engine. We’ve been hard at work making several improvements that will not only make building formulas easier and faster, but can help you reclaim time throughout your day to focus on the work that matters most. 

I’m excited to tell you about the following new features, available now: 

Column formulas: When you need a formula applied consistently and uniformly in your sheet, column formulas are the perfect solution. Now, licensed sheet owners and admins can apply formulas to an entire column in Smartsheet, rather than individual cell level, for more reliable results throughout sheets.

 Here’s how to make a column formula:

1. Build your formula in a cell (see help article for syntax).
2. Right click, then select ‘Convert to Column Formula’.
3. Relax, knowing the formula will be consistently applied to the entire column.

No matter where rows get inserted into the sheet via forms, copy/paste, or move row, the column formula will automatically apply to every new row that gets added to the column - enabling you to set up your sheets faster, and have more confidence in your formula solutions knowing that every cell has the correct calculation. After the column formula is set up, it’s locked from editing to eliminate the frustration of accidental deletions or overwrites from your collaborators. 

Column formulas are available on Trial, Individual, Business, Enterprise, and Premier Plans. 

Read our help article and check out our blog post to learn more about the benefits of working with column formulas!

Default @row for formulas: Now, when writing a formula in Smartsheet and selecting a single cell on the same row, instead of defaulting to reference the row number, we default to using @row. @row is more performant, and will enable you to more easily build and use column formulas. 

For example, instead of “Column1”, the formula will default to “Column@row”.

@row will not be applied if a range of cells is selected or a cell on a different row is selected. You can learn more about the benefits of using @row here.

MEDIAN function: We are pleased to announce that we now support the MEDIAN function. With this function, you will be able to quickly return the median (middle number) in a given set of numbers. For example, =MEDIAN(1,2,3,4,5) returns 3. This ensures that outlying numbers do not sway the outcome when measuring a group of data, and in many cases, is a more accurate function than using AVERAGE. 

Learn more and get started with our updated Formula template in Solution Center.

Formulas in allocation columns: For those of you using our legacy resource management or the 10,000ft panel integration, great news! Smartsheet now supports using formulas (and column formulas) in the designated allocation column. In cells with formulas, results will be calculated at both the parent and child rows, instead of just child rows.

This supports many scenarios including the common need to calculate % allocation based on effort (for example, in hours), and will provide more flexibility to do more advanced scenarios such as taking into account custom availability or linking to allocations defined in separate sheets. 

We hope you’re as excited about these features as we are, and we want to hear how you’re using them in the comments below. Don’t forget, you can always submit a Product Enhancement Request for something you’d like to see on our roadmap. 

Thank you,

Kara

Sanil Shankar

Hi There, Here is a use case and I am a new user and I am also working as a an evangelist to help more people see benefits of Smart sheet in our project in Adobe. One of the features I am looking for is to add Column type - Time. As of now I know we have a modified Date/Time stamp that's system generated and not modifiable and also you can add Time as a Text ...this however doesnt serve my purpose as I want to have validation on the Field say "Execution target Start Time" and "Execution End Time" and then have a formula that calculates the hours taken - Is this possible in Smart sheet as of today?

Nate@E

Is there a way to change a cell reference formula into a formula that is supported by "Column formula"?

example: =[Row Number]392 + [epu Team Member]392

Andrée Starå

Hi @Nate Ensor

If it's on the same row, you can use @row instead. If it's not, you can use a so-called helper column, add the specific cell reference, and then use @row to include it in the Column Formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Nate@E

yes it did. It was on the same row, so I switched it to @row and then changed it to a column formula....which then seems to eliminate occasionally having to drag down formulas if a row is skipped etc. Thanks again!

Andrée Starå

@Nate Ensor

Excellent!

You're more than welcome!