-
Has anyone found a way to calculate CPI (based on planned spend and actual spend) with regards to to
I am using the following formulas for my calculations on the parent row in my project plan but need to find a way to have it based on today's date and not the roll up from the entire project SPI: =IF(AND([Planned Value]@row = 0, [% Complete]@row <> 0), [% Complete]@row + 1, IF([Planned Value]@row = 0, 0, [%…
-
Contain items that I don't want to count
"pruned boxwood and boxwood hedges." This is an an entry for a cell from a form. There are two search items here: "boxwood" and "boxwood hedges". I have another cell attempting to note that boxwood (shrubs) were pruned separate from "boxwood hedges". =IF(AND(CONTAINS("boxwood", [FN: Pruning]@row), (NOT(CONTAINS("boxwood…
-
Help with the following parent/child formula
=COUNTIFS(CHILDREN([Not Applicable]@row), 0, PARENT([Name of the Operational Provision or Rule],"Payer-Accounts") #INCORRECT ARGUMENT SET I am trying to count child rows where a checkbox is set (not applicable) where the parent row title is "Payer-Accounts". I am creating individual Summary values for each of the parent…
-
Contact List suggestions not showing on Dynamic View
I have a number of contacts added to our sheet, however, when other people in my team access the sheet via dynamic view they cannot see the contact list suggestions on the dropdown.
-
Convert day of month to Business Day date
We have a calendar template that we re-use every month. On this calendar ,we have a column that is the numerical representation of the day of the month (1, 2, 3, 4, etc.). Each month, this is a different date. Currently, someone is manually entering the dates that correspond to the day number. Is there a formula that would…
-
When a specific value present, calculate date from Created to Date of completion
For other sheets, we use formula =IF(ISBLANK([Manually Actioned By]@row), NETWORKDAYS(Created@row, TODAY()), NETWORKDAYS(Created@row, [Manual Actioned Date]@row)) to tell us the turnaround time for the activity. It might not even be a great formula but it does what I need. But now, want to only trigger this where…
-
Split or Parse Formula
Hi All I am trying to spilt a multi select drop down column into two individual columns - I have tried several options but just cannot get them working =IF(ISERROR(FIND(",", [Survey Type]@row)), [Survey Type]@row, LEFT([Survey Type]@row, FIND(",", [Survey Type]@row) - 1)) =LEFT([Survey Type]@row, FIND(",", [Survey…
-
Formula Order of Operations Problem
I am trying to use a formula with CHAR(10) between each missing asset item to build a nice list to use in a record search return automation. I am using one row for each employee with multiple uniform items in several columns. My formula does well until it finds an item in the formula sequence is not missing, then it quits…
-
Calculate totals across multiple project sheets , using another sheet to get the list of projects
Hello I have project request sheet which is used to track all projects that the LE team are asked to do. The LE team consists of 3 team members. As projects on the request sheet are approved, team members are allocated to the project. On the project sheet the project status is set to approved. I would like to know how to…
-
How do I pull data using cross-sheet reference and multiple criteria
Help! I have tried to get pull this data using many different combinations of formulas (using AI and searching through other posts.) Here is what I'm trying to do: I want an "X" to appear in the appropriate column when pulling from another sheet labeled "Roles" The Roles sheet has five different columns that I need to…
