If all = Competent then competent, if not, then inconsistent

Gunn_Jack

Hi all,

I need help with a formula. People choose their skill based on a certain topic:

• Competent
• Inconsistent
• Expert

I need a formula that looks at all the children and determines that:

• If 1 inconsistent, no matter what else, show as inconsistent
• If all competent or expert, show as competent

I am guessing it can be easily achieved by saying if there is 1 inconsistent then inconsistent, otherwise, show competent. But i am not sure how this will look in a formula?

Any help would be much appreciated.

Thanks,

Jack

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")

Gunn_Jack

@Paul Newcome Perfect, thank you!

Paul Newcome

Happy to help. 👍️

Gunn_Jack

Hi @Paul Newcome - sorry to bother you but just as an addition, if everything is blank, how do i get the parent row to show inconsistent? Not an issue if too difficult, as i can just pre-populate to inconsistent.

Cheers,

Jack

Paul Newcome

Do you have a column that will have data in every row?

Gunn_Jack

Hi @Paul Newcome really appreciate your help but i think i cracked it:

=IF(COUNTIFS(CHILDREN(), "") > 0, "", IF(COUNTIFS(CHILDREN(), "Inconsistent") > 0, "Inconsistent", "Competent"))

:)

Thanks,

Jack

Paul Newcome

Happy to help. 👍️

Your formula currently will display a blank if there is even one child row that is blank. So if all are "Expert" except for one blank, it will show as blank. To write it so that it is only blank if ALL child rows are blank, you are going to want something more along the lines of...

=IF(COUNTIFS(CHILDREN(), "") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "", IF(COUNTIFS(CHILDREN(), "Inconsistent") > 0, "Inconsistent", "Competent"))