Formulas and Functions

Archives for January 2022

Getting errors on ignoring the error cells while calculating the average for the column
Hello all, I've been trying to get an average for the column "Planning notified- QSTO generation". I want to ignore the #invalid data types cells and calculate the average of the column. I was suggested to use iferror but I'm not sure how to place this as a formula as it needs to return something if error is found. I need…
How to auto-trigger a flag in smartsheet?
Hi All, I am relatively new to smartsheet and trying to see if I can auto-trigger a flag in smartsheet? For example, if the quantity is under 500, the flag will automatically turn red. Is there a formula or a way to do this? Another question, if the quantity wasn't in the same sheet that the flag was at, can I still…
Cross-sheet references
I created a metrics sheet for one project that I am using for another project and did a Save as New and then used Clear Contents to remove the formulas from the sheet so that I could reference the new data. All was going well inputting my new formulas to the new data but now it gives me a popup stating that I've reached…
How do you count Columns in Smartsheet's
Column1 + Colum2 + Colum3 I need a formula that could add totals from 3 columns = Example= Risks + Issues+ Assumptions = Total See screenshot Thanks for your help in advance
Autopopulate Fields from existing columns into a Form
Hi all, I am trying to autopopulate two fields into my form from two existing columns with data. I've been following the community and so far I am able to pull one field using this formula: ="Linktotheform............?NameandSurname=" + SUBSTITUTE(NameandSurname@row, " ", "%20") Like that it works perfectly and I am able…
How to ignore error msgs when calculating the average?
I'm trying to calculate the average of the "planning notified -QSTO generation" column and ignore the invalid data type. I've tried the attached averageif formula but it seems to be not working! getting invalid data type. Highly appreciate your help! Thanks =)
Help setting up a formula
Hello there, I'm trying to set up a formula that shows when a vendor is on holiday. We have a sheet shared with our vendors where they add their time-off, and another one where we send them work. What I want is to create a formula that pulls that data from the holiday tracker and shows if the vendor will be available to…
how to work IFERROR THE EXCEL DEPOSIT DATA FORMULAS
how to work IFERROR THE EXCEL DEPOSIT DATA FORMULAS
Schedule Health Based off Duration and % Complete
Hi - I'm trying to write a task health formula with the following criteria and am hoping someone can help. Here's what I'd like it to calculate: GREEN: If today is less than 50% of the task duration and % complete is more than 50% YELLOW: If today is more than 50% of the task duration and % complete is less than 50% RED:…
Schedule Health (R, Y, G Symbols) Based off of End Date and % Complete Columns
Hello, I had previously posted a question regarding how to get red and green symbols based off of the due date and % Complete columns within a sheet. I now want to configure a formula that includes a yellow symbol if the due date is today -3 days. This means the symbol would be green until 3 days before the date. I would…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Index/Match vs Index/Collect vs Index/Contains
Creating a sheet to collect and answer FAQs, I am attempting to populate the System/Tech Supporting SME dependent upon the selected system/technology. I have my primary collection sheet and then I have made a reference sheet for some formulas. Relevant columns with fake data from the primary collection sheet. System/Tech…
How to add varying months to varying dates?
I am really having trouble getting a formula to work here. I've gone through a number of support articles, but I'm just missing something. I want a formula that looks at Date Contract Executed and it adds Months Until Expiration to produce a new Date of Expiration. I've got my date columns as dates and the middle column as…
How Can I Calculate the Number of Hours between Two Different Time Formula Values?
I have a form that collects times of day, into a sheet via a dropdown List of times (9:00 AM, 9:30 AM, 10:00 AM, ect). I have converted these values into a time using the TIME function. So now I have 4 columns: Start Time (dropdown list value from the form) End Time (dropdown list value from the form) Start Time 12 Hr…

Formulas and Functions

Discussion List

Help Article Resources