Tip:
Highlight text to annotate it
X
Let's take a look at Formulas in Smartsheet
Formulas in Smartsheet allow you to automate
calculations on any of the data in your sheet.
When data that the formula references is changed,
The formula recalculates and will automatically update.
Now there are many situations where formulas can be useful.
We can count items in a list,
or calculate hours
and budgets.
We'll walk through creating formulas and
showing different examples
using the Formula Sample Sheet.
First, lets find the Formula Sample Sheet.
In the template gallery,
type 'formula' in the search box.
click 'Use Template'
and it'll open in a new tab.
There are several sections to the Formula Sample Sheet.
Click on the plus sign to reveal the details if each section.
Notice the cells highlighted in green.
They contain live formulas that
you can examine and experiment with to see how they work.
Now every formula available in Smartsheet
has a live example in this sample sheet.
Let's start with the basics.
Click ONCE on a cell with a formula
and it will display under the cell.
DOUBLE click on a cell to enter edit mode,
and any cell references will be outlined.
Once in edit mode, you can position the cursor
using the mouse by clicking anywhere in the formula.
or using the arrow keys.
Hit the ENTER key to apply your edits.
Or ESCAPE to exit edit mode without applying your edits.
Now every formula must start with an equal (=) sign.
Parenthesis () can be added to specify the order of operations.
Here the parenthesis ensures the addition (+) occurs first,
followed by the division (/)
To use text in a formula,
use quotes (" ") around the text.
To use a function, you can type the function name
or use the formula button in the tool bar
Note that only the most commonly used functions are in the tool bar.
A cell reference is composed of the column name and row number.
To reference a cell,
start the formula and then just click on the cell you want to reference
and it will be added to the formula automatically.
Or you can type the cell reference manually.
The column name needs brackets ( [ ] ) if it contains a SPACE.
To reference a range of cells,
click and drag over the cell range.
The cell range is represented by the beginning cell reference
and the ending one, separated by a colon (:)
We can also reference an entire column as a cell range.
We can move cell references
by dragging one of the borders to a new location.
Or add to it by dragging a corner to stretch the cell reference.
We can add additional cells
or ranges by separating them with a comma (,)
Notice that the formula text
is color coded to match the color of the cell reference border.
We can use drag and fill to copy
a formula into adjacent cells.
and the new formula with update row
and column references automatically.
If you need to duplicate formulas
and reference the same cell,
you can lock the cell reference
by putting a dollar sign ($) in front
of the column name, row number or both.
Now lets look at 'Numeric formulas'.
Adding (+), subtracting (-),
dividing (/) and multiplying (*)
require only the symbol between two values.
whether it's a number or cell reference.
Functions are supplied to help calculate things like
counts, averages and more,
used to help simplify formulas.
You can see the syntax of each function in this column.
Logical formulas allow us to make calculations
based on conditional logic.
For example, we can compare two values
and display which one is larger.
If we want to change the size or color
of items based on conditional logic,
we can use the conditional formatting feature
from the format rules button on the tool bar.
Text functions allow us to process
text and have it displayed in our sheet.
For example, we can combine FIRST
and LAST names from separate columns
into their own field.
Date formulas allow us to make date based calculations.
For example, with the Today function,
we can reference today's date
and subtract (-) it from another date,
giving us the number of days between the two dates.
With the Children function,
we can calculate counts, sums and averages
from child rows under the parent row.
When a new child row is added,
the parent row calculation
will automatically include the new child row.
Advanced formulas, such as 'PRORATE',
'COUNTIF' and 'SUMIF', are available in Smartsheet if needed.
In the 'Additional Examples' section, we find more examples
of using formulas with different column types in Smartsheet.
For example, we can see how to set the value of a check box
and get the value of a check box.
This is just an introduction to formulas in Smartsheet.
To learn more,
take a closer look at the Formula Sample Sheet
or reference the help tutorial.
And that's formulas in Smartsheet.