-
How to fix INVAID DATA TYPE, referencing start and end dates, & duration to calculate % complete
I am using a project sheet with the formula below to calculate the % complete based on the duration of the task, and start and finish dates. However, if there are not dates in the start and finish column I am getting an INVALID DATA TYPE error. I've tried using IFERROR in my equation but I cannot get it to work. I plan to…
-
Multiple formula
Hello everyone, I tried to use multiple formulas, but for some reason it is working together (one-by-one they are working). What I want to do with this: I have a machine checking sheet, where the sheet send me a notification, if I work with any of my machines. Every machine has a group nr (1-5), what defines ow often I…
-
How do I add a third argument to countifs formula?
I am trying to only show dashboard data from the sheet IF the submitted date is from the current year - we do have a helper column for the submitted year but I am unsure how to incorporate it within my formula below. I am trying to count if the status is Working, Next up- top opportunities, Pilot/implemented,…
-
Formula to extract a date from a cell that includes both text & date
The cell in our sheet has "Completion Date: MM/DD/YYYY". In the sheet summary, what is the formula to pull just the date from that cell/field? Thank you!
-
Formula issue showing 0-5 Stars
Can anyone identify the error in this formula - it should show "Three" stars in the column as it's counting 3 true checkbox cells. However it will only return the "Empty" stars result. Obviously the number of stars formula result is based on how many boxes are checked.
-
Check inventory in two columns and change symbol column.
Hello all ! I have a challenge for you guys. I have a sheet where we book groups for a venue. Some of those groups will also book a space for a cocktail before the show. There are 3 spots available per day for a cocktail. I would like to have a column that tells me if the cocktail area is available for that date. I guess I…
-
Counting blank cells when other criteria is met
Hi, I need to add on top of a formula that counts tasks that are in progress or not started that are past due. The formula below works for that, but now I also need my total to include cells in the target completion date that are blank. =COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"),…
-
Formula to auto assign based on request number series?
Is there a formula that could be used to assign staff automatically based on request number series? The sequential request number will be auto generated when the form is submitted. For example, if one location has three staff that get assigned requests in the order they are received, I would like to automate it so that:…
-
AVGIF Formula Based on Date Range
Hello everyone, I'm trying to get a average of a column if the cells in another column are within 90 days of today. My current formula is =AVERAGEIF([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]@row>TODAY(-90)). I tried using AVG(Collect) as well with no luck. =AVG(COLLECT([Pick-up Variance]:[Pick-up Variance],…
-
Results and expressing as percentage
In my forms, I have several questions that can result as either PASS, FAIL or N/A. I want to express only the FAIL cells as a percentage over the entire row results. I've been using this formula: =COUNTIF(CellName1@row:CellName2@row, CONTAINS("PASS", @cell)) / 7 The only way I've been able to make this work is by counting…
