-
Using AVG to Calculate Average Completion Time
Good afternoon, I am trying to find the average of a column and am having problems using AVG, AVG COLLECT, etc. I am trying to find the average of "Completion Time" column. As you can see sometimes there is no data and sometimes it says "invalid data" while awaiting the completion date. I want to put an average completion…
-
Status stoplight ball formula
Hi all... I have a pretty good working formula to automate task status with stoplight balls based on percentage complete and how close we are to the task's finish date. I have it tied to a 7-day flag column that is flagged when we are within 7 days of finish or past finish date. The stoplight formula is: =IF(AND([7 Day…
-
Formula for calculating only number in Project Status (Complete)
Probably a basic formula, sorry if it is. Need a formula for calculating only the number in Project Status (Complete). Is there an online class I can take to wrap my head around the basics? Thanks!
-
IF/AND date formula to flag a delayed task
Hi! I want my formula to flag in red when a task is late, that is, when it is still "in progress" when the "end date" is today. The smartsheet accepted the formula below, but it didn't work. Can you help me? =IF(State@row = "complete", "Green", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow",…
-
Set Priority Number based on Date and Aging?
Hi there, Struggling a bit with an If/then formula to create a series of priorities. Priority 1: Date is within 21 days OR Aging is more than 300 days Priority 2: Date is within 35 days OR Aging is less than 299 days Priority 3: Date is within 56 days OR Aging is less than 199 days Priority 4: Date is greater than 56 days…
-
How to Pre-Populate a field in a form with a unique ID?
Hello Community, I am asking for your help today figuring out a challenge I am currently facing. We are trying to pre-populate the "Work Order ID" field in the form because it is often overlooked or entered incorrectly. We have been trying to figure it out but it is hard because you cannot have a column formula field in…
-
Flagging duplicates if 2 criteria match
Hi! I'm trying to build a formula that will identify duplicates from 2 different columns on the same sheet. So the combination of both need to be repeated. The columns are [RCPT] and [1st Test].
-
Formula Character Limit
I am creating a large formula, when I add in another column, the formula stops working. Is there a character limit to a formula in a cell? =IF(OR([Billing Schedule]@row = "", [Billing Launch Date]@row = "", [Steady State Date]@row = "", [# of Locs]@row = "", Biller@row = "", [Monthly Goal Standardization]@row = "",…
-
Contains or IF formula
I have two columns, one called EP and the other BP. I would like to have the row appear on a report if either column does not have "X", "TBD" or "N/A". For example, If one column has an "X" and the other has something other than "X", "TBD", or "N/A", I would like the row to appear on the report. I know I need a helper…
-
For a Project Portfolio Roll Up, SUM of Total Budget IF Project is Active/In Progress
I'm using the Project Management Office template set; however, it does not come with Budget totals, health, etc., like it does for the Schedule. I had no problem adding budget info for individual projects and displaying them on the project dashboard. I am now trying to get this information to roll up to the Portfolio level…