-
#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula
I am trying to pull an average of months employed for current employees in a specific division. My formula is: =AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN")) {MonthsEmp} is a column formula: =ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire…
-
Formula error help: Not Started, Complete, In Progress, Overdue
Hello - Looking for assistance to resolve #INCORRECT ARGUMENT SET error. I believe I am overlooking it, because I have been focused on it for too long. If the due date is in the future, I want "Not Started" Formula giving error: =IF([% Completion]@row = 1, "Complete", IF(AND([% Completion]@row < 1, [Due Date]@row <…
-
Sum Cell Range (Min-Max) to Grand Range Total (Min-Max)
Smartsheet Community: Does Anyone have a Solution for Totaling a Range in Each Cell to a Grand Total Range? See Example Below: Range: Row 1: $100k - $500k Row 2 $100k - $500k Row 3: $100k - $500k Row 4: Total: $300,000 - $1,500,000 Thanks
-
Countif Blanks with an OR function
Hey! I am trying to build out my metrics sheet, with a long Countifs formula with multiple criteria. One of the criteria I need is: Count the number of cells in a range if either of a date column is Blank. I keep getting the #INVALID DATA TYPE Error. My formula looks something like: =OR(COUNTIF({Date Range 1},…
-
Need help with a complex formula
Attempting to write a formula in a sheet summary field. Is it possible to write a formula that looks for particular rows based on the text value in one column, and then get the AVG of the numbers in a different column on those selected rows? (ie. If the value in column Task is "Work Order Received", find the average of the…
-
Help: Formula Creation Request Please
Can someone help me by typing out what I hope is a simple formula? End result, when I update my "Status" column, I want it to enter the following amounts in my "% complete" column. FYI, Status is a drop down single choice column type. I will be so appreciative! I just don't time to figure it out by trial & error. Example…
-
Pulling Data Based on Certain Criteria
Hello Community, I have a situation where I am trying to pull data from 3+ columns based on a few input fields (city, country, engineer level). We want to output the primary company, secondary company, and third company once this data is inputted. On my base sheet, I have a City, State, Country, Engineer Level, Primary…
-
Modified Date Corresponding to One Column ONLY
Hello all! A question about a formula and/or Modified Date System Column. In my task list, I have a column marked "Due Date." Our team may extend that due date, and my manager would like to keep a record of when the due date was modified. I was wondering if it was possible to have the system column "Modified Date" only…
-
Formulas not working for resource allocation sheet
Hi, I have reached out to Smartsheet support, but haven't received a response in almost a month so thought I would try here. With our plan, we don't have resource allocation/workload view so I am trying to create a sheet for the team that shows allocation such as number of tasks, story points etc. However, the formulas I…
-
Averaging a month and year of data
I want to average the month of April, 2022. I can't figure out the month, much less the year. I did look this up but I can't seem to get it to work. =AVG(COLLECT(Boiler Gas Usage}:{Boiler Gas Usage}, {Date}:{Date}, IFERROR(MONTH(@CELL),0)=4))