Create a formula that returns the current project phase in the project summary metadata sheet.

Melitta

The phases in my project plan are Level 1. The formula should return the text value of the phase (task name) that has the least % started, NOT including phases at 0% or at 99% and higher.

=INDEX({Task Name}, MATCH(MAX(COLLECT({Level}, {Level}, AND(@cell, 1), {Level},1) AND(MATCH(MAX(COLLECT({Project 10 - % Complete}, {Project 10 - % Complete}, AND(@cell < 0.99, @cell > 0))), {Project 10 - % Complete}, 0)))))

It works, until I add in the Level 1.

Anyone have a suggestion?

Thanks

Paul Newcome

I would suggest an INDEX/COLLECT like so:

=INDEX(COLLECT({Testing Phase - Task Name}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = 1), 1)

Kelly Moore

Hey @Melitta

Try this

=INDEX({Task Name}, MATCH(MAX(COLLECT({Project 10 - % Complete}, {Project 10 - % Complete}, AND(@cell < 0.99, @cell > 0), {Level}, @cell = 1)), {Project 10 - % Complete}, 0))

Will this work for you?

Kelly

Melitta

That gets a little bit closer, Thank you @Kelly Moore

It returns the Task Name, Level = 0 rather than the Task Name, Level =1 which is the phase level.

=INDEX({Testing Phase - Task Name}, MATCH(MAX(COLLECT({Testing Phase - % Complete}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = "1")), {Testing Phase - % Complete}, 0))

I feel as if this is so close and I can't see it.

Paul Newcome

I would suggest an INDEX/COLLECT like so:

=INDEX(COLLECT({Testing Phase - Task Name}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = 1), 1)

Melitta

@Paul Newcome

That works. I also realized I had an error in my project plan that was causing a problem.

I love crowd sourcing a solution!

Thanks for your time.

Paul Newcome

Happy to help. 👍️