Formula Error at 100% Completion: How to Display 'Closed' Instead?

System

This discussion was created from comments split from: Automatic Project Phase Formula.

lyss15

Hello,

I have the following formula is working until the whole project is at 100% then we get #INVALID VALUE

what can I change for one project is total 100% to show Closed

=INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], AND(@cell <= 0.99, @cell >= 0 ), Hierarchy:Hierarchy, 1), 1)

SSFeatures

Hi,

Try wrapping your formula in an IFERROR. If your formula gives you an #INVALID VALUE error then you know that the project is 100% completed, so then display "Closed".

=IFERROR(
  INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], AND(@cell <= 0.99, @cell >= 0 ), Hierarchy:Hierarchy, 1), 1),
  "Closed"
)

Hope this helps!