-
Formulas Question - Referencing Another Sheet
Good morning, I am trying to pull calculate an average from data being pulled from another sheet. I want to be able to calculate the average duration, by month of completed projects. I basically want to say what is the average of the duration fields if the phase is completed and the end date column is in the month of…
-
Absolute reference
I want to use absolute reference for a cell. However, when I select the cell, I always get the @ row at the end. For example, when I select the cell [Text Text], I get [Text Text]@row. Do I have to always manually specify the cell number when I want to use absolute reference?
-
OR condition within range-criterion of COUNTIFS
I want to get the count, where Workstreams columns is one of "Overarching", "Others","Cross-Topics" and status is "active", DEEP - DIVE:slid 1 Users is "int", DEEP - DIVE:slid 1 -Biz/Tech is "Business" and DEEP - DIVE:slid 1 FTE is "hired" . I am getting stuck with the first condition, of using OR in the first…
-
Why is this formula not converting to a column formula?
Hi, I have set up this formula and am trying to convert to a column formula but it tells me there is an error and it won't do it. How do I change it so that it works? Also would there be a more efficient way of writing this formula? Thanks! =IF([Month No]@row = 1, [PO #]$1, IF([Month No]@row = 2, [PO #]$1, IF([Month…
-
Split Text to multiple Column Formula not working?
I tried to split the text in Course Release date to two column if there is more than two dates. For Course release date original column, I used =LEFT([Course Release Date]@row, FIND(" ", [Course Release Date]@row)) For Course release date if refreshed column, I used =RIGHT([Course Release Date]@row, LEN([Course Release…
-
Index Match
My first attempt to use this function. I have a worksheet that has a column (Category). Properties set for dropdown / single select. Rather laborious to manually click on every cell and select. Two other columns contain Product description column and part number column. The dropdown column places the item in a category. I…
-
Sheet Summary Formula for Number of Tasks Complete (Not Started, On Hold and In Progress)
Looking to find a formula for number of tasks in each status- I have this so far but I'm getting the #UNPARSEABLE error. Please help =COUNTIFS([Status]:[Status], CONTAINS("Complete" @cell)", [%Complete]: [%Complete], 1)
-
Nested IF OR AND string
I am looking to combine 2 variables supported by an AND statement to look for matches and then result in an output REGION AREA Owner (output on match) =IF(OR([::IMPACTED REGION(S)]@row = "US", [::IMPACTED REGION(S)]@row = "CAD", AND([::IMPACTED FUNCTIONAL AREA(S)]@row = "Insurance", [::IMPACTED FUNCTIONAL AREA(S)]@row =…
-
Index Match - Circular reference
I'm getting a circular reference error with the following formula. I understand why I'd get the error, but how would I approach the following? For the sake of example, I have 2 columns (ColA and ColB). ColA has an ID number, and ColB has a description. ColA may have multiple records with the same ID number. I want ColB to…
-
Ancesstors
I can use the below formula to get the ancesstors + task name JOIN(ANCESTORS([Task Name]@row), " | ") + " | " + [Task Name]@row This will return to me: Parent 1>Parent 2> Parent 3> Task 1 I'd like it to return the reverse order: Task 1> Parent 3>Parent2>Parent 1