Formula Handbook: Your ultimate guide to master formula in Smartsheet
No one can deny that mastering formula will have a massive advantage in your daily work life with SmartSheet, you can efficiently execute calculations and lookups on a single
sheet or across multiple sheets using formulas and functions. You may automate symbols and drop-down fields using formulas as well.
Both manually entered variables and cell references can be utilized to generate a formula. You can apply a consistent computation to an entire column or add formulas to a set of fields. To conserve grid space, you can also create formulas in the Sheet Summary.
Here are some common formulas you might consider:
Certainly, here’s an expanded list of 20 Smartsheet formulas with examples to give you an idea of their practical applications:
1. **SUM**: Adds up values in a range.
– `=SUM([Budget]1:[Budget]10)`
– Adds up the values from row 1 to row 10 in the “Budget” column.
2. **AVG**: Calculates the average.
– `=AVG([Sales]1:[Sales]5)`
– Calculates the average of sales from row 1 to row 5.
3. **MAX**: Finds the largest number.
– `=MAX([Revenue]1:[Revenue]10)`
– Finds the largest revenue from row 1 to row 10.
4. **MIN**: Finds the smallest number.
– `=MIN([Expenditure]1:[Expenditure]10)`
– Finds the smallest expenditure from row 1 to row 10.
5. **COUNT**: Counts the number of cells with numbers.
– `=COUNT([Inventory]1:[Inventory]10)`
– Counts cells with numbers from row 1 to row 10 in “Inventory”.
6. **TODAY**: Returns the current date.
– `=TODAY()`
– Inserts the current date.
7. **DAYS**: Calculates the number of days between two dates.
– `=DAYS([End Date]1, [Start Date]1)`
– Calculates the days between the start and end date in row 1.
8. **IF**: Checks a condition and returns a value based on the result.
– `=IF([Status]1 = “Completed”, “Yes”, “No”)`
– If the status in row 1 is “Completed,” returns “Yes,” otherwise “No.”
9. **VLOOKUP**: Searches for a value in another sheet or range.
– `=VLOOKUP(1001, {Sheet2 Range 1}, 2, false)`
– Looks for 1001 in Sheet2 and returns the value from the second column.
10. **JOIN**: Joins multiple text strings.
– `=JOIN(“, “, [Column1]1, [Column2]1)`
– Joins the text in row 1 of Column1 and Column2, separated by a comma.
11. **PARENT**: Gets the value of the parent row.
– `=PARENT(ROW())`
– Returns the value of the parent row’s primary column.
12. **RANK**: Ranks a number within a list.
– `=RANK([Score]1, [Score]:[Score], 1)`
– Ranks the score in row 1 within all scores in ascending order.
13. **INDEX**: Gets a value from a specific row and column.
– `=INDEX({Sheet2 Range 1}, 2, 3)`
– Gets the value in the second row and third column from a range in Sheet2.
14. **MATCH**: Finds the position of an item in a list.
– `=MATCH(“Apple”, {Fruits Column}, 0)`
– Finds the position of “Apple” in the “Fruits” column.
15. **INDEX-MATCH**: Combines INDEX and MATCH for complex lookups.
– `=INDEX({Price Column}, MATCH(“Apple”, {Fruit Column}, 0))`
– Finds the price of “Apple” from the “Price” column based on its position in the “Fruit” column.
16. **LEN**: Measures the length of a string.
– `=LEN([Name]1)`
– Returns the length of the string in the “Name” column of row 1.
17. **UPPER**: Converts text to uppercase.
– `=UPPER([Name]1)`
– Converts the name in row 1 to uppercase.
18. **LOWER**: Converts text to lowercase.
– `=LOWER([Name]1)`
– Converts the name in row 1 to lowercase.
19. **AND**: Checks multiple conditions.
– `=AND([Status]1 = “Completed”, [Quality]1 > 90)`
– Checks if the status is “Completed” and quality is above 90 in row 1.
20. **OR**: Checks at least one of multiple conditions.
– `=OR([Status]1 = “Pending”, [Status]1 = “In Progress”)`
– Checks if the status is either “Pending” or “In Progress” in row 1.
These formulas can help you perform a range of tasks, from basic calculations to complex data lookups and text manipulations.
To go deep, you can also use the “Template Set” of Smartsheet, some key benefits are:
- Learning about using formulas in SmartSheet.
- Working with live examples of each function in SmartSheet.
- Examples of commonly used formulas, such as Red Yellow and Green status calculations Examples of advanced formulas, such as cross-sheet data lookups with multiple criteria.
How does it work?
You access to the template via this link .
