-
Calculating the average of a column based on specific criteria in another group of cells
Hello everyone, I am trying to calculate the average of a column based on criteria found in another. The catch is that I don't want to specify the specific text of the other criteria as it is going to be based off of an ever expanding drop down/list of different values and I want to minimize constant manual intervention.…
-
Countifs and past due formula help
I am trying to count the items that have a particular workstream, multiple status (blank, In progress) and Due date in the past. here is my formula: =COUNTIFS([Workstream]:[Workstream], @row "QBR", [Status]:[Status], @row "In Progress", [Due Date]:[Due Date], @row <= TODAY()) the error I get is #unparseable. any ideas on…
-
How can I set-up my Gantt sheet, to plan from end date to starting date?
Hi everyone, I'm trying to make a planing scheduel starting from my ending due date. I need it to calculate automaticly my starting date. Can anyone help me?
-
Counting Parent Row for Weeks
I want to be able to Count the yellow parent weeks to say how many weeks out from current week is. For example week of 6/28 is current week. I want the proceeding weeks to say for example 7/12/20 - 3 weeks out 7/19/20 4 weeks out The current week always gets moved to another sheet once the week is over.
-
IF(AND's and IF(OR's
I have the below formula working to assign a price pursuant to particular job's complexity. =IF(COMPLEXITY517 = "LOW", 1000, IF(COMPLEXITY517 = "MID", 1500, IF(COMPLEXITY517 = "HIGH", 2000, IF(COMPLEXITY517 = "HQP", 2500)))) However, I have 2 tiers of customer pricing that i'd like to track. The above covers +90% of the…
-
How do I write formula to capture date field changed to approved
I am trying to populate a date column with the date that another column is approved. I am befuddled. My formula returns today's date but it changes every day. =IF([BIC Approved]@row = "approved", TODAY())
-
Find function case sensitive
Hello, I have the following formula that only works if a time is entered hh:mm pm. I can't seem to find a way to get this to accept PM. Only works with pm. =IF(FIND("pm", [Tech Arrival Time]@row) > 0, "P", "A") Exhausting my resources before dictating how time is entered. Thank you in advance Sharon C.
-
Count Based on other Distinct Data
I am doing end of year summary data and am stuck. I ran a report and have lots of data - name, gender, race from rosters across multiple trainings. There is duplicate data (X individual may have attended Y training, but also Z training). I need duplicated summary data - X counts as 1 person trained, 1 female trained, etc.…
-
Create a sub task from a task
I need to start using sub tasks. I already have hierarchies built into my input sheet. Much of the information on the parent must also be included in the child tasks Is it possible to systemically copy data from a parent task into a sub task? Or Add a link to the parent record onto the child? I'm not sure a formula will…
-
Rolling 12-Month Grid
I've built the rolling 12-month grid below, which will be referenced elsewhere in this summary sheet.It works, but this could be cleaner. I know I could remove the "helper" row (row 1), but this is how I worked it out, so I kept it for now. Any suggestions for improvements would be appreciated. In m1 row 2, I have this…