Finding the minimum value in non blank cells

Chamudi Withanawasam

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?

Chamudi Withanawasam

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 :( :(

Chamudi Withanawasam

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))

Paul Newcome

<> ""

There is the argument for "not blank".

Chamudi Withanawasam

https://community.smartsheet.com/discussion/comment/279247#Comment_279247

Tried both but doesn't seem to work. :(

Paul Newcome

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?

Chamudi Withanawasam

https://community.smartsheet.com/discussion/comment/279421#Comment_279421

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))

Paul Newcome

@Chamudi Withanawasam I believe I responded to your question on another thread.

Chamudi Withanawasam

https://community.smartsheet.com/discussion/comment/280117#Comment_280117

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))

Paul Newcome

Are you able to provide some screenshots?

Chamudi Withanawasam

summary eq.jpg

test project.jpg

Paul Newcome

Based on your screenshots, the formula should be returning "Draft Designs".

Chamudi Withanawasam

Exactly my point but it return "Procurement" instead...

Paul Newcome

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?

Chamudi Withanawasam

I'm selecting the entire column by clicking on the column header

Paul Newcome

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.

Chamudi Withanawasam

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.

Test 1 (1).xlsx

TEst Project (1).xlsx