Finding the minimum value in non blank cells
I'm creating a dashboard out of the project timelines and I need to extract the task with the earliest date where the % completion is not 100%.
=INDEX({ATL BP Material Handling Automation Task Name}, MATCH(MIN(COLLECT({ATL BP Material Handling Automation Actual Start}, {ATL BP Material Handling Automation % Complete}, NOT(1))), {ATL BP Material Handling Automation Actual Start}, 0))
This works fine but when non of the sub tasks of a specific main task is completed it shows the main task instead of the sub tasks. I can resolve this if I can include a condition where the prececessors are not blank (No predecessors for the main tasks)
=INDEX({ATL BP Material Handling Automation Task Name}, MATCH(MIN(COLLECT({ATL BP Material Handling Automation Actual Start}, {ATL BP Material Handling Automation % Complete}, NOT(1),{ATL BP Material Handling Automation Task Name}, >0)), {ATL BP Material Handling Automation Actual Start}, 0))
As I understand the problem here is that the PRedecessors column is not considered as numbers format. Any idea how I can achieve this?
Best Answer
-
Ok finally figured out a way!!!! Eliminated the NOT with the value and seems like it's working
=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project Parent}, 0, {TEst Project % Complete}, 0)), {TEst Project Start}, 0))
Edit: ok no it doesn't work :3 only works for some sheets only.
I give up :( :(
Answers
-
Update:
Added another column on to the project timeline to show the number of parent tasks each task has and then used it in the formula. Still no luck :(
=INDEX({AGV for Glove Knitting Phase 1 Task Name}, MATCH(MIN(COLLECT({AGV for Glove Knitting Phase 1 Actual Start}, {AGV for Glove Knitting Phase 1 % Completed}, NOT(1), {AGV for Glove Knitting Phase 1 Parent}, 1)), {AGV for Glove Knitting Phase 1 Actual Start}, 0))
-
<> ""
There is the argument for "not blank".
-
Tried both but doesn't seem to work. :(
-
Can you copy/paste the exact formula from the sheet directly to here?
What do you mean by "it doesn't work"? Are you getting an error or incorrect results?
-
I've created two sheets which I can share with the others that I have simulated the error. If you don't mind sharing your email I can share the sheets with you. I've tried 3 scenarios:
- Result is "Design" which is the first main task
=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1))), {TEst Project Start}, 0))
- Result is UNPARSEABLE
=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1), {TEst Project Perdecessors}, <>))), {TEst Project Start}, 0))))
- Result is "Design" which is the first main task
=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1), {TEst Project Parent}, >=1)), {TEst Project Start}, 0))
-
@Chamudi Withanawasam I believe I responded to your question on another thread.
-
It still doesn't give the answer I need. Seems like it's a glitch with smartsheets. It ignore the condition relating to number of parent tasks but capture the % complete fine.
=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project Parent}, NOT(0), {TEst Project % Complete}, NOT(1))), {TEst Project Start}, 0))
-
Are you able to provide some screenshots?
-
Based on your screenshots, the formula should be returning "Draft Designs".
-
Exactly my point but it return "Procurement" instead...
-
When you create your cross sheet references are you clicking on the column header to select the entire column or are you selecting a range of specific cells?
-
I'm selecting the entire column by clicking on the column header
-
I would suggest double checking your ranges to ensure they are looking at the entire column for each.
Once you have verified that, try removing the INDEX function and just enter the MATCH portion. The result should be 2.
-
I checked all the ranges and removed Index as suggested and used below equation. Result was 4 which is again "Procurement"
=(MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project Parent}, 0, {TEst Project % Complete}, NOT(1))), {TEst Project Start}, 0))
I'm attaching the excel sheets of the smartsheet. Please check if you have time to spare.
Thanks a lot for your time helping me resolve the issue.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!