-
Return a Minimum Date with A Condition
Hi there I am trying to adjust summary formulas so that they only return information from rows considered In Scope (so that data does not have to be removed from Not In Scope rows). Can anyone help with a formula that would return the MIN date from this group, only for the rows In Scope, ie Out of Scope is checked / true.…
-
Better Understanding of formula
I have the following formula in one of my sheets that I modified from one of the templates. It works just fine but after review the IFERROR information online I am still confused. At this point I don't really understand how the "52" plays a role in the formula other than knowing there are 52 weeks in a year. Can someone…
-
Converting text to a numerical value
I suspect there is a very simple solution to this. My knowledge of formulas is minimal and I want to make sure I am setting things up to work as I want them to work. In the example below what I want to do is to assign a numeric value to each of the text responses. (Very low = 1, low = 2, etc.). I would do this in both…
-
Formula to add "No" to a blank cell, but leave value if not blank
On a Smartsheet project intake form, I have several Yes or No dropdown selections. If Yes, the user must answer additional questions. If No, they do not and those cells are left blank. Auditors do not like Blank cells, so I'm looking for a formula to replace those blank cells with "Does Not Apply" but leave a value there…
-
Calculate the time between two dates by days, hours and minutes
Hi, I am trying to implement the following formula from my excel spreadsheet into my smartsheet: =INT(O2-Q2)&" Days "&HOUR(MOD(O2-Q2,1))&" Hour "&MINUTE(MOD(O2-Q2,1))&" Minutes" Basically, i need to calculate the time between two columns (date received) and (date documents issued) in days, hours and minutes. Cell 'O2'…
-
Weighted Average With a Condition
Hi Is it possible to gather a weighted average, with a condition? My starting formula is but I would like to build in a condition. In other formulas this has taken the form of I can't work out how to bring these together / if it is possible. Grateful for any thoughts anyone may have! Emma
-
SUMIFS with LEFT as a reference
I'm looking to sum a range as long as 3 criterion are met. 1 of those criterion is that Left of another sheet reference (4 characters) match a cell (could be text in the formula as well). =SUMIFS({Time To Assemble}, {IPK}, [Machine Type]@row, {Progress}, "Finished", LEFT({Item Number}, 4), [Machine Type]$17) This is…
-
COUNTIFS formula that compares dates in two columns as a criteria
Here's what I'm trying to do. KPI is a check box field (Key Performance Indicators). Status is a single choice list box field. Actual Finish and Plan End date are date fields. I want to count the number of KPI rows with a check mark where the status is complete and the Actual finish date is on or before the plan end date.…
-
Analyzing Text for Special Characters in a Conditional Formula
I have two sheets. Sheet 1 (Character Table) has a list of non-alphanumeric text symbols (e.g., !, @, #, $, etc.) and alphanumeric characters (A-Z, a-z and 0-9) alongside their ASCII values. This table distinguishes the non-alphanumeric values from the alphanumeric ones by classifying their types as "Special" or…
-
Help with Updating Rows Via a Form Submission
Hello, I am trying to use Smartsheet to track reagents, the workflow is as follows: The person prepping the reagents fills out a form with the pertinent information and the number of vials made. in this example the person made 40 vials. I would like to have this same number decrease via form submission. Example.) 1 vial is…