Health based on check box

Matthias Matthe

Hi Community,

I want to automate my health column, based on whether several checkboxes (milestones) are ticked or not. Health column is indicated in heart symbols ("Empty" - "Five").

That's what I have right now, but causing an error:

=IF([Infra Team informed to remove legacy hardware]@row = 1, "One", IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1) "Two", IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1) "Three", IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1)"Four", "Empty")))))

Kimberly Loveless

If it is the later. I would actually reverse your order. The way the nested if statements work is if the criteria in the first statement is true then it will stop there, i.e. any that have the [Infra Team informed to remove legacy hardware] checked would show as 1 heart.

This formula should work...

=IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1),"Four",IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1), "Three",IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1), "Two", IF([Infra Team informed to remove legacy hardware]@row = 1, "One","Empty"

As I was doing that I actually noticed that you missed a couple of commas so if it is throwing an error that might have been it also.

Kimberly Loveless

Is it giving an actual error or just giving you 1 heart or empty?

Kimberly Loveless

If it is the later. I would actually reverse your order. The way the nested if statements work is if the criteria in the first statement is true then it will stop there, i.e. any that have the [Infra Team informed to remove legacy hardware] checked would show as 1 heart.

This formula should work...

=IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1),"Four",IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1), "Three",IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1), "Two", IF([Infra Team informed to remove legacy hardware]@row = 1, "One","Empty"

As I was doing that I actually noticed that you missed a couple of commas so if it is throwing an error that might have been it also.

Matthias Matthe

Thanks! It works. Logical thinking error on my side :)