-
SUM "Duration" based on date Range
I have a sheet with an "End Date" and "Duration" columns. I need a formula that will look at the "End Date" and see if it is equal to or between two dates and then SUM the duration. Ex. Date range 6/8/2020 through 6/14/2020 and I want to SUM the total duration. Thank you in advance.
-
Row and Sum calculations for Capacity Management
So, until we get 10k feet, I am trying to figure out how much of our team's capacity is spent on projects or operations work. In the sample below, I'd like to know how much time teams and sub teams spend on Projects, Operations, Enhancements, and Admin. There may be muliple project, operations, or enhancement rows. I'm…
-
How do I round a formula answer?
The above is the average formula I am using which gives me the below answer. I want to round it to a whole number.
-
Select separate values from multi-select cells
I have a sheet where staff input their hours available in a week. I have the formula to calculate the hours available for the week. However, staff can enter multiple start and end times, if they are available from 8:00am - 10:00am and then from 3:30pm to 5:00pm. The formula still works for the first set of numbers, but…
-
Alert based values in two columns
Hi, I have a total inventory column and a reorder point column. I'm trying to make my sheet alert someone when the inventory falls below the reorder point. I can't seem to figure out how. I know I'll need a helper column. I thought I'd do a check box and then have the alert set to when the checkbox is checked. But I can't…
-
Cross Sheet Fixed Cell References
Hello, Is it possible to create a fixed cross sheet reference such that my formulas will always pull the first row of data from the target sheet? I am building a Dashboard off of results from a Form that I have set to add new submissions at the top i.e. row 1. As new submissions get added, I want the Dashboard to update,…
-
Multiple Criteria, total count
Hi All, I am trying to do calculate a total number of events, that match criteria for 1. Date range (can also use fiscal column as a matching criteria where i only want those that contain specific letter/numerical value = Q2) for all -NDA Webinars. I don't want it to count each column that matches the criteria, but if the…
-
Basic RAG Calculation formula based off completion % in project schedule
Hello All, I would like to know if anyone knows how to set up a basic RAG formula based on completion % and the schedule date. Custom criteria would be as follows: If % Complete = 100, return Blue If it's not scheduled to start yet, return Clear/Grey If past the scheduled finish date and % complete is less than 100%,…
-
Function for Calculating with Dropdown Boxes
Hello - We have a column that has a checkbox for each year, and another column with a numeric number in it (called Complexity Score (NUMERIC FIELD ONLY) on my screen print). If someone has January, March, April and Sept checked for months in the dropdown column, I need to calculate a the numeric number times how many…
-
Rolling average by week number
I'd like to automate the calculation of a rolling average: "Average the value of the past four weeks" "Average the value of the past 12 weeks" The 'SUP avg' column is what I want to average, and 'W#' is week number. I know we can get the current week number with =WEEKNUMBER(TODAY()) though I haven't figured out the rolling…