-
Formula return blank result
I've attempted multiple times to create a function that result in Blue, Yellow, Green, Red health and it has been unsuccessful. Here are the results that I am aiming at: On Track > "Complete" "In Progress" = Green Ahead > "Complete" = Green Ahead > "In Progress" = Blue At Risk > "Not Started" "In Progress" = Red Behind >…
-
Cross-Sheet Reference Limit
Concerning the current limit of 25,000 total cross-sheet references per sheet - is there an easy way to determine how many are currently being used on any given sheet? Thanks!
-
Updating an aggregation sheet/dashboard with weekly data
Hi all, I'm struggling to think of a way to aggregate data that changes weekly. I will try and describe as best as I can! We have a sheet that financial advisers fill in weekly (separate sheet for each adviser) to show various information as attached in the screenshot. The adviser updates this weekly every Friday, and adds…
-
HOW TO REMOVE #DIVIDE BY ZERO AND MAKE CELL BLANK
I am trying to average a customer feedback section of my sheet. But if the cells where the data to be averaged will come from, the result section shows #Divide by Zero. I use the formula =AVG([column1:column2]). What's the formula I need to use for this? I saw in the other community discussion that I can use IFERROR but I…
-
Forumla Help, Nested IFs
Hi, Looking to see if someone can help me with this formula. I would like it to follow the following logic: * If Task is checked as complete= "Green" * If Task is past complete by date and not checked as complete= "Red" * If Task is 3 days until complete by date and not checked as complete ="Yellow" Here is the what I have…
-
Problems figuring out nested-IF formula
I'm having difficulty figuring out what is wrong with my formula in attempting to use a nested-if formula to automate the RYG light in the "Status" column, depending on the "% Complete" column. Here is my formula: =IF([% Complete]3 < 0.7, "Red", IF(AND([% Complete]3 => 0.7, "Yellow, [% Complete]3 < 1.0, "Yellow", IF([%…
-
Latest Date in Column
I am getting an error with this formula: =MAX([Due Date]:[Due Date]) #Invalid Column Value....the column name is Due Date and the data is configured to take Date.. Thanks
-
Metric Sheet -Showing Trending
I set up a metric sheet that with CountIfs formulas to show Monthly Stats.. I just realized that when I change the reporting period on my source sheet , I will lose the value counted in the previous reporting period... Is there a way to lock the data values in the previous reporting periods... arggh
-
COUNIFS formula when multiple contacts
I have a sheet with columns such as "Status of Request"(Dropdown list: New, In Design, Completed) and "Assigned Designer"(My assigned designer column allows multiple contacts). I want to count how many projects under the status "In Design" for Designer "Luis" do I have so I have the formula =COUNTIFS([Assigned…
-
Help with Networkdays formula
i have a vacation calendar sheet. I am using the networkdays formula to calculate vacation days =NETWORKDAYS([Start Date]10, [End Date]10). The problem is that if there is not a start date and and end date entered into the cell it comes back with an "invalid data type." I am also using a formula to calculate remaining…