Average of a drop downbox item

Anton

Hello, 

I am currently working on a project that involves a Dropdown box that has 3 options, Not Started, In-progress and Completed. However, I would like to get the average status of the project, and the the formula I am currently using doesn't seem to be working properly so I was wondering if I could have some help.

This is the formula:

=IF((COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "In-progress") * 2 + COUNTIF(CHILDREN(), "Completed") * 3) / COUNT(CHILDREN()) < 2, "Not Started", IF((COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "In-progress") * 2 + COUNTIF(CHILDREN(), "Completed") * 3) / COUNT(CHILDREN()) < 3, "Completed", "In-progress"))

If you could help me please do. Thank you. Also if it is possible I would also like it to work with Symbols.

Kind regards,

Anton

 

Paul Newcome

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In-Progress"))

 

What this does is... If all children are complete, then the parent will be complete. If all children are not started, then parent will be not started. If at least one of the children is in progress, then the parent is in progress.

 

To make it work with symbols, simply replace what's "inside of the quotes" with the corresponding symbol reference. "Green" "Red" "Yellow"