-
My COUNTM formula will not stay
Not sure what to do here. I inherited this sheet and the countm formula will not stay. If you remove the apostrophe it reverts back within a minute. How can I keep the formula?
-
Average for both columns
I have a dropdown columns with several choices and each choice represents % complete. The idea is to get the Circuit % complete for 1st and 2nd circuits columns but also It has ‘N/A” that will not count. It only count the column that doesn’t have it. This is what I tried: =AVG(IF([1st Circuit Status]@row = "Assigned to…
-
join collect
I have a formula that returns as expected =IF(ISBLANK([unique.invoice]@row), "", JOIN(COLLECT(collect:collect, [Invoice No.]:[Invoice No.], [unique.invoice]@row), CHAR(10))) but I tried to replicate the formula to join collect another column and its not working =IF(ISBLANK([unique.invoice]@row), "",…
-
Lookup multiple columns for text value and return cell value
I'm trying to figure out a formula to return the value in a cell over multiple columns. I have 10 locations consisting of various buildings for each location. Only one column will have text in it. The formula I have will obviously only return the text for that column and I cannot figure out how to repeat the formula to…
-
Combining IF Statements is not working with comma
Hello, My formula is as follows: =IF(AND([日付 Date]@row < TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Red"), (IF(AND([日付 Date]@row <> TODAY(), CONTAINS("2号室 Room 2", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])),…
-
Trying to use COUNTIFS and keep getting UNPARSEABLE error
Hi, I've entered this statement: =COUNTIFS(PMO Project RAG Status Range 5, <DATE(2019, 12, 31), (PMO Project RAG Status Range, "Corporate Support - Corporate Finance") What I'm trying to do is count the number of projects with the designation of Corporate Support - Corporate Finance that will be delivered in 2019. The…
-
Progress Bar Date Automation
Hi All, Could someone help me figure out a formula for the progress bar column style. My goal is to determine how close (by progress bar visual) are we to the "target end date" of a task. For example, my start date is 01/09/2022 and my target end date is 30/09/2022 and the actual date of today is the 29/09/2022 so we would…
-
How to subtract quantities from one sheet via forms to another sheet with total quantities
My issue is this: I have a master sheet with material types (A1,A2,A3 etc..) and total material quantities (#'s). The other sheet I am using is updated via forms. The form is used for distributing materials and requires a material type, and material quantity before submission. Once submitted I would like the quantity to be…
-
Identify Quarters based on date AND add text if cell is not a date
I am wanting to identify a quarter based on a date and have that formula working, but I also want it to return a value of "TBD" if the Start Date is not a date AND return a value of "Dates Invalid" if the Start Date is outside of the current year, which in this report is 2023. How can I adjust the formula below to…
-
Average formula with error should give a blank cell
I used this formula below to give me the "DOC % Complete" but it is missing something. If the cell has “Cancelled” the Docs % Complete column should be blank but right now is giving me 50% =IFERROR(AVG(ISDATE([Design Circuit Recommendation Actual]@row), 0.5) + IF(ISDATE([Design Velo Recommendation Actual]@row), 0.5), "")…
