-
IF/OR Formula
Hello - I am trying to make a formula work but having issues. I have a more significant formula that is correct but when I add to it, I mess it up and can't figure out what I'm not doing. I can make each formula work on its own but having trouble merging them. The following formula works. I need all of it but I also need…
-
Translate Cell value to a specific number
Hello, My Master sheet has one column called "Portfolio,". Each cell in that column *may* contain multiple values - up to 5 choices (as shown below) I'd like a new column that would convert those "names" to a specified number (ie. 1,2,3,4,5) for example (using below as an example), it would translate to.... 1 1,2,3,4,5…
-
MAX Date based on value in a Category column
Hello. I am trying to calculate MIN and MAX dates based on the value in the Milestone Category. The first screenshot is my source sheet. The second screenshot is the second sheet where I am performing the calculations. Looking at the first screenshot below, let's say I want to calculate the MAX Start Date for any row that…
-
Need to summarize RAG status
Hi, I have multiple RAG status' in my plan and I would like to summarize the overall status, is there any way to achieve this. So as an example, I have a sheet summary in my sheet and each status in the plan has a formula which tells me what % is the colour, e.g =COUNTIF(Status:Status, "Red") / COUNT(Status:Status) and so…
-
Need to summarize RAG status
Hi, I have multiple RAG status' in my plan and I would like to summarize the overall status, is there any way to achieve this. So as an example, I have a sheet summary in my sheet and each status in the plan has a formula which tells me what % is the colour, e.g =COUNTIF(Status:Status, "Red") / COUNT(Status:Status) and so…
-
If Column A contains specific text and Column B contains specific text, then Column C is text
Hello, I am not sure how to figure this out. I have tried IF, INDEX, CONTAINS with no luck. What I am trying to do is set up on Impact/Effort Matrix with a formula that looks at the text in Column "Effort" and Column "Impact" then returns certain text in Column C. For example: If Effort is "low" and Impact is "low" then…
-
INDEX COLLECT with MAX and ISBLANK
Hi folks, I'm a bit stuck with this formula. Hope someone can help. I want the Link to Latest Edit column to pick up the Latest Edit for whichever project is indexed in the row. The Current formula is leveraging my Autonumber column and picking up the last added with a MAX. Like so: This all works fine except that…
-
Automatically drawing data from another sheet? Vlookup? Index?
Hi brainstrust, I'm trying to match up some population data from a different sheet with a humanitarian response distribution tracker. So if a volunteer enters "Sydney" in a row regarding their latest trip then the population is automatically entered from the other sheet. I've tried to use IF but there are way too many…
-
Using % in RAG status calculation
I am trying to use % complete to indicate RAG, Percentage complete: 75% and over "Green" Percentage complete 50%-74% " Yellow" Percentage complete below 49% "Red" I am unable to use % symbol in the calculation, it gives me incorrect RAG Kindly advise
-
Trouble combining two formulas with "OR"
I have the following two working formulas but when I try combining it I keep getting error messages. =IF(ISDATE([Meeting scheduled date]@row), "Scheduled") =IF(OR(Acuity@row = "High", Acuity@row = "Medium"), "Required") They each work on its own by referencing two diff columns. I need help combining this to one formula.…