Cell Data controls checkbox default status

Question

Hello,

I can only find examples online on how to change a cells format dependent up if a checkbox is checked or not.....not the opposite, which is what I essentially want to do. I want the data and/or conditions of a cell to determine a checkbox's status.

I want an excel checkbox to change its default status based on the conditions of a different cell. I can work with a resolution that does this based on the cell data changing color, but prefer to do it based on whether or not the cell passes or fails a specific formula.

This is an example setup:

Cell A1 has a checkbox whose default status is "unchecked". This is the PASS checkbox

Cell A2 has a checkbox whose default status is "unchecked". This is the FAIL checkbox

Cell A3 has data to be entered (example: 0.1000, a "target" value)

Cell A4 has data to be entered & has conditional formatting applied so that any value outside the A5 and A6 ranges changes the A4 cells color to red.

Cell A5 has a formula that calculates an upper tolerance value for cell A3.

Cell A6 has a formula that calculates a lower tolerance for A3.

          example: A5 =A3+(A3\*0.03)    A6 =A3-(A3\*0.03), results are displayed as A5 = 0.1030, A6 = 0.0970 

I want cell A1 (the checkbox) to change from "unchecked" to "checked" if the data entered in cell A4 falls within the upper and lower tolerance values established in cells A5 and A6.

Additionally I "would" also prefer to have cell A2 change from unchecked to checked if the data entered in cell A4 does "not" fall within the upper and lower tolerance values established in cells A5 and A6.

Answer 1

Select A1.

Enter the formula =AND(A4>=A5, A4<=A6)

Select A2.

Enter the formula =OR(A4<A5, A4>A6)

Alternatively: =NOT(A1)