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

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭
Options

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

I love crowd sourcing a solution!