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

  • Chamudi Withanawasam
    Chamudi Withanawasam ✭✭✭✭
    edited 03/04/21 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 :( :(

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!