-
How to Calculate Age from a Specific Date & Average Results
I am trying to average the age of multiple events as of specific dates (end of each month). Currently I have "Age" calculated from "Today()" in a column in another sheet. I would like to instead calculate the Age from "Date of Notification" to "End Date", and would like to avoid using a helper column to do so. I do not…
-
Using Join collect from referenced sheet while ignoring criterion
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP",{Prime Student Onboarding Worksheet Affiliate}@row))), CHAR(10)) trying to get this formula to work while ignoring cells in the rate total column that have a hyphen (this is formula…
-
HELP!!! Max Collect for Dates
I utilize Smarthsheet to manage a hospital schedule. I need to find out the last time a staff member worked. I have been playing around with Max Collect and constantly failing… #UNPARSEABLE Things to note: The Primary column shows the name of the staffer multiple times with multiple different dates under the Date Worked…
-
Average that meets Criteria 1 or Criteria 2
I have a smartsheet with many events that often span several months. I want to calculate the average age of relevant events for each month. For example, for the month of March, I want it to calculate the average age of events that are still open in March in addition to the age of all events that were closed within the…
-
Join Collect formula that excludes the current row from the results
I'm trying to create a formula that creates a list of duplicates in my sheets, I've gotten this formula to work but it's showing the original row within the list of duplicates. I tried adding an auto number column and including a condition that the list shouldn't include the current row. However, the addition of this to…
-
Having trouble with an Index Collect with multiple matches?
Hello all, please let me know if this makes sense what I am trying to accomplish as I have been stuck on trying to get a fix for this. I am working to create a sheet that looks at an export out of a dsp and checks to see if those are elements match with the information in our smartsheet - kind of like a QA sheet to do…
-
Join(Collect question
Hello, I am trying to join all notes in a cell from another sheet if the phone number on that row matches the phone number on my current sheet's row. I can do this in google sheets but struggling to translate to smartsheets. =JOIN(COLLECT({Notes}, {Phone #}, [Phone #]@row), ", ") This is returning 9 commas for each cell.…
-
Divide by Zero Error With AVG(COLLECT(...
Hoping someone can help because I am totally twisted with this one. I don't use AVG(COLLECT very often so I may be missing something simple here… What I'm trying to do is take the average of Column17 where both the year (Column22) and the weekday (Column9) match the year and weekday that I've indicated in Column21. (Yes I…
-
Adding Multiple Criteria to a COLLECT Function
I have a working formula gathering the MEDIAN of a group of numbers that fall within a date range used in a trend chart in a dashboard. I now want to add two additional criteria for cases when the dashboard user would like to calculate the MEDIAN but exclude certain values (referred to as 'HOLD' and 'Strategic' data) from…
-
How to return the contents of one cell that don't exist in another cell
I have an employee list where I'm tracking training status. I have a column with assigned training, and a column with completed training. I want to be able to display training that an employee has not completed. In the past I've used a JOIN(DISTINCT(COLLECT(CONTAINS formula to pull the training completed but would now like…