Create a formula that returns the current project phase in the project summary metadata sheet.
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
Best Answer
-
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)
Answers
-
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
-
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.
-
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)
-
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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!