-
Schedule Health | Average RGYB symbols
Hello community, I have created a schedule health column in my schedule and used the following formula to calculate the row health: =IF(Status@row = "On Hold", "Gray", IF(AND([% Complete]@row = 1, TODAY() >= [End Date]@row), "Green", IF(AND([% Complete]@row < 1, [End Date]@row = TODAY()), "Yellow", IF(AND([% Complete]@row…
-
Really simple If statement - value if true doesnt work if value if false is ""
Hi I have a relatively straightforward if statement and it works, but not always. This is my formula and it works, =IF(COUNT(ANCESTORS()) = 3, PARENT(Responsible@row), "nope") however if I replace "nope" with other types of values, the whole thing stops working, including the value if true .. e.g. =IF(COUNT(ANCESTORS()) =…
-
Question about Functions and Automations
Hi - I have a formula that calculates whether or not an end date has passed by more than 7 days using the TODAY(7) function. If it is greater than 7 days, then a checkbox is checked. That checkbox is used to trigger an automation which sends a notification to a user. My question is whether or not I have to have the sheet…
-
How do I count an occurrence when two dates don't match?
Hello, Can someone help me create a formula that will look at the form created date (date only) and compare it to the activity date being reported and tell me how frequently the activity date is not the same as the created date? I get invalid operation with the following: =COUNTIFS({NS FSE}, [FSE Name]@row, {NS entry date}…
-
Column function not working on select rows with Index Match
Hello Community, I am at a loss on this issue. I have two sheets; I use one (Reporting Sheet) for external resources to report status into via reports, and the second (Master List) I use to house lots of data about projects and pull-in the reporting data. I have column formulas to pull information from the Reporting Sheet…
-
Need help with COUNTIF and Percentage
I have a sheet that is counting a range of check marks. Currently in this example there are 25 possible checkmarks and only 22 are checked. The formula I have written has given me the percentage of unchecked, (12%), but I need it to give me the percent checked, (88%). Here is the formula: =COUNTIF([24/7 Care Line…
-
INDEX(MATCH formula issue
I am trying to use helper columns to consolidate form submissions onto the top row of a sheet, in order to use Document Builder to populate a PDF with the inputs from eight different sessions. There are about 10 columns per session. In the screenshot below, the "Which Session" column is a drop-down list with radio buttons…
-
Formula for multiple column Checkmarks or N/As as a value?
Hello, I am not getting any headway on a IF formula. I have multiple columns that use the values Checkmark and N/A in a drop down column. Its not a checkbox column. The checkmark is a icon that shows a green checkmark in the cell. I have another column that I want to have a formula that says if all these columns have a…
-
Need help in formula for counting only if a specific text is within a cell
Hello, Please see the image below. I would like to count the number of Journal Articles but only those with the status "Accepted for publication(attach final pdf copy)". Currently, I am using this formula in my Sheet Summary - =COUNTIF([Activity Type]:[Activity Type], "Journal Article"). But it is also counting the Journal…
-
#INVALID DATA TYPE need to be blank instead
I need help, I'm creating a formula to calculate to number of night which mean (-1 for the returning date). Current formula: =NETDAYS([Tentative Departure Date]@row, [Tentative Returning Date]@row) - 1 Works well but when departure & returning date are blank I #INVALID DATA TYPE. What to do? Thank you! Marc
