Checking checklists

Michael Ott

Hi all,

 

I have a series of columns with checklists that I need to analyse and need some help, please. 

 

The logic is this: if the report is "completed", but _any_ of the several asset check cells are still not checked, a cell called "asset checks not complete" should fire up. 

 

The formula I have is below, but the "asset check not complete" turns to zero if _any_ of the asset checkboxes is checked. I need it to equal zero only if _all_ the asset checks are complete. 

 

=IF(AND([Report Completed]6 = 1, [Asset Check: iPad]6 = 0, [Asset Check: Sonos]6 = 0, [Asset Check: Playstation]6 = 0, [Asset Check: Controllers]6 = 0, [Asset Check: 4G WiFi Device]6 = 0), 1, 0)

 

Thanks for your insights!

Michael

Peter Kirkham

Very easily you can just add an OR around the Asset checks e.g.

 

=IF(AND([Report Completed]1 = 1, OR([Asset Check: A]1 = 0, [Asset Check: B]1 = 0, [Asset Check: C]1 = 0)), 1, 0)

 

Not sure if this achieves what you want though. I think it does?

Michael Ott

Hi Peter, this worked perfect! Thank you!

 

Michael

J. Craig Williams

For check boxes, you can avoid the =0 & =1

 

so this 

=IF(AND([Report Completed]1, OR([Asset Check: A]1 = 0, [Asset Check: B]1 = 0, [Asset Check: C]1 = 0)), 1, 0)

removes the =1

 

and NOT() removes the =0

=IF(AND([Report Completed]1, OR(NOT([Asset Check: A]1), NOT([Asset Check: B]1), NOT([Asset Check: C]1))), 1, 0)

 

I prefer to think "all assets need to be checked"

so I would write it this way

 

=IF(AND([Report Completed]1, NOT(AND([Asset Check: A]1, [Asset Check: B]1, [Asset Check: C]1))), 1, 0)

 

Craig