# Formula

I have a series deliverables reference from another sheet (sheet 1). In this sheet (sheet 2) I list those deliverables under an objective and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula.

Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.

Tags:

• ✭✭✭✭✭✭

If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Thanks,

Aravind GP| Principal Consultant

Atturra Data & Integration

M: +61493337445

E:Aravind.GP@atturra.com

W: www.atturra.com

Thank you Aravind! Much appreciated

• ✭✭✭✭✭✭

If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Thanks,

Aravind GP| Principal Consultant

Atturra Data & Integration

M: +61493337445

E:Aravind.GP@atturra.com

W: www.atturra.com