-
Update formula so it ignores empty rows
Hello. I am using the below formula to mark whether a task is overdue or not, and then highlight using conditional formatting those overdue tasks in red: =IF(AND(Finish@row < TODAY(), Status@row <> "Complete"), "true", "false") I would like to update the formula to ignore blank rows. How may I accomplish this? Also, open…
-
Formula for quarter based on finish date
I want to create a formula that will auto-populate the Quarter column with Q1, Q2, Q3, or Q4 based on the date entered in the "Finish Date" column. For instance, if the "Finish Date" is 4/15/2021, I want the Quarter to be "Q2".
-
Within X amount of time from Today
Hello, I am trying to find a formula that will tell me if a date is within 4 Years of today's date and populate "YES" or "NO" in a separate column. (Example: To help specify if a product is in warranty that lasts 4 years, and populate "Yes" in another column if the date entered is within 4 years)
-
Count IFS and Count IF
I have a formula that is counting, for a specific criteria (see below), in a specific column; however, now I need it to look in a 2nd column and also include it in the COUNT; however, I do not want it to be "double-COUNTED". For example, if the amount will always appear in Column 1 and then eventually appears in Column 2…
-
How to create template dashboard and summary grids with formulas?/ Is it possible?
I need to create a set of templates that are personalized to our needs. The needs are the following: - We have a template that is pre-set with column names that is populated by us. 1. From this template after it is populated I need to create a summary template in a grid containing formulas. 2. From this Summary template I…
-
AVG Collect adding Month and Year
Hello All, I am looking to average a Likert scale question of 2 hospitals together, from my list of 9 hospitals. How do I add the month and year so I only average the score for January 2024 =AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B")) I have done this in the past,…
-
Average collect w/ 3 criteria
I'm attempting to average the %age passed for a specific month and year for a specific team. I successfully used the below to average the total by month and year, but can't figure out how to add the team. =AVG(COLLECT([%Passed]:[%Passed], [Month 2]:[Month 2], =1, Year:Year, =2024) I tried the below, but am getting a…
-
Logging time on hold
Hi, I am looking for a way to log the time a row's project status column is changed to on hold and then stop counting when the status is changed. It would be possible for the project to go on hold multiple times. Any help would be much appreciated. Thanks!
-
Formula SUMIF with date range
Hello, I have a sheet that tracks educational programs: date of the program, # of participants, and content categories for reporting purposes. I'm trying to write a formula that counts all the participants in a specific date range and falls into a specific category but I keep unparsable. Can someone help me figure out what…
-
Complicated IF formula
I am trying to create a column formula that does the following. If the application is Declined or Withdrawn then cost is 0. If not, then based on each course we offer, it puts the cost of the course. This is the formula I have as of right now but it is very messy and not working. =OR(IF(IF(OR([Application Status]@row =…
