-
Combining AVERAGEIF and AND Functions
Hi All, I am wondering whether it's possible to combine the AVERAGEIF and AND functions together to meet 2 criteria. In my case, I'm averaging time - {X} is in minutes hence the division by 60 to convert to hours and the 2 to round to two decimal points. The below works as expected. =ROUND(AVERAGEIF({X}, {X} > 0) / 60, 2)…
-
Join/Match/Count Criteria
I have a sheet that is populated by another sheet. And in this populated sheet - I'd like to count how many times certain criteria show up (two specific data points). I assume I would need to join the two columns (i.e., Last Name and ID) and then if these two items show up together again, I'd like to have a count of these…
-
Using IF formula to reference any change in cell (adding a value)
Hello Experts! I was wondering if a formula like this is possible: =IF([Confirmation #]@row = "any value", "Confirmed") When a confirmation number is added to a cell, that means the item is confirmed. What can I put in place of "any value'? I am just not sure how to do this in the context of my larger status formula:…
-
Planko Status Column - is this possible?
Sheet 1: Col 1: Numbers in row sequence, i.e. 1111, 1112, 1113, 1114 Col 2: Status: Show / Unavailable Sheet 2: Col 1: Last Name Col 2: Number (index/matched from Sheet 1 based on Status Columns) Col 3: Status: Show Sheet 2 is a manual row entry sheet, and what I’m hoping to achieve is that when the status changes in Col 3…
-
Status light, nested IF statement
Hello everyone, I'm looking to find some help with my status light formula. My columns are as follows: • Date Due (date column) • Progress (drop down column with: "Not Started", "In Progress", "Complete" as options) • Status (drop down symbol column with "Red", "Yellow", Green", "Gray" circles -- to become a formula column…
-
Highlight duplicates across multiple columns
I think I already have the formula working for my Number column, but I would also like it to check additional columns "Number Field 2" "Item Number" for duplicate values. Not just checking for duplicates down one single column but occurring in any of the fields and highlighting those occurrences. I hope that explains what…
-
Percentage (%) complete formula
I'm using this formula suggested in another thread to calculate the percentage complete for particular tasks: =(Today()-StartDate)/(EndDate-StartDate) What I'd like to know is how to cap this at 100% because I don't want the percentage to keep increasing the further past the completion date we go. Any ideas?
-
If condition for Symbols in Smartsheet
Hi Smartsheet Community, By any chance, is there a possibility to create automation here wherein if a user ticks all 9 stars, the status will be tagged as "Completed", else it will remain as "In Progress". Thanks for any help you can provide. -Mart
-
"If" formula - Auto populate a defined text if a cell is blank?
Hello - unsure if I'm even using the right formula here, but I want a cell to populate the word "Requestor" if the cell is blank, and keep whatever is populated... if not blank. Or is there a way for a column that is currently a single select dropdown to default to the option "Requestor" and then would be changed if needed…
-
IF formula to pull biweekly updates
I'm trying to create a form that I send out to partners on a biweekly basis. I'm working with partners from 5 different countries, and I want their updates to be pulled using the sheet summary into a dashboard. Is there a way to do an IF formula with an INDEX formula to get the following results: IF [Country] is listed as…