Verify data in cells
Scenario: Check whether order numbers conform to the defined format.
Description: Order numbers are only valid when they follow this format (###-#########).
Formula: IF(FIND("-",[order number])=4&&LEN([order number])=13,"✅","❌")
250px|700px|reset
250px|700px|reset
Calculate sales rankings
Scenario: Calculate members' ranking within the sales team based on monthly sales figures.
Description: Team members are ranked based on their monthly sales and you can use sort to list members based on their ranks.
Formula: [Ranking].FILTER(CurrentValue.[Sales amount]>[Sales amount]).[Name].COUNTA()+1
250px|700px|reset
250px|700px|reset
Calculate a running total
Scenario: Calculate the running total of monthly sales figures.
Description: Sales staff are calculating a running total of their monthly sales figures to keep track of their progress in real-time.
- Step 1: Create a numbering field or number field, and then number each row.
- Step 2: Apply the formula to calculate the running total.
Formula: [Accumulation].FILTER(CurrentValue.[No.]<=[No.]).[Sales amount].SUM()
250px|700px|reset
Check whether multiple fields meet specified criteria
Scenario: You need to determine whether the equipment meets the required standards during an inspection.
Description: In manufacturing, routine inspection is vital to ensure the equipment meets quality standards. In this case, you need to determine whether multiple fields meet their respective conditions.
- Step 1: Summarize the results of multiple fields with the LIST function
- Formula: LIST([Appearance],[Controller],[Emergency Button])
250px|700px|reset
250px|700px|reset
- Step 2: Determine if the criteria are met with the IFS + COUNTIF functions
- Formula: IFS([Facility Status].COUNTIF(CurrentValue="Pass")=3,"Qualified",[Facility Status].COUNTIF(CurrentValue="")=3,"To be checked",TRUE,"X")
250px|700px|reset
Calculate the time difference between two dates
Scenario: You need to calculate the time difference when holding a meeting with participants from different time zones.
Description: When calculating the time difference between two dates, you can use the TEXT function to change the results into text format.
- Formula 1: Calculate the time difference in hours
- TEXT([Start date]-[End date],"[h]")
- Formula 2: Calculate the time difference in minutes
- TEXT([Start date]-[End date],"[m]")
- Formula 3: Calculate the time difference in seconds
- TEXT([Start date]-[End date],"[s]")
250px|700px|reset
Calculate month-over-month growth
Scenario: Calculate the month-over-month growth of order volumes for order management.
- Step 1: Set the month column to a number field, and enter each month as a cardinal number (1, 2, 3...)
- Step 2: Create a formula field and select the FILTER formula.
- Step 3: Set the number format of the final column to Percentage.
Formula: ([Amount]-[Sequential growth rate].FILTER(CurrentValue.[Month]=[Month]-1).[Amount])/FILTER(CurrentValue.[Month]=[Month]-1).[Amount]
250px|700px|reset
Quickly identify the latest updates
Scenario: Quickly find the date on which members provided their latest update for a project.
Description: For projects big and small, members need to submit progress updates to keep the team informed and aligned. You can use Lookup fields and the IF function to quickly find the date of update for each member.
- Step 1: Set Latest submission date as a lookup field to find the date on which each member provided their latest update.
- Step 2: Apply the IF function.
- Formula: IF([Submission date]=[Latest submission date],"✅","")
250px|700px|reset
Combine date and text into a single cell
Scenario: Combine the purchase date and stock level into a single cell to facilitate inventory management.
Description: With the TEXT function, you can convert specified content into text. You can specify the date format when you combine a date and text.
Formula: TEXT([Purchased on,"YYYY/MM/DD])&"-"&[Storage]
250px|700px|reset
Sum up a range of data
Method 1: Use the SUM and FILTER functions
Scenario: Calculate the total annual sales for each team member.
Formula: [Sales Table].FILTER(CurrentValue.[Sales Name] = [Name]).[Sales Revenue].SUM()
Description: In Sales Table, the sales amounts of each salesperson are listed. By using the filter function, the sales amounts for each salesperson can be filtered out in the Sales Amount table. The SUM function is then used to calculate the total. In the images below, the left image is the Sales Table, the center image shows the formula used, and the right image shows the calculation result.
250px|700px|reset
250px|700px|reset
250px|700px|reset
Method 2: Use the lookup field
Scenario: Calculate the total annual sales for each team member.
Description: In Sales Table, the sales amounts of each salesperson are listed. By using a lookup field, the sales amounts for every salesperson can be summed up. In the images below, the left image is the Sales Table, the center image shows the settings for the lookup field, and the right image shows the calculation result.
250px|700px|reset
250px|700px|reset
250px|700px|reset
Add a line break in formula fields
Scenario: Combine the name and department of each member into a single cell, and insert a line break for better readability.
Description: The CHAR function converts a code into a character, so you can use it in a formula to add special characters or line breaks.
Formula: [Name]& CHAR(10) &[Dept]
250px|700px|reset
Calculate the number of days between different dates, or add or subtract days, months, or years to/from a given date
Scenario 1: Calculate the number of days spent on different tasks.
Description: Calculate the number of days between two dates, or add or subtract a given number of days from a specific date.
- Formula 1: [End date]-[Start date]
- Formula 2: [Start date]+10
250px|700px|reset
250px|700px|reset
Scenario 2: Calculate the predicted end date for a task.
Description: Use the DATE, YEAR, MONTH, and DAY functions to calculate the date after a given number of months or years.
- Formula 1: DATE(YEAR([Start date]),MONTH([Start date])+2,DAY([Start date]))
- Formula 2: DATE(YEAR([Start date])+1,MONTH([Start date]),DAY([Start date]))
250px|700px|reset
250px|700px|reset
Obtain the ID of a record
Scenario: Record the ID of each row for indexing, inserting, editing, or deleting entries on external or third-party platforms.
Description: You can use the RECORDID function to obtain the ID of each row for indexing with third-party platforms. The IDs for each record in the table are unique.
Formula: RECORDID()
250px|700px|reset