Topic8. Basic formulas

With formulas and functions, you can use them in one sheet orOver multiple sheetsYou can perform calculations and lookups (references).Use formulas to create symbols and drop-down fields自动化You can also.

You can use both manually entered values ​​and cell references to create formulas.Add formulas to specified fields,Whole columnYou can apply uniform calculations to. Sheet summaryYou can also create formulas with to save space in the grid.

Formulas can include functions such as SUM, AVG, IF, like CHILDRENHierarchical functionThere are also some unique functions dedicated to Smartsheet, such as.For all available functions,Function listPlease Confirm.

Find out more about the syntax for creating and editing formulas in Smartsheet.

Video of explanation by concrete example

There is an Excel sheet that was used as a reference for the sample.Website

Sheet used in the explanation

Sales management sheet
https://app.smartsheet.com/b/publish?EQBCT=b8208442aeb14c409cd8e62372dd9cd3

Overview of Smartsheet formulas

Smartsheet functionsThere are many things in common with Excel functionsTherefore, those who are good at Excel functions can utilize their knowledge and experience.Here are some of the most important things you need to know when using formulas.

Even if you've used spreadsheet formulas, many of these tips are unique to smartsheets, so be sure to read them.

How to add a function

Now suppose you want to add a formula to a sheet.
To do this, on the toolbarFunction menu-Use.For each functioneasy explanationSo you can check if there are any mistakes.
Also, if you enter a function in a cell,Help tipWill guide you through the completion of the new formula.

For example, in the figure on the upper rightDetailed informationClick to jump to the Smartsheet function help article.

Inheriting functions to upper and lower rows

When adding new rows above and below rows with the same formulaNote that new lines automatically inherit their formulas and formats.
This should be noted when adding new rows by the form.In other words, if you want to apply the formula to the newly added row, set the new row to be added next to the row that currently contains the formula.One surefire way is to add a new line at the beginning.

Cell reference

next,Cell referenceThis section explains.Cell referencesManualEnter withClick cell directlyYou can enter it by doing.

Currently, there is a bug that garbled characters occur when inputting Smartsheet functions in Japanese.We are requesting improvement, but until it is improved,How to process by clicking a cellIs recommended.

The biggest difference from Excel etc.You can use column names
In addition, refer to the cellIf the column name contains multiple words or ends with a numberIs the column nameEnclose in bracketsYou.

Absolute cell referenceDoes not change when moved, copied or filled.
To make the cell reference absolute coordinates, before the row number in the column nameDollar signAdd, or both.

Cell range reference

If you want to reference a range of cells in the same sheet, select the range or click the start and end cell references.colonEnter by separating with.
In addition, Referencing the entire column as a range of cellsYou can also.
Add cells and ranges with commascan.

Column formula (function)

Column formulaYou can also create to apply calculations and formulas to every row of the sheet.
The formula is automatically applied to new rows as well. 
However, be sure to format itReference to @rowPlease use.
After creating the formula, select Convert to Column Formula from the cell menu.Then it will be applied automatically to the entire column.

Smartsheet unique functions

When using a hierarchical structure within a sheet, CHILDREN functionIs convenient.
You can calculate the total number and average of the child rows under the parent row.
In addition, When a new child row is added, the parent row calculation automatically contains the new data..

Comparison with Excel function

Comparison with Smartsheet original function (Children) and Excel function

Smartsheet's formula syntax (that is, how to write formulas) is a bit different from Excel, but the basics are the same.
Let's compare the main Excel functions with the Smartsheet functions.

List of Smartsheet functions
https://app.smartsheet.com/b/publish?EQBCT=af48f9809ea546d994342f3e71fb7a22

There are many functions that are almost the same as Excel, and you can make use of Excel's function knowledge.

Smartsheet's formula syntax (that is, how to write formulas) is a bit different from Excel, but the basics are the same.Perhaps Smartsheet has the same or similar syntax so that you can use your knowledge of Excel as much as possible.

  • SUM total value
  • MAX, MIN maximum / minimum value
  • Sum with SUMIF conditions
  • Number of values ​​that meet the COUNTIF condition
  • Search and display VLOOKUP values
  • IF condition judgment
  • AVERAGE average value

Excel that does the same thing as the list of Smartsheet functions above Click here for more information..

Cell reference is easier

Whereas Excel refers to cells such as A23, in Smartsheet price 23 or price@rowAnd so on to the cell.
[Column name] @rowIs used to refer to another column in the same row, and to refer to a value in another row (in this case, row 12), such as discount rate 12.

It's easier to understand when you look at the formula

  • For Excel A12 = B12 * C12
  • For Smartsheet Sales amount @row = Price @row * Quantity @row

Has its own function, such as a hierarchical function

For example,Children ()It is,= SUM (Children ()) In the form of, it is possible to aggregate the rows of the children of a certain parent row.
In addition, in the Ganchi chart of project managementDependency functionsThere is also.

Creating and editing formulas in Smartsheet

You can use formulas to calculate numbers and automate sheet elements.For example, you can create a formula to sum up a range of expenses, or to select a check box when a specific value is entered in a field.


Create a formula

You can create formulas for the following field types:

On the other hand, formulas cannot be used in the "Automatic Numbering / System" column.
For the benefits of using formulas in contact list columns, Click here for more information.Please refer to the article.

  • Dependency,Resource managementTonot being usedfield.For more information, seeEnable dependencies and use predecessors"Please refer to.
  • String / number (if not used in dependencies or resource management)
  • Contact list fields (if not used in resource management)
  • Date (if not used in dependencies or resource management)
  • drop-down list
  • Checkbox
  • symbol
  • % allocation

To enter a formula in a field:

  1. Select the desired field.
  2. Enter the equal sign in the field, then enter the formula.
    5 + 3
  3. After entering the formula, press Enter.
    8
    The result is displayed in the field that contains the formula.

You can enter formulas in the contact list fields.For example, use the VLOOKUP expression to automatically assign users to tasks.Note that you cannot use formulas in the contact list fields used for resource management.

function

Formulas can include functions such as SUM, AVG, and IF.Functions use the data in the sheet to perform calculations and manipulate the data in the sheet. For a complete list of functions available in Smartsheet, seeSmartsheet function list"Please refer to.

Formula operator

You can use the following operators in Smartsheet formulas:

symbolDescription
+追加
-Subtraction
*Multiplication
/division
^Indices
<Smaller
>Greater
>=Greater than or equal to
<=Less than or equal to
=equal
<>Not equal

 


Edit an existing formula

To edit an existing formula:

  1. Fields containing formulasDouble clickThen open it for editing.
  2. Change the formula you want and press Enter.

    If you want to stop editing a formula, press Esc to exit edit mode and return to the formula you were not editing.

Tips for easily creating formulas

Use column formulas

If you need to apply the formula consistently and evenly across the columns,Column formulaIs best used.Create a formula in a cell, quickly convert it, and apply it to all cells in the column.The column formula is automatically applied regardless of how the new row is inserted into the sheet.Detail is,"Set formulas for all rows using column formulas"Please refer to.

Use the Sum tool

If you don't know which function to use, you can use the toolbar [Sum] You can select a function by clicking the down arrow to the right of the button.

[Sum] Buttons provide formulas based on the hierarchical relationship between the selected fields on the sheet and the selected fields.For example, select a field in the parent row and[Sum] Click the icon to generate the formula = SUM (CHILDREN ()) in the field.

After you create a formula, you can change it at any time by double-clicking on the selected field or by pressing F2 (fn + F2 on Mac).

Use percentage

Smartsheet treats the percentage as a value between 0 and 1.If you want to create a formula in a column formatted for percentages (on the toolbar) [Percent button],[Percentage format] Use the button),Small numberUse the.example…

= 0.5 + 0.4

Type ... in a column formatted as a percentage 90% Is displayed ...

= 5 + 4

Type ... in a column formatted as a percentage 900% It will be displayed.

Copy formulas with drag fill or auto fill 

If you want to use a formula in multiple fields without having to manually enter it in each field, you can quickly copy the formula to another area of ​​the sheet using the following method.

  • Drag Fill – You can copy the formula to adjacent fields in the sheet by dragging from the bottom right of the selected field.When copied, the formula automatically changes each field reference.
  • Autofill – with Smartsheet,Automatically copy formulas to new vertically adjacent fieldsCan be made.When you run Autofill, the new formula automatically changes its field reference.

For more information on this feature, see the help article "Drag fill"When"Formula autofill and format."


Areas where the use of formulas is restricted

The following fields cannot contain formulas.

  • Contact list fields used in resource management
  • System field (eg updater)
  • Default columns (attachments / links, comments, row action indicators)
  • DependencyResource managementThe following fields used for:
    • start date
    • End date
    • period
    • Predecessor task
    • % completion

You cannot create formulas with the following features:

  • Form
  • Update Request (The result of the formula can still be viewed in the update request, but it cannot contain standalone formulas.)
  • Report (The result of the formula can still be displayed in the report, but it cannot contain a stand-alone formula.)
  • Dashboard (The result of a formula can be displayed in the dashboard, but the dashboard cannot contain standalone formulas.)

You can't edit formulas from the following features in Smartsheet:

  • Form
  • Update Request (The result of the formula can still be viewed in the update request, but you cannot edit the formula.)
  • Report (The result of the formula can still be viewed in the report, but you cannot edit the formula.)
  • Dashboard (The result of the formula can still be viewed in the dashboard, but you cannot edit the formula.)