-
How to have a date formula based off another date formula?
Hello, My situation is I have a master list that has all my event IDs and event start dates. Every event has a separate "project plan" sheet based off a template. When you enter the Event ID on the project plan in [details]1, the event info, such as event start date, pulls from the master sheet using index match. Part of…
-
Some cells not populating with column formula
Hello all, I've got a column formula that automatically determines start date based on a different date. It appears though that some cells are not populating (see snapshot) and I wondered if anyone can advise why it might be? The Start-Up duration column has a column formula "=3" for 3 months and Start-Up start date should…
-
What formulas help join people, teams, AND roles (person may have >1 team and >1 role)?
Stephen Covey says begin with the end in mind. I want to create views of people by scrum team and role as well as by role and scrum team (end result mock-up pictures below). It would be easy if EACH PERSON was only on 1 TEAM and only had 1 ROLE, however, there are other scenarios such as: * 1 person on 2 or more TEAMS as 1…
-
Calculating percent of a percent in an =IF(AND formula
I'm trying to write a formula that says if one cell is less than 30% of another cell, then note the status as "At Risk." =IF(AND([Percent Complete]@row < [Timeline Progress]@row, [Percent Complete]@row = [Timeline Progress]@row * 0.3) / 1, "At Risk", "No Value")
-
Trying to use AverageIF.
Hello I am trying to make a formula that counts the average amount of time an active (open) issue takes, whilst segregating it by priority (low, medium, high). To count the total amount I used this formula: =SUMIFS({Time to fix}; {Solved}; 0; {Priority}; "LOW") However I want to make a new formula that counts the "time to…
-
Overall Status as a percent score (factoring in percent complete, weighted average and due dates)
Hello, I'm trying to calculate a department's overall status based on a percent score. I think I need to use percent of completed deliverables, weighted average of deliverables and deliverable due dates within that area. Any words of wisdom? I'm working on a project with various departments that each provide an arbitrary…
-
Why won't my COUNTIF formula not count anything from source sheet?
Hi all, I currently have a system for three spreadsheets: 1) Masterlist for open/closed actions that feeds a 2) Open Actions sheet, and a 3) Closed Actions sheet. The formulas for the first two sheets work perfectly, however, when it comes to the third sheet (Closed Actions), my COUNTIFS formula will not count anything on…
-
How do I modify a 24-hour helper formula to include non-numerical values?
I have a column with AM/PM times set up as a single select drop-down list. In order to make this column sortable, I created a helper column that converts the AM/PM times to 24 hour times. The formula in the helper column is as follows: =VALUE(IF(VALUE(LEFT([Requested upload time]@row, FIND(":", [Requested upload time]@row)…
-
Vlookup formula help
Hi, I am doing a VLOOKUP in which the formula looks up a list of holiday dates listed in a range from a second sheet. If matching, it is to display the second column which identifies the type of holiday (note, in the formula below I have "false" I also tried "true"). The columns sources are both type: date. the formula:…
-
Multiple IF statements HELP!
The request is to check L2 and if it is NAME1 OR NAME2, then look at L3 to return the contact. Trying to return a contact but need to look at two IF's to make it happen: I currently have: =IF([L2]@row = "NAME1", VLOOKUP([L3]@row, {POC}, 2, false), VLOOKUP([L2]@row, {POC}, 2, false)) and I need to add IF([L2]@row = "Name2",…