-
How to adjust formula for stacked bar chart to use HAS or CONTAINS
I have a stacked bar chart that displays how many initiatives are assigned to an individual by year. (See example below.) We've recently changed the source sheet from only allowing one year per initiative to allowing for multiple years (for initiatives that span multiple years). How do I adjust this formula to count if…
-
Auto RAG based on milestones
I'm looking to write a formula that auto calculates a RAG on a row and struggling where to start as not my area of expertise. I've looked in the community and examples provided have not help me answer my question so hoping someone can help. My logic is if the Planned Start Date is in the past/overdue then it should flag…
-
How to create a sum column formula in Sheet Summary
I have a formula base column that I'm trying to get the sum in Sheet Summary and I'm getting the #invalid operation error. I have some criteria for the sum I'm looking for and I did the exact formula for the [DDA Hours Used] column, so I'm not understanding why this one isn't working.
-
Creating a widget for tracking the tasks for each member of a team
Hi. For the past few days, I've been trying to create a dashboard to track the tasks that each member of my team has. I learned that the best way to do this is to create a separate sheet where I used formulas to create a set of data and then use those to create charts in my dashboard. However, when I tried to use a formula…
-
calculate number of full months between start and end dates
I'm looking for a formula that will calculate the number of full months between 2 dates that will be used for calculating a goal amount by multiplying a monthly amount x the number of full months (partial months are prorated). In my example, I'm using =ROUNDDOWN((([End Date]@row + 1) - [Start Date]@row) / 30.425, 0) It…
-
A True Mind Bender - Tracing Tasks through Predecessors
Is this even possible? I have a task (say Task 18) that is preceded by other tasks, all joined by predecessors. Task 18 is the "final" task. Task #18 is preceded by Task 16, and Task 16 is preceded by Task 14. Outcome - I want to filter rows to see only the tasks that have a relationship to "Task 18." I'm thinking that I…
-
Formula that Recognizes a Cell's formatting
Is there such a thing? If a cell has a fill color of Red, can a formula read that cell and recognize that fill color? Same question for any kind of cell formatting - strikethroughs, font color, bold, italic, etc.? The reason for the ask is that I have a plan that has A LOT of conditional formatting, and I want a report…
-
TODAY() returns yesterday or tomorrow depending on your timezone
Hi Community, We have an interesting issue here in Melbourne, Australia when we use the TODAY() function. At various times of the day, it can return yesterday's date rather than today's date (as it should). Took quite a while for this to be acknowledged, but the behaviour has been confirmed now by a couple of senior…
-
Average Collect formula
I'm trying to collect the average survey response number for a quarter for the current year and multiply it by 0.2, for a percentage, but it's coming back unparseable. The first referenced column is a 1-5 number, the months are from a numerical month helper column, 'Created' is the auto-create date. Any ideas?…
-
Changing Status Dot Color Based On Deadline Column
Hi, I've tried looking up other questions to answer this and got as far as this formula [=IF(Deadline@row <= TODAY(+14), "Green", IF(Deadline@row <= TODAY(+7), "Yellow", IF(Deadline@row >= TODAY(0), "Red", "")))], but it's not providing the proper results. I have a Deadline column and a Status column (which is the RGY…