Announcing Several Formula Enhancements Available Now
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:
- Build your formula in a cell (see
help articlefor syntax).
- Right click, then select ‘Convert to Column Formula’.
- 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.
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 “[email protected]”.
@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.
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.