How to make Parent helper column reflect values based on Children values with multiple conditions

decrom

I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column

Levels:

Untrained

In Process

Trained

Proficient

Preceptor

Parent helper row value should reflect lowest rating in the children rows except for the following:

If all CHILDREN = Untrained then PARENT = Untrained

If CHILDREN Untrained >= 1 and any others >0 = In Process

Parent row is "Place an intravenous catheter in a stable patient" with children Canine and Feline

Kelly Moore

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))))

Please let me know what we need to tweak

Kelly

Kelly Moore

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))))

Please let me know what we need to tweak

Kelly

decrom

Thank you! Works perfectly!

Is it possible to then copy the values from the children rows to show up in the same column as part of the formula?

Kelly Moore

Hey @decrom

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))), Proficiency@row)

Will this work for you?

Kelly