3 Replies Latest reply: Jun 11, 2013 7:58 PM by Barry
JayhawkCWE Level 1 (0 points)

I'm not sure if I'm asking this right, but I will try as hard as I can to explain it concisely.


B2 = $180.00

C2 = B2*.81

D2 = Checkbox


How do I make an IF THEN telling B2=$0.00 when D2=TRUE without it affecting the value in B2 if D2=FALSE?


Basically, I need the checkbox to insert a specific value ($0.00) into B2 without affecting a value I previously entered into B2 if the checkbox is unclicked...


Is that even possible?  I can get it to zero with no problem, I just can't figure out how to make it so that I can enter a value in a cell with a formula already existing and have it not affected by the IF THEN statement if the checkbox is false... this may be a pipe dream....


Thanks in advance.

  • Yellowbox Level 6 (8,520 points)

    Hi Jayhawk,


    Another column to hold the formula and calculate the result, leaving original data intact. The formula is in column D:


    Screen Shot 2013-06-12 at 11.02.45 AM.png


    Screen Shot 2013-06-12 at 11.03.06 AM.png


    Formula in D2 is





  • Wayne Contello Level 6 (16,935 points)

    In Numbers a cell cannot refer to itself as that is a circular reference.  There are things you can do to avoid circular refs.


    Using your example:

    Screen Shot 2013-06-11 at 9.47.04 PM.png

    C2=B2*0.81*IF(D2, 1, 0)


    this performs the original math you provided and make it conditional on the checkbox.  If the checkbox is checked then B2*0.81 is multiplied by 1 otherwise by 0.


    Another way would be:

    C2=IF(D2, B2*0.81, 0)

  • Barry Level 7 (29,610 points)

    Hi JayHawk,


    Further to Ian's post...


    Cells in a spreadsheet can contain entered values or values that have been calculated by a formula in the cell.If you enter a value into a cell containing a formula, the entered value replaced the formula (permanently).


    Ian has shown you a way to make the result (in D2) zero when the box in E2 is checked. You can move that calculation into C2 by combining the formulas in D2 and C2:


    C2: =IF(E2,0,B2*$C$1)


    Where =B2*$C$1 was the original formula in C2.


    See Example 1 below.


    Or you can enter the starting value in a separate cell (A2 for Example 2 below) and use the IF function in a formula in B2 to set that value to either A2 or zero, depending on the state of the checkbox in E2:


    B2: =IF(E2,0,A2)

    C2: =B2*0,81


    C2: =B2*$C$1

    Screen Shot 2013-06-11 at 7.55.41 PM.png


    The $C$1 cell reference pulls the percentage from cell C1 and uses that as the multiplier. The $ operators make that an absolute reference. If the formula is filled into other cells, the reference stays fixed on cell C1.