3 Replies Latest reply: Jun 11, 2013 7:58 PM by Barry
JayhawkCWE Level 1 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 5 Level 5 (7,615 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

    =IF(E2=TRUE,0,C2)

     

    Regards,

    Ian.

  • Wayne Contello Level 6 Level 6 (15,870 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 Level 7 (29,350 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

    OR

    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.

     

    Regards,

    Barry