Automatic Project Phase Formula

Mallory McFall

Hello,

I am wanting a formula that returns the current project phase. The phases include Initiation ,Planning, Execution, Testing, Implement, and Closing. I want the formula to return the text value of the phase that has the least % started, NOT including phases at 0% or at 99% and higher.

In the example below, I would want the formula to populate the answer "Planning", since Initiation is at 99%, and the rest of the phases are at 0%.

Mark Cronk

Hi @Mallory McFall ,

Try:

=INDEX([Task Name]:[Task Name], MATCH(MAX(COLLECT([% Complete]:[%Complete], [% Complete]:[%Complete], AND(@cell<.99, @cell>0)), [% Complete]:[% Complete],0)

Work?

Mark

Mallory McFall

Thank you for your assistance @Mark Cronk ! Unfortunately, it is returning the error message "Incorrect Argument Set".

Mark Cronk

Hi Mallory,

One more try:

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0))), [% Complete]:[% Complete], 0))

This should work for you. Syntax is correct this time.

Work?

Mark

Mallory McFall

@Mark Cronk Almost there! Is there anyway we can exclude the Project Plan row?

Mark Cronk

Glad we're close. To exclude the project plan, enter a text/number helper column [level]. In it, place the column formula =Count(Ancestors([task name]@row)). It should result in a 0 for your project plan row and something greater than 0 for the rest.

Then change your return formula to:

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0, level@row>0))), [% Complete]:[% Complete], 0))

Work?

Mark

D Allen

The formula

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0))), [% Complete]:[% Complete], 0))

works but i need to exclude the title row also but a different way

I know which is title because the column name is [Hierarchy] = "1 Project" so how do i adjust the formula to exclude the row if the Column "Hierarchy" ' "1 Project"

Mark Cronk

Try

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0), [hierarchy]:[hierarchy], NOT(@cell="1 Project"))), [% Complete]:[% Complete], 0))

Work?

Mark

D Allen

Yes works perfect ! Thank you

D Allen

Can you explain the formula please and what if i want the phase that has the highest complete %?

Also what does it do if 2 phases have the same % complete

Mark Cronk

Hi @D Allen ,

Index-Match is similar to VLOOKUP. The formula uses Index/ Match to return a value. The Index is what gets returned. The match is where to look. The Match piece collects the % completes that satisfy your criteria - between 0 and 0.99, have a hierarchy other than "1 Project. Then it finds the largest value - Max. Then it looks for that max value and the associated task name. There is good training material available on Index Match.

If the max value exists more than once it will return the 1st one found.

Help?

Mark

D Allen

Yes perfect thanks

D Allen

I can get the formulas, just struggle a bit on syntax when using multiple functions together (match, index) and when using "not" and "and", what is best source to reference syntax for complicated formulas used together

Mark Cronk

The Smatsheet function list page provides the syntax for each function, link below. The community is a great source. Then there's trial and error. The Smartsheet training is really good but may not give you the detail and complexity you're looking for. The is an intermediate and advance formulas class in Smartsheet University.

https://help.smartsheet.com/functions

Happy to help any time.

Mark

Stephanie Zeoli

This is super helpful @Mark Cronk. Your formula above works well, however I am needing to only pull phases with a level greater than 2. I keep trying to insert that criteria in the above formula and it returns errors.

I am using your formula referenced above (see below):

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0))), [% Complete]:[% Complete], 0))

I tried the Level@row>2 and it returns an error.

Can you offer any suggestions? Thank you!

Mark Cronk

Sorry for the delay in responding. Try:

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0), level:level, @cell>2)), [% Complete]:[% Complete], 0))

Work?

Mark