-
How can I average a percentage in a parent row?
I have a status column that uses Red, Yellow, Green and Gray to represent "Not Complete", "Partially Complete", "Complete" and "Not Applicable". I successfully added a progress column that uses a formula to translate those statuses into a percentage. However, since the parent row also has a status color, I can't get the…
-
how do I wright the =if for this
I have a call audit sheet were 12 questions total a percentage in a column call marks i now need the answer in mark to show fail pass or excellent. and highlighting as well if possible. 0 to 69 being fail (showing word and highlighted) 70 to 85 being pass (showing word and highlighted) 85 to 100 being excellent (showing…
-
countif/contain formula
Hello I need a formula for the Sheet summary that will give me the total based on section id/status color. instead of creating 3 fields for each color status
-
INDEX COLLECT not working!
I have an estimate sheet, where I am referencing pricing from another sheet with multiple criteria - trying to use an INDEX with COLLECT formula. Please see screen shots attached. The formula I am using is: =INDEX(COLLECT({Rental Cost}, {Equipment Description}, [Equipment Description]@row, {Units}, Units@row, 1)) This…
-
Combining IF and IF AND Formulas
I have three formulas below that work as individual formulas. I am trying to combine them in my Status column into one formula so my cell automatically updates based on the percent shown in the % complete cell. I thought it should be =IF([% Complete]@row = 0, "Not Started"), IF(AND([% Complete]@row > 0.01, [% Complete]@row…
-
#Invalid Data Type
Hello, I have a formula in my sheet summary that was working until recently. For some reason the formula is no longer able to count the months or years in the sheet. I also receive the error when I remove the [Current Month]# from the formula and try putting "11". Does anyone have any recommendations or see anything wrong…
-
How to write a formula based on 3,6,9 months from current date, using G/R/Y/G symbols?
Hi, I am trying to write a formula that uses the gray, green, yellow, red symbols to show how close we are getting to something expiring. If the Patent has not yet been filed (date is blank) this would be the gray symbol. If it has been filed and is under 6 months old, green; if it is between 6 months - 9 months from the…
-
Seeking some assistance/guidance on Rollup / Headers (formula)
I've reviewed some of the Rollup documents, and it appears some of what I'm thinking is do-able, but perhaps not fully, depending on how the formatting works... Appreciate any insights! I have a project where I have several drop down status’s for tasks such as (STATUSA, STATUSB, STATUSC). Since I have so many subtasks,…
-
How do I create a formula to show how many weeks/days out a task needs to be completed?
For example, if I have an event, or a project live date, and I want a task list that says Task 1 is due 8 weeks prior Task 2 is due 3 weeks prior etc...I am new to this and cannot figure out the formula!
-
Countif not containing specific text
I am trying to create a formula to count "Red" based on one column but not include certain ones which contain the text "Spring" in another column but I can't get it to work. Originally I tried this formula: =COUNTIFS([Aging]:[Aging], "Red", Project:Project, <> "Spring") However, I realized that won't work because the…