-
Return unique values while ignoring blanks.
Hello! I'm trying to find a function similar to vlookup or index that will return a unique value where there are duplicate search values. In this example, individuals book a desk on a given date. Since form submissions create a new row, the bookings flow in like this: Sheet # 1 My goal is now to collect these bookings on a…
-
I want to return a negative number as this sheet will be used for our accounts payable team.
I'm using a simple Sum formula. I want to display this as a negative. conditional formatting to change the color will not work. It needs to either be -$192,000 or (192,000) =SUM([2021 Total]1:[2021 Total]46) = 192,000 I've tried sumIF with no luck too. it's really just adding up all the values but I need the total to be…
-
Formula for replacing specific characters
Hi everyone! I am struggling with the formula language for this. So in Column 1 I have the file names with their file extensions. In column 2 I'd like a list of the filenames WITHOUT the extensions (see the top row) Is there a way to write a formula to replace the LAST 3 characters with nothing? Thank you!
-
Formula for At Risk Flag
Hi Experts, I am using the following formula to turn the At Risk flag red when the condition is true. But I am getting a #UNPARSEABLE error. The formula is trying to say: If either of these conditions is true, I want to turn the At Risk flag red: When an End Date is within 3 days of today AND % complete is below 80% When…
-
Formula issue
Hi Can someone help me to write these five formula in a single formula to get the schedule health. IF(Status@row = "Not Started", "Gray") IF(Status@row = "On Hold", "Yellow") IF(Status@row = "Not Applicable", "") IF(Act1@row >Act2@row , "Red") IF(Act1@row <Act2@row , "Green") THanks
-
Formula not working
Hi , Can anyone help me with this formula issue:- I have 4 symbol color- Not Started- Gray Not Applicable- No color On Hold -Yellow Complete- Green Act1 greater than Act2 and Status is equal to In Progress - Red else Green =IF( Status@row <> "Not Applicable", IF( Status@row = "Not Started", "Gray", IF(Status@row = "On…
-
How remove a character ">" in order to SUM values in one column if they meet a criteria.
I need to be able to get the results using a formula. (I can't use a helper column, or the find and replace to remove ">". Logic needed: This formula should substitute the greater-than sign with nothing and convert those to numbers. Then it will need to sum the numbers where the criteria is met (in this case the condition…
-
if formula
Hi I was wondering if someone could assist. I'm trying to have a box check if the criteria on two sheets are the same. Example would: I have a column called Submission Number on one sheet and I have another sheet with a column with submission numbers. I would like if a number appears on both sheets I would like to check a…
-
Return task name associated with a future date
I have a project with linked tasks. I can get the start date of the next task using the following formula: =MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY())) How can I return the task name associated with this upcoming task? In this example, this formula gives the next start date as 6/19/19. What formula will return…
-
Formula for Actual duration excluding duplication
Hi Community, I am trying to calculate the actual duration starting from a list of Start and End dates. What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous time ranges. Please see example: Start: 01/10/2019 End: 04/10/2019 = 4 working days…