-
Formulas
I'm Trying to add a function to the following formula in which adds "should the cell to the right of this formula show anything else except completed then the formula will display BEHIND =IF(AND(TODAY() + 1 >= [Completion Due Date]29, [Days to Completion]29 < 100), "BEHIND", "")
-
Sumif if question
Hey folks, Working on a sheet to sum up if checkboxes are selected and running into an issue. The formula I have now is- =SUMIF(PRD50, 1) + IF(Stories50, 1) + IF(Assets50, 1) + IF(Wires50, 1) + IF(Comps50, 1) Each one of those are checkboxes and I want to tally them all up. Ideally, I'd like it to equal 100 since that…
-
Using CHILDREN() to Define Range in VLOOKUP()
I have a parent row on Row 1 with three indented child rows. I want to populate a cell in the parent row with the first name from a child row that matches a value in one of the columns. There will be other parent rows with their own children, and the number of children may vary. Each parent row represents a "family", and…
-
How to keep a formula when a cell is modified
Hello, I have a bit of an odd situation and I'm hoping that someone might know how to help. We have a Smartsheet that we use when new clients come on board so that our Customer Care team can complete the account setup. The new clients are added to the Smartsheet via a form and we have formulas to automatically add "N/A" to…
-
Average formula with weekends excluded
Hello All, I need to tweak a very simple formula. Currently i have this '="Average Resolution Time - " + AVG([Time Taken to Complete]33:[Time Taken to Complete]148) + " Days" ' but i really need something that only averages workdays within that time period. Be very glad i any suggestions. I started experimenting with the…
-
Countifs with with percentages
What am I doing wrong? The formula was fine when I did the tower in air dates from today - 12.31.19, but as soon as I tried to add the condition of lease probability being above 49% it doesn't work. Any thoughts are greatly appreciated! =COUNTIFS({C. *Ground Lease Tower In Air Date}, >=TODAY(), {C. *Ground Lease Tower In…
-
Calculating the duration between 4 dates
Does anyone know the formula to count the days between 4 dates. If the Ready to Launch date is greater than or equal to the Received IO, then the Duration would equal the number of days from Start to Launched. Otherwise, the Duration would equal (days from Start to Ready to Launch) + (days from Received IO to Launch).…
-
Formula instead of cell linking?
Hi all, please note I've never used any functions like this so not sure if I'm asking in the right way. I have two sheets: Sheet 1 includes the following columns: - Project # - Start Date Sheet 2 includes the following columns: - Project # - Start Date (blank, needs to be filled with formula) How do I build a formula in…
-
Creating formula in smartsheet
Hello, I have a requirement to create the below formula in smartsheet. Please help. If short shelf life is between 40% to 59%, then discount is 5% on the invoiced amount, If short shelf life is between 30%– 39%, then discount is 15% on the invoiced amount, If short shelf life is between 20 – 29%, then discount is 40% on…
-
how to define who the approval is from multiples
I have migrated our Engineering change to smartsheets which is working fine. I now want to progress this by adding approval requests. We can get changes approved by 3 different people, so I have created an approval request which gets sent out once a box is checked to declare the change is ready for approval. At present…