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?

Find more posts tagged with

Accepted answers

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

All comments

Chamudi Withanawasam

Also... removed the MATCH as well. It return 05/03/21 which is the start date of the Procurement task :'(

=MIN(COLLECT({TEst Project Start}, {TEst Project Parent}, 0, {TEst Project % Complete}, NOT(1)))

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.